Quiz
- So, what happens if a transaction takes too long to release its locks like can this prevent other users from updating the database?
Yes! That's the main difficulty. A lock on a table means no one else can use that table until the lock is released. Sometimes it's necessary, but we want to minimize it. That's the goal of transactions.
- I am a bit confused by the visual in the initial Strict Two-Phase Locking section, can we go over it?
For sure. I think you mean this section: two-phase locking.
- Can we also go over Isolation Levels, I am a bit confused on how this helps us update databases correctly because we can just lock all the tables for each change.
Yes, we could lock the table, but that's the lowest level of concurrency. We're trying to do better.
Isolation levels are different "bad" things that you're willing to endure.
This blog post on isolation levels looks really good. I've added it to the reading.
- Could you explain the difference between a Dirty Read and Non-Repeatable Read again?
For sure. See that blog post again.
A dirty read (first example) gets a "wrong" value because (in the other transaction) it's been written but not committed.
A non-repeatable read (second example) gets a "wrong" value the *second* time it's read.
- Could you explain how databases that use fine-grained locking handle phantoms?
Sometimes they don't.
Other times, they lock rows "near" a query, preventing the insertion of rows that would cause incorrect values.
- Can we clarify what isolation is from the ACID properties? Also, what exactly does durability in the ACID properties mean? Does it mean that for example if im registering for a course and if i hit submit (to register) it should have worked even if my computer crashed while doing that? I don't understand… Thank you!
Isolation means that each transaction is independent of others. Obviously, that's not quite true, since they of course affect one another (if one changes the invenory and another checks the inventory...). But intuitively it means that the execution of one doesn't intefere with the correct execution of another.
Durability means once a value is committed, the database won't forget it. It has to do with whether the *database server* crashes, not whether your laptop crashes.
- No questions but I was surprised by how similar database locking is to locking in operating systems!
Yes, it's very interesting!