A developer's reference to the locking mechanisms database management systems use to handle concurrency, maintain data integrity, and prevent conflicts between simultaneous transactions.
Allows multiple transactions to read the same resource
simultaneously. No transaction may write while a shared lock is held.
This is the foundation of READ concurrency.
Grants full write access to a resource and blocks all other
transactions — both reads and writes. Used during
INSERT, UPDATE, and
DELETE operations.
A hybrid lock used during the search phase of an
UPDATE. It is compatible with shared locks but prevents
other update locks, avoiding deadlock when multiple transactions
attempt to update the same row.
A hierarchical lock placed at a higher level (e.g., table) to signal that a transaction intends to acquire a finer-grained lock (row or page) below. Enables efficient lock escalation.
Protects the structure of database objects during
DDL operations like ALTER TABLE. Ensures no queries
execute while a schema modification is in progress.
Used when bulk-loading data into a table with the
TABLOCK hint. Allows multiple concurrent bulk
operations to insert into the same table while blocking other
traffic.
Protects a range of index keys to prevent
phantom reads under the SERIALIZABLE
isolation level. Lock covers both existing and non-existent
rows in the range.
Locks an entire 8 KB data page in the buffer pool. Provides a middle ground between row and table locking, balancing concurrency and memory overhead.
The most granular lock type, applied to individual rows in a table. Maximizes concurrency by allowing other transactions to modify different rows in the same page or table.
Database locks are applied at different levels of granularity depending on the operation, the isolation level, and the optimizer's choice. Coarser locks (table-level) reduce overhead but limit concurrency; finer locks (row-level) maximize concurrency at the cost of managing more lock structures.
The fundamental split: Shared (S) locks allow multiple readers but block writers; Exclusive (X) locks grant sole access to a resource. Every other lock type builds on this core principle.
Intent locks (IS, IX, SIX)
signal intent at the table level so the database can efficiently check
compatibility without scanning every row-level lock. This is defined
by the Multiple Granularity Locking Protocol.
Each lock type card above includes a color-coded compatibility matrix. ● Green = request allowed, ● Red = request blocked, ● Blue = conversion, ● Yellow = special case. This matrix-based visualization lets you instantly identify conflicting operations.