Locks in Databases

Introduction

A database lock is a concurrency-control mechanism that coordinates access to shared data or metadata. When transactions attempt incompatible operations on the same resource, the database may make one wait, reject an operation, or choose one transaction as a deadlock victim. This coordination protects uncommitted changes and helps each isolation level deliver its promised behavior.

Locks are not the whole concurrency system. Modern engines also use Multi-Version Concurrency Control (MVCC), snapshots, validation, latches, and optimistic checks. A sound design understands which work is protected by locks, which reads use versions, how long protection lasts, and how the application responds to waits or retryable failures.

What Are Locks in Databases?

A lock represents a transaction's protected access to a database resource. The resource might be a row, key, index range, page, table, schema object, or database. A lock mode describes the operation being protected. The lock manager compares requested modes with locks already held and grants compatible requests while making incompatible requests wait.

For example, two customers may attempt to purchase the final product. Both can inspect product information, but their reservation updates must not silently succeed against the same inventory. Locking and transaction logic serialize the conflicting part of that workflow so the application can determine which reservation actually succeeded.

Why Database Locks Matter

Locks help prevent dirty writes, coordinate concurrent modifications, preserve relationships, and support isolation. They are central to money transfers, inventory changes, payroll updates, booking systems, and every workflow where concurrent transactions touch shared state.

A lock does not automatically make business logic correct. The transaction must still use an atomic statement, constraint, suitable isolation level, or guarded read-and-write sequence. Locking protects the resources selected by the engine; it cannot infer an unstated rule such as "never sell more than available inventory."

How Database Locking Works

  1. Transaction A requests access to a resource.
  2. The lock manager checks existing modes and compatibility.
  3. A compatible request is granted immediately.
  4. An incompatible request normally waits, fails immediately, or times out.
  5. Commit or rollback releases transaction-duration protection.
  6. A waiting transaction is awakened and rechecks its operation.

Exact acquisition and release rules depend on the engine, statement, execution plan, and isolation level. Locks can also be acquired incrementally as an operator scans data rather than all at once when a statement begins.

Two-Transaction Timeline

TimeTransaction ATransaction B
T1BeginsNot started
T2Updates account 101 and holds write protectionBegins
T3Continues processingAttempts a conflicting update and waits
T4Commits and releases protectionBecomes runnable
T5CompleteRe-evaluates and performs its update

The waiting statement does not simply continue with assumptions made before the wait. A database may re-evaluate conditions, and the application must check affected-row counts and current state.

Core Locking Terminology

TermMeaning
HolderTransaction that currently owns a granted lock
WaiterTransaction waiting for an incompatible request
ModeType of protected access, such as shared or exclusive
GranularitySize of the protected resource
BlockingWaiting caused by incompatible concurrent access
DeadlockCycle in which transactions wait for one another
EscalationReplacement of many fine locks with coarser protection
ContentionCompetition for a resource that limits concurrency

Shared Locks

A shared lock, often written as S, protects read access in locking-based implementations. Multiple transactions can usually hold compatible shared locks on the same resource, while an exclusive request must wait. Lock duration can be statement-level or transaction-level depending on isolation.

Do not assume every SELECT takes row-level S locks. MVCC engines and versioned isolation modes can return committed row versions without blocking a writer. Metadata locks or lightweight internal synchronization may still be involved.

Exclusive Locks

An exclusive lock, often written as X, protects a modification against incompatible access. Two transactions cannot normally hold exclusive locks on the same resource at the same time. Write protection generally remains until transaction completion so another session cannot observe or overwrite a partially committed change.

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 101;
COMMIT;

The engine may lock index keys as well as the row because every affected index must remain transactionally consistent.

Shared vs Exclusive Compatibility

Held modeRequested sharedRequested exclusive
SharedCommonly compatibleIncompatible
ExclusiveIncompatible in lock-based readsIncompatible

This simplified matrix explains the main idea, but real engines have additional modes and conversion rules. Versioned readers may not request a shared data lock, so they can read an older committed version while an exclusive writer proceeds.

Update Locks

Some systems, notably SQL Server, use an update lock to reduce conversion deadlocks. A transaction can acquire U protection while reading a row it intends to modify, then convert U to X for the update. Shared locks may remain compatible with U, but only one updater is selected for that resource.

