Database Lock Types

A database lock is a promise the database makes about who may touch a resource while a transaction is in progress. The point is not just to block writes. The point is to preserve invariants while many sessions are reading and modifying the same data at once.

Lock names vary a little between database engines, but the main ideas are stable. It helps to group locks by compatibility, granularity, and purpose.

Compatibility locks

A shared lock allows reading but not modification. Several transactions can usually hold shared locks on the same row or page at the same time. This is useful when the database wants readers to coexist but still stop a writer from changing the resource underneath them.

An exclusive lock allows one transaction to modify a resource and blocks other readers or writers, depending on the engine and statement type. If two transactions both need exclusive access to the same row, one waits.

An update lock exists to reduce a common deadlock pattern. Imagine two transactions that both read a row and then both try to upgrade to exclusive locks to update it. If they start with shared locks, each can block the other during the upgrade. An update lock signals “I may write this later” and narrows that race.

Granularity locks

A row-level lock protects a single row. It gives high concurrency and is usually what application developers want for OLTP workloads.

A page-level lock protects a fixed-size page that contains multiple rows. It is cheaper for the engine to manage than many row locks, but it can block unrelated rows that happen to share the same page.

A table-level lock protects the entire table. It is simple and sometimes efficient for large maintenance operations, but it reduces concurrency sharply.

Many databases can escalate from many fine-grained locks to a coarser lock when memory pressure or lock count thresholds are reached. Lock escalation surprises teams because contention can suddenly widen from one hotspot row to a large section of the table.

Special-purpose locks

A key-range lock protects a range in an index, not just existing rows. It matters for preventing phantom reads. Without it, another transaction could insert a new row into a range that was already checked earlier in the transaction.

A schema lock protects object definition changes such as ALTER TABLE. This stops one session from changing the table structure while another session is compiling or running a query that depends on it.

A bulk update lock is used by some engines during bulk load operations. It reduces lock management overhead and can improve throughput, but it changes how much concurrent access is allowed during the load.

The failure modes are operational, not theoretical. Long transactions keep locks longer. Different access order across transactions causes deadlocks. Hot rows, such as counters or account balances, create queues of waiting sessions. Observability matters here: lock wait time, blocked session graphs, and deadlock logs often explain production incidents faster than query latency alone.

The practical rule is simple. Finer locks improve concurrency but cost more metadata and coordination. Coarser locks are cheaper for the engine but wider in blast radius. Good schema design, short transactions, and consistent access order usually matter more than memorising lock names.