Locks (DB)
Shared/exclusive, row/table, intention locks. Two-phase locking as a correctness argument.
Level: advanced
What is it?
Database locks coordinate concurrent transactions around rows, pages, tables, schema objects, or other resources. Shared locks commonly permit compatible readers; exclusive locks protect writes, while intention and range/predicate locking express broader coordination needs.
Why does it matter?
Locks preserve integrity under concurrent updates and can enforce serializable ordering, but contention lowers throughput and can create deadlocks. Understanding lock scope and duration helps explain blocking incidents.
Mental model
Each transaction holds permissions over resources. A lock compatibility matrix says whether another transaction may acquire a conflicting lock; wait-for relationships help explain stalls and deadlocks.
How it works
- Lock granularity trades concurrency against bookkeeping cost.
- Strict two-phase locking holds relevant locks until commit/rollback, supporting serializable behavior in classic models.
- Deadlock occurs when transactions form a cycle of waits; engines may detect a victim or use timeouts.
- MVCC can reduce read-write blocking, but locks remain for writes, metadata, and other operations.
- Lock wait timeouts and deadlock errors must be handled deliberately by application transaction logic.
Steps
- Transaction A locks row 1 for update.
- Transaction B tries to update row 1 and waits for A.
- A commits and releases the lock.
- B proceeds or retries according to the database's conflict and timeout behavior.
Real-world usage
- Diagnosing blocked transactions and lock waits.
- Understanding two-phase locking and deadlock detection.
- Designing short, predictable transactions around shared rows.
Common mistakes
- Holding locks while waiting for a user or remote HTTP response.
- Assuming row-level locking means only one row can ever be involved; index and range locks may widen the scope.
- Retrying a deadlock without making the transaction safe to repeat.
Interview questions
What is a deadlock in a database?
Transactions wait on one another in a cycle, so none of the transactions in the cycle can proceed without intervention.
Why keep transactions short?
Long transactions can hold locks and snapshots longer, increasing contention, deadlock opportunities, and cleanup pressure.