An update lock is not a portable SQL feature and should not be assumed from the word UPDATE. Product documentation and execution behavior determine whether such a mode exists.

Intent Locks

Intent locks summarize fine-grained locking at a higher level. If a transaction owns an exclusive row lock within a table, an intent-exclusive lock on the table signals that fact. Another transaction can then determine whether a table-level request is compatible without inspecting every row lock.

Common conceptual modes include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). Intent locks usually coexist with compatible row activity; their purpose is hierarchy coordination rather than direct protection of every row.

Schema and Metadata Locks

Databases protect object definitions while statements compile or run. A long query can therefore delay ALTER TABLE, while a schema change can block ordinary statements waiting to access that object. These waits are easy to misdiagnose because the transactions may not be modifying the same data rows.

Keep schema migrations focused, understand online-DDL limitations, and monitor metadata blockers separately from row and key contention.

Key-Range, Gap, and Next-Key Locks

Row locks protect existing records, but serializable predicates may also need to protect places where matching rows could be inserted. A key-range lock protects an interval in an ordered index. InnoDB gap locks protect gaps between records, while next-key locks combine a record lock with an adjacent gap.

These mechanisms can prevent phantom inserts, but their scope depends on indexes, predicates, isolation, and execution plans. A missing index may require broad protection and sharply reduce concurrency.

Predicate Tracking

Logical predicate protection does not always mean a conventional blocking lock. PostgreSQL Serializable uses SIREAD predicate markers and dependency analysis. Conflicting work may proceed, but the engine can abort a transaction if the resulting dependency pattern could violate serializability.

The distinction matters operationally: one engine prevents an insert by waiting, while another allows both transactions to run and later produces a serialization failure. Applications must support the mechanism their database actually uses.

Lock Granularity

Granularity describes resource size. Fine locks on rows or keys allow unrelated work to proceed but consume more lock-manager memory. Coarse table or database locks use less tracking overhead but block more work. The optimizer and storage engine usually choose granularity according to the statement and workload.

Row-Level Locks

Row-level protection limits conflicts to targeted records and is well suited to transactional workloads where sessions update different customers, orders, or accounts. "Row lock" is often shorthand: an engine may actually lock an index record representing the row and can acquire additional locks for secondary indexes.

Page-Level Locks

A page lock protects a storage page containing multiple records. It reduces management overhead compared with many row locks but creates false contention when transactions need different rows on the same page. Availability and selection of page locks are product-specific.

Table and Database Locks

A table lock protects a complete table and is useful for some bulk operations, maintenance tasks, or explicit coordination. A database-level lock is even broader and is generally associated with administration, file operations, or engines with coarse write serialization. These modes simplify coordination at the cost of concurrency.

Rows vs Tables: The Tradeoff

FactorFine-grained locksCoarse-grained locks
ConcurrencyHigher for disjoint dataLower
Tracking overheadHigherLower
Blocking scopeNarrowBroad
Typical fitOLTP transactionsBulk or administrative work

The best choice is workload-dependent. Thousands of row locks can be appropriate when they preserve useful concurrency, while a planned table lock can be more efficient during an isolated bulk load.

Locks and Transaction Boundaries

BEGIN establishes an explicit transaction but does not necessarily lock data by itself. Locks arise as statements access resources. COMMIT makes changes durable and releases transaction-duration locks. ROLLBACK discards changes and releases them as the transaction ends.

A savepoint does not create an independent transaction. Rolling back to one may release some later-acquired locks on certain engines, but lock-release details vary. Never use a savepoint as a portable promise that every lock after it disappears.

Autocommit and Lock Duration

In autocommit mode, each statement is normally its own transaction, so transaction locks are released when that statement commits. Explicit transactions extend protection across statements. Accidentally leaving a pooled connection in an open transaction can therefore turn a short update into a long blocker.

Isolation Levels and Locking

Isolation levelTypical locking or versioning effect
Read UncommittedWeak read protection; dirty visibility may be possible
Read CommittedPrevents dirty reads; read protection often ends per statement
Repeatable ReadRetains row stability or uses a stable snapshot
SerializableProtects serial outcomes with ranges, validation, or dependency detection

