Locks
Shared and exclusive locks, row or table level, their compatibility, SELECT … FOR UPDATE, pessimistic or optimistic locking, waiting and the deadlock the database detects and resolves.
- 26 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
A few cards from the deck
Shared lock: what is it?
Show the answer
A lock taken to read data: several transactions can hold one at once on the same row, but none of them can write it.
Two readers do not get in each other's way. A writer, however, has to wait until they finish, because its exclusive lock is incompatible with the shared one.
T1 ran the UPDATE below without committing. T2 then runs an UPDATE on the same row. What happens to T2?
```sql
UPDATE compte SET solde = solde - 10 WHERE id = 1;`- It waits until T1 commits or rolls back
- It fails at once with an error
- It goes through and overwrites T1's change
- It goes through by reading the old value
Show the answer
It waits until T1 commits or rolls back
T1 holds an exclusive lock on the row until it ends. T2 waits, then resumes from the value committed by T1, without losing it.
Two … locks are compatible, but an … lock is compatible with no other lock on the same row.
Show the answer
Two shared locks are compatible, but an exclusive lock is compatible with no other lock on the same row.
Reader with reader: no waiting. As soon as a writer is involved, one of the two waits for the other.
Exclusive lock: what is it?
Show the answer
A lock taken to write data: only one transaction holds it, and no other can obtain a lock, shared or exclusive, on the same row.
Every UPDATE or DELETE takes one on the rows it touches, and it is only released at the transaction's COMMIT or ROLLBACK.
The database answers “deadlock detected” several times a day on transfers: some move money from A to B, others from B to A. Which fix?
- Add an index on the balance column to speed up the UPDATE statements
- Run the transfers at READ UNCOMMITTED to avoid the waits
- Always lock the two accounts in the same order
- Run the transfers at SERIALIZABLE so they no longer cross
Show the answer
Always lock the two accounts in the same order
The cycle arises when two transactions take the same locks in opposite orders. A single order makes the cycle impossible; at worst, one waits for the other. A stricter isolation level does not change the lock order. The application must also know how to retry a victim.