Locks and Throughput
What is a lock?
A lock is a special software or hardware primitive that provides mutual exclusion. A resource can be locked for exclusive access by one concurrent application, which must unlock it again after use. Other contending applications have to wait, which delays their completion.
Locks are acquired and released by transactions during execution. The DBMS uses locks internally to enforce the isolation property of ACID. How locks are used to implement ACID is not part of any DBMS API — it is part of the “secret sauce” implemented by each vendor.
Granularity
Locks can be placed along a spectrum:
| Granularity | What is locked | Effect |
|---|---|---|
| Very coarse-grained | The entire database | Only one transaction can run at a time |
| Coarse-grained | A whole table | Concurrent access to different tables allowed |
| Medium-grained | A page or block of rows | Moderately concurrent |
| Fine-grained | A single row | High concurrency |
| Very fine-grained | A single data value (field) | Maximum concurrency |
Locking and throughput
If transactions lock large amounts of data, or lock frequently used data, fewer concurrent updates can be supported. This degrades throughput.
The relationship between locking and throughput:
Finer granularity generally permits higher concurrency, but at the cost of greater overhead in managing many small locks. There is a trade-off: managing thousands of row-level locks consumes more CPU and memory than managing a handful of table-level locks.
Important observation
If transactions lock large amounts of data or frequently used data, fewer concurrent updates can be supported. This has direct implications for schema design:
- Tables with many columns: a transaction updating one column may lock the entire row, blocking updates to other columns.
- Hot rows: rows that are updated frequently become contention points.
- Indexes: updating an indexed column requires locking the index structure as well as the row.
Lock types
| Lock type | Allows | Typical SQL |
|---|---|---|
| Shared (read) lock | Other readers, no writers | SELECT |
| Exclusive (write) lock | Neither readers nor writers | UPDATE, DELETE, INSERT |
Multiple transactions can hold shared locks on the same resource simultaneously. Only one transaction can hold an exclusive lock, and no shared locks may coexist with it.
Summary
- Locks enforce mutual exclusion, enabling isolation in concurrent systems.
- Granularity ranges from entire-database to single-field locks.
- Coarser locks reduce concurrency and throughput.
- Finer locks increase overhead but permit more parallelism.
- Locking is an internal DBMS mechanism, not exposed in the API.