These are outcomes, not universal lock recipes. PostgreSQL, MySQL, SQL Server, Oracle, and SQLite implement the same named levels differently.

Read Uncommitted and Read Committed

Read Uncommitted minimizes read isolation and may allow dirty data on engines that implement it. Writes still require protection because physical and transactional structures cannot be modified concurrently without coordination. Read Committed prevents ordinary dirty reads, but repeated statements can see newly committed data.

A locking Read Committed implementation may release shared locks after each statement. An MVCC implementation may instead provide a new committed snapshot for each command. Both can satisfy the level while producing different wait patterns.

Repeatable Read and Serializable

Repeatable Read keeps previously read data stable, either by retaining locks or by reading one transaction snapshot. SQL-standard phantoms can still be possible, although some products provide stronger snapshot behavior. Serializable adds protection for the outcome of predicate-based decisions and may use range locks or serialization checks.

Stronger isolation can increase waits, deadlocks, version retention, or aborts. Choose it because the business invariant requires it, and implement complete transaction retries where the engine documents retryable conflicts.

SELECT FOR UPDATE

SELECT ... FOR UPDATE is a pessimistic locking read supported in several databases. It selects rows while reserving them against conflicting modifications until transaction completion. It is useful when an application must inspect current state and then update those same rows.

BEGIN;
SELECT status, remaining_quantity
FROM inventory
WHERE product_id = 500
FOR UPDATE;

UPDATE inventory
SET remaining_quantity = remaining_quantity - 1
WHERE product_id = 500
  AND remaining_quantity > 0;
COMMIT;

Prefer a single conditional UPDATE when possible. It shortens the protected interval and lets the affected-row count determine success.

SELECT FOR SHARE

A share-locking read, written as FOR SHARE or a vendor-specific equivalent, lets compatible readers protect selected rows while preventing conflicting changes. Syntax and semantics differ by product, and ordinary foreign-key checks or MVCC reads may already provide the required behavior without an explicit share lock.

NOWAIT and SKIP LOCKED

NOWAIT requests an immediate error instead of waiting for a conflicting lock. SKIP LOCKED omits locked candidates and is useful for multi-worker job queues where each worker can claim a different available row. Neither option is a general correctness shortcut.

Skipping locked rows produces an intentionally incomplete view and can cause starvation or unfairness. Use it for queue-like work distribution, not financial totals, reports, or queries that require every matching row.

Explicit Table Locking

LOCK TABLE and vendor-specific hints can deliberately coordinate bulk or maintenance operations. Explicit locks increase coupling between code and one database's semantics, so document their order, duration, timeout behavior, and effect on readers before using them.

What Is Blocking?

Blocking occurs when a session waits for an incompatible lock held by another active transaction. Short blocking is normal coordination. It becomes a performance problem when the holder is slow, idle, waiting on a remote service, or touching an unnecessarily broad range.

Diagnose the head blocker rather than killing every waiter. The first transaction in the chain usually explains why many downstream requests are delayed.

Deadlocks and Lock Timeouts

A deadlock is a cycle: Transaction A holds one resource and waits for B, while B holds another and waits for A. The database detects the cycle and aborts a victim. A lock timeout is different: a transaction simply waits longer than the configured limit, even when no cycle exists.

ConditionCauseTypical result
BlockingIncompatible holderWait until release
DeadlockCircular waitOne transaction aborted
TimeoutWait exceeds limitStatement or transaction error

Acquire resources in a consistent order and retry the complete transaction after deadlock or documented serialization failures.

Lock Escalation

Escalation replaces many fine-grained locks with coarser protection to reduce lock-manager overhead. SQL Server is strongly associated with lock escalation, but its decision depends on memory, thresholds, and context rather than one universal row count. Escalation can transform localized work into table-wide blocking.

Batch large updates, maintain selective indexes, and monitor actual behavior. Disabling escalation without understanding memory impact is rarely the first remedy.

Lock Contention

Contention is repeated competition for the same resources. Common hot spots include one inventory row, a sequential queue head, monotonically increasing index pages, shared counters, and broad status scans. Symptoms include rising wait time, throughput flattening, deadlocks, and long latency tails.

