Unit
- Concurrency control: Locks prevent simultaneous transactions from interfering destructively. Without locks, lost updates and dirty reads occur when two transactions modify the same data without coordination.
- Transaction isolation: Locks enforce isolation by restricting access to data items that a transaction is reading or writing. This ensures each transaction sees a consistent snapshot of the database.
- Data integrity: By serializing access to critical resources, locks maintain the consistency of the database state. Integrity constraints remain satisfied even under high concurrency.
- Lock types: Shared locks allow multiple readers but block writers; exclusive locks block both readers and writers. This basic distinction forms the foundation of all locking protocols.
- Lock duration: Locks can be held for the duration of a single statement or until transaction commit. Short-term locks reduce contention but may compromise repeatability.
- Automatic locking: Modern DBMSs acquire locks automatically when executing DML statements. Developers rarely manage locks manually, but understanding the underlying mechanism helps in troubleshooting.
- Lock escalation: Many systems promote fine-grained locks (row) to coarser ones (page, table) when a threshold is reached. Escalation reduces overhead but increases blocking.
- Performance trade-off: More granular locks allow higher concurrency but increase lock management overhead. Finding the right balance is key to throughput.
- Deadlock detection: A deadlock occurs when two or more transactions hold locks that the others need, creating a circular wait. Modern DBMSs detect deadlocks by constructing a wait-for graph and abort one transaction to break the cycle. This ensures forward progress without manual intervention.
- Lock timeouts: To prevent indefinite waiting, many systems impose a timeout on lock requests. If the lock cannot be acquired within the specified period, the transaction is aborted and rolled back. This mechanism limits the impact of long holding times and potential deadlocks.
- Two-phase locking (2PL): This protocol ensures serializability by dividing the transaction into a growing phase (acquiring locks) and a shrinking phase (releasing locks). Strict 2PL holds all locks until commit, preventing cascading aborts and providing recoverable schedules.
- Intent locks: Hierarchical locking uses intent locks (IS, IX, SIX) to signal a transaction's intention to acquire finer-grained locks. They allow efficient lock checking at higher levels without examining every lower-level lock, improving performance in large tables.
- Lock compatibility matrix: The set of allowed concurrent locks is defined by a compatibility matrix. For instance, shared locks are compatible with other shared locks but not with exclusive locks. Understanding this matrix helps predict blocking behavior and design high concurrency workflows.
Comparison
Shared vs. Exclusive Locks
- Shared (S): Permitted when no exclusive lock is held. Multiple transactions can hold shared locks on the same resource simultaneously. Used for read operations.
- Exclusive (X): Permitted only when no other lock (shared or exclusive) is held on the resource. Used for write operations to ensure exclusive access.
Quick Revision
- Locks are essential for ensuring isolation and consistency in concurrent database systems.
- Shared locks support read concurrency; exclusive locks prevent write conflicts.
- Automatic lock management reduces developer burden but requires understanding to avoid contention issues.
- Lock granularity and escalation directly affect system performance and scalability.