Locks in PostgreSQL

PostgreSQL uses MVCC for ordinary reads, so readers generally do not block writers and writers do not block readers of older committed versions. Row-level locks protect modifications and explicit locking reads. Table-level modes coordinate operations from normal queries through schema changes.

PostgreSQL detects deadlocks and exposes activity through views such as pg_stat_activity and pg_locks. Serializable uses predicate dependency tracking rather than simply range-blocking every relevant insert.

Locks in MySQL InnoDB

InnoDB combines MVCC consistent reads with record, gap, and next-key locking. At Repeatable Read, ordinary consistent reads commonly use one transaction snapshot, while locking reads and modifications use current-read behavior. Index choice strongly affects which records and gaps are protected.

Use performance_schema, InnoDB transaction and lock-wait views, and deadlock reports to inspect contention. Remember that MySQL's behavior differs across storage engines; transaction claims here refer to InnoDB.

Locks in SQL Server

SQL Server exposes a rich hierarchy of shared, update, exclusive, intent, schema, and key-range modes. It can lock keys, pages, objects, and other resources and may escalate fine locks. Read Committed Snapshot and Snapshot isolation use row versions for many reads, changing reader-writer blocking behavior.

Dynamic management views such as sys.dm_tran_locks and request/session views help trace holders and waiters. Deadlock Extended Events provide the resource cycle and victim details needed for a durable fix.

Locks in Oracle

Oracle uses multiversion read consistency, so ordinary readers generally do not block writers. Row locks protect modified rows, and TM enqueues coordinate table-level concerns. SELECT ... FOR UPDATE explicitly reserves current rows for a later change.

Oracle detects deadlocks and reports an error rather than allowing indefinite cycles. Dynamic performance views can identify sessions, locked objects, blockers, and wait events, subject to privileges.

Locks in SQLite

SQLite coordinates access to a database file rather than acting like a client-server row-lock manager. Rollback-journal mode and WAL mode have different reader-writer interactions. WAL allows readers and a writer to overlap more effectively, but SQLite still permits only one writer at a time.

Keep write transactions short, configure a deliberate busy timeout, and do not assume SELECT FOR UPDATE semantics from server databases apply to SQLite.

Database Locking Comparison

DatabaseOrdinary readsNotable protection
PostgreSQLMVCC snapshotsRow locks, table modes, SSI predicate tracking
MySQL InnoDBMVCC consistent readsRecord, gap, and next-key locks
SQL ServerLocks or optional row versionsUpdate, intent, key-range, and schema modes
OracleMultiversion read consistencyRow locks and enqueue mechanisms
SQLiteJournal-mode snapshotsFile-level coordination with one writer

Banking and Money Transfers

A transfer must debit and credit as one transaction. Update accounts in a consistent order, verify the source balance with an atomic condition, and commit both changes together. Holding locks while calling an external payment service increases risk; use durable workflow records or an outbox rather than keeping the database transaction open across the network.

Inventory and Ticket Booking

Inventory and seat allocation need an atomic claim. A conditional update such as WHERE available_quantity > 0, a unique seat assignment, or a pre-created slot lets the write enforce the rule. Counting existing reservations and then inserting without predicate protection is vulnerable to concurrent over-allocation.

Job Queues and Multiple Workers

Workers can select a small ordered batch with a locking read and SKIP LOCKED, mark those jobs as claimed, and commit quickly. Processing should happen after the claim commits so slow work does not hold locks. Leases, attempt counters, and idempotent handlers support recovery if a worker fails.

Enrollment, Payroll, and Appointments

Enrollment limits resemble inventory capacity; payroll updates often affect many employees; appointments require uniqueness across provider and time. Each domain benefits from explicit constraints and narrow transactions. Locks coordinate conflicting attempts, while constraints provide the final authority against duplicate or invalid state.

Pessimistic Locking

Pessimistic locking reserves data before a likely conflict. It fits short, high-value operations where collisions are common and waiting is preferable to failed validation. Costs include blocking, deadlocks, and sensitivity to transaction duration.

Optimistic Locking

Optimistic locking reads without holding a long business lock and validates at write time, commonly with a version column. The update includes the original version and increments it only on success.

UPDATE documents
SET body = ?, version = version + 1
WHERE document_id = ?
  AND version = ?;

Zero affected rows means another transaction changed the document. The application must reload, merge, reject, or retry. Optimistic control is effective when collisions are uncommon, but it does not automatically protect multi-row predicates.

Pessimistic vs Optimistic Control

FactorPessimisticOptimistic
Conflict handlingWait before changeDetect at write
Best fitFrequent collisionsRare collisions
Main costBlocking and deadlocksFailed validation and retries
Long user think timePoor fitUsually better

Two-Phase Locking

Two-Phase Locking conceptually has a growing phase in which locks are acquired and a shrinking phase in which they are released. Strict 2PL holds write locks until commit or rollback, preventing other transactions from seeing uncommitted writes and simplifying recovery. Real products combine variants of locking with MVCC and other techniques.

Locks, ACID, and MVCC

Locks contribute to Isolation and help Atomicity remain invisible until commit, while logging and storage mechanisms provide Durability. MVCC stores multiple row versions so many readers can avoid waiting for writers. The two are complementary: even MVCC databases need write locks, metadata protection, uniqueness coordination, and cleanup rules.

A snapshot can provide stable reads without reserving future capacity. If two writers make decisions from the same snapshot, stronger serializable validation, a constraint, or an explicit guarded resource may still be required.

Locks and Indexes

Indexes reduce the number of rows examined and can narrow the ranges that require protection. A missing index can force a scan, acquire many locks, increase escalation risk, or protect a much broader interval. However, every extra index also needs transactional maintenance during writes.

Review actual execution plans and lock diagnostics together. The same SQL can take different paths as statistics, parameters, and data distributions change.

Long Transactions and Connection Pools

Transaction duration is the most important controllable locking factor. Do not hold transactions open for user input, file processing, sleeps, or remote calls. In connection pools, always commit or roll back in a guaranteed cleanup path and restore isolation and autocommit before returning a connection.

An "idle in transaction" session can retain locks and old versions even though it is doing no CPU work. Monitor transaction age, not only query duration.

Monitoring Locks

A useful lock investigation captures the holder, waiter, resource, lock mode, wait duration, transaction age, SQL text, plan, application endpoint, and safe correlation identifier. Begin with the oldest head blocker and determine why its transaction remains open.

  • PostgreSQL: inspect pg_stat_activity, pg_locks, and blocker helper functions.
  • MySQL: inspect Performance Schema lock and transaction wait data plus InnoDB diagnostics.
  • SQL Server: inspect request, waiting-task, transaction-lock, and session DMVs.
  • Oracle: inspect session, lock, transaction, and wait-event performance views.
  • SQLite: measure busy errors, write-transaction duration, checkpoint pressure, and journal mode.

Protect sensitive bind values and personal data when recording SQL or diagnostic snapshots.

Reducing Lock Contention

  • Keep transactions short and free from remote calls.
  • Update resources in a consistent order.
  • Add selective indexes for protected predicates.
  • Use small, deterministic batches for bulk changes.
  • Replace check-then-write logic with conditional statements.
  • Partition hot resources where the business model permits.
  • Use row-versioned reads for reporting when supported and appropriate.
  • Move long analytics to replicas or reporting stores.

Retries and Idempotency

Deadlocks, serialization failures, and selected lock timeouts can be retryable, but retry policy must use documented error codes. Roll back, discard the old snapshot, wait with bounded exponential backoff and jitter, then execute the complete transaction again.

External side effects require an idempotency key or transactional outbox. Retrying only the final statement can reuse a stale decision and make correctness worse.

Testing Database Locks

Use at least two independent connections and deterministic barriers. Session A acquires protection and pauses; Session B requests a known conflicting operation; the test observes whether B waits, fails, skips, or proceeds. Then A commits or rolls back and the test verifies B's final result and the committed state.

  1. Test shared and exclusive compatibility.
  2. Confirm lock duration at each supported isolation level.
  3. Reproduce a deadlock with opposite resource order.
  4. Verify timeout and retry handling.
  5. Compare indexed and scan plans.
  6. Test pool cleanup after success and failure.
  7. Load-test hot rows, batches, and queue workers.

Arbitrary sleeps make concurrency tests flaky. Coordinate exact points with latches, test hooks, or session-level signaling.

Common Locking Mistakes

  • Assuming every database uses the same lock modes.
  • Believing every SELECT blocks writers.
  • Starting a transaction before slow non-database work.
  • Using broad locking reads without suitable indexes.
  • Holding locks while waiting for users or services.
  • Retrying one statement instead of the whole transaction.
  • Treating lock waits as deadlocks.
  • Killing waiters without identifying the head blocker.
  • Using SKIP LOCKED where complete results are required.
  • Depending on locks instead of enforceable constraints.

Database Locking Best Practices

  1. Define the business invariant and conflicting schedule first.
  2. Prefer constraints and atomic conditional writes.
  3. Use the weakest isolation that still preserves correctness.
  4. Keep transactions narrow, short, and observable.
  5. Access shared resources in a consistent order.
  6. Index predicates that govern locking decisions.
  7. Handle documented retryable errors with idempotency.
  8. Monitor blockers, wait duration, deadlocks, and transaction age.
  9. Test against the production database family and schema.
  10. Reassess behavior after plan, index, or workload changes.

Interview Questions and Answers

What is a database lock?

It is a concurrency-control mechanism that protects access to a database resource and coordinates incompatible transaction operations.

What is the difference between shared and exclusive locks?

Shared modes commonly coexist for reads, while an exclusive mode protects a modification and conflicts with other access requiring the same resource.

Do readers always block writers?

No. MVCC and row-versioned isolation can serve an older committed version without taking a conflicting row read lock.

What is lock escalation?

It is replacement of many fine locks with coarser protection to control lock-management overhead, potentially increasing blocking scope.

How is a deadlock different from blocking?

Blocking is a one-way wait that may resolve normally. A deadlock contains a circular wait and requires the database to abort a participant.

How can lock contention be reduced?

Shorten transactions, index predicates, use consistent resource order, batch large changes, avoid remote calls in transactions, and redesign hot resources.

Practical Exercises

  1. Open two sessions and demonstrate a blocked update to one row.
  2. Compare an ordinary read with a locking read on your database.
  3. Create a two-row deadlock using opposite update order.
  4. Add an index and compare the plan and lock footprint.
  5. Build a queue claim using the product's skip-locked feature.
  6. Implement an optimistic version-column update.
  7. Find the head blocker using native diagnostic views.
  8. Verify transaction cleanup in a connection-pool failure path.

Quick Reference

ConceptPurpose or meaning
SharedProtects compatible read access in lock-based designs
ExclusiveProtects a conflicting modification
IntentSummarizes lower-level protection in a hierarchy
Range or gapProtects places where qualifying keys could appear
BlockingNormal wait behind an incompatible holder
DeadlockCircular wait resolved by aborting a victim
TimeoutWait exceeded a configured duration
MVCCProvides versions so many reads avoid writer locks
Optimistic controlDetects conflict at write or commit
Pessimistic controlReserves resources before a likely conflict

Production Locking Workflow

  1. State the invariant and expected transaction boundary.
  2. Map the exact rows, keys, ranges, and metadata involved.
  3. Confirm the engine's isolation and MVCC behavior.
  4. Inspect plans and indexes for protected predicates.
  5. Reproduce contention with deterministic sessions.
  6. Capture holders, waiters, modes, and transaction age.
  7. Fix transaction scope or data design before adding hints.
  8. Implement complete, bounded, idempotent retries.
  9. Load-test throughput and tail latency.
  10. Monitor the behavior after deployment.

Key Takeaway

Database locks coordinate incompatible access so concurrent transactions can preserve valid committed state. Shared, exclusive, intent, range, schema, and vendor-specific modes protect different resources at different granularities. Isolation level, MVCC, indexes, execution plans, and transaction boundaries determine which locks appear and how long they remain.

Production-safe locking begins with a clear business invariant. Prefer atomic writes and constraints, keep transactions short, order access consistently, index protected predicates, diagnose the head blocker, and retry only documented transient failures as complete idempotent transactions. The goal is not to eliminate every wait; it is to preserve correctness with predictable and measured concurrency.