Database Lock Contention and Long Transactions Explained

Quick answer

Database lock contention occurs when a session requests a lock that conflicts with one already held, so the requester waits. A long transaction extends the lifetime of its locks and snapshot even if it is not currently running a statement. In a backend service, waiting statements keep database connections occupied; the application pool then queues new requests, deadlines expire, and retries can amplify the same contention.

Diagnose a directed blocker/waiter chain. Start from user impact and pool wait, identify active database waits, use PostgreSQL 18 pg_stat_activity, pg_locks, and pg_blocking_pids() to connect waiting sessions to blockers, and inspect transaction age, state, application ownership, operation, and intended business work. Lock wait, deadlock, statement timeout, pool wait, and serialization failure are different outcomes and need separate evidence.

Mitigate with the least dangerous action: stop admitting optional work, bound lock and statement waits, fix transaction scope, order updates consistently, batch smaller units, or cancel a verified safe statement. Cancellation does not undo committed side effects. Terminating a session rolls back its open transaction but can trigger application retries or interrupt business workflows. Do not kill the oldest PID automatically; establish ownership, rollback cost, idempotency, and remaining deadline first.

Shared order-history incident stage

In the shared order-history incident, the slow read and a reporting transaction overlap continued order updates and a schema/index remediation attempt. Some sessions wait on row or transaction-related locks; a queued DDL request can also affect later lock acquisition depending on its requested mode. Application connections remain checked out while statements wait. Pool acquisition time climbs even for requests whose SQL would otherwise be fast.

The original query plan still contains a cardinality estimate error, but that does not explain every elapsed second after contention begins. Plan execution evidence and lock-wait evidence describe different clocks, so responders keep both rather than charging blocked time to the slowest-looking plan node.

The reporting session is idle in transaction after reading a consistent snapshot. It may not be the direct blocker for every writer, but it retains transaction state and delays MVCC cleanup. Separately, an order update holds locks longer than intended while the application performs network work inside its transaction. The incident needs both MVCC, VACUUM, and Table Bloat and lock-chain evidence.

Retries turn waiting into amplification. A client times out, but cancellation does not promptly reach all server work; the service admits another attempt with no sufficient remaining deadline. New sessions join the pool or lock queue. Increasing pool size would admit more contenders without increasing locked-resource throughput.

Operations first protect high-priority order writes, pause low-priority reports and migration work, and identify the blocker/waiter graph. They cancel only an owned, safe statement or terminate a session under a documented rollback and retry decision. Recovery is verified with user SLI, in-flight requests, pool wait, lock wait, transaction age, retries, and correct order state.

Core mechanism and evidence boundary

PostgreSQL has table-level, row-level, page-level, transaction, advisory, and other lockable objects. Lock modes have an explicit compatibility matrix. Table names such as ROW EXCLUSIVE do not mean the lock covers only a single row. DML and DDL acquire different modes, and many locks are held until transaction end. Row locks block conflicting writers and lockers, not ordinary snapshot reads, but foreign keys, uniqueness, updates, and DDL introduce additional relationships.

A waiter is a session whose requested lock is not granted. A blocker holds or awaits locks in a way that prevents progress. pg_blocking_pids(pid) applies PostgreSQL’s lock-manager knowledge and is generally safer than inventing a join that misses soft-blocked queues. pg_locks exposes active lock requests, while pg_stat_activity contributes session state, transaction start, wait event, application name, and current or last query under privilege rules.

Lock queues deserve attention during DDL. A migration requesting a strong table lock may wait behind an existing transaction. Later operations can then wait according to lock-manager ordering and compatibility instead of simply bypassing the queued request. A DDL command that appears inactive can therefore be part of a widening incident. Set a short reviewed lock_timeout before the change, observe waiters, and abandon the attempt cleanly when its lock budget is unavailable.

Row ownership and table-lock evidence should be interpreted together. PostgreSQL ordinarily represents many row-lock waits through transaction-ID relationships rather than listing every row as a durable object that an operator can safely map to business data. Avoid brittle “locked row” dashboards. Use application operation context, blocker relationships, controlled reproduction, and the schema’s known constraints.

Advisory locks form another namespace. They are application-defined and can be session- or transaction-scoped. PostgreSQL does not know the business invariant encoded by an advisory key. Document acquisition order, release scope, failure handling, and ownership; never publish raw keys when they derive from tenant or entity identifiers.

Deadlock is not prolonged blocking. PostgreSQL detects a cycle in which transactions wait on one another and aborts one participant after its deadlock detection behavior applies. A lock timeout is a configured client/session decision to stop waiting. A statement timeout bounds total statement execution from its applicable timing boundary. A pool timeout occurs before a database session is acquired. A serialization failure comes from concurrency-control rules rather than necessarily from a lock cycle.

Transaction age begins at the transaction boundary, not at the latest statement. idle in transaction means the session is not executing but still owns a transaction. It can retain locks and a snapshot horizon. idle outside a transaction does not have the same implication. Measure transaction_age_seconds, state, wait, and ownership together.

This article uses PostgreSQL 18 views, lock modes, wait events, pg_cancel_backend, and pg_terminate_backend. They are PostgreSQL-specific, not a SQL standard or universal database API. lock_wait_seconds, pool_wait_seconds, and transaction age are teaching fields, not a stable OpenTelemetry semantic convention. PIDs, query identifiers, trace IDs, tenant IDs, and raw SQL belong in restricted diagnostics, never metric labels.

Minimal reproducible PostgreSQL 18 example

This lock experiment reuses the synthetic order model from Database Query Performance for Backend Systems and adds only the rows needed for contention. Use two or three sessions against synthetic data in a disposable database. Session A begins an update and deliberately remains open for a short, bounded exercise:

BEGIN;
UPDATE orders
SET status = 'PAID'
WHERE id = 1001;
-- Do not commit until Session B has demonstrated the bounded wait.

Session B applies local timeouts before requesting the same row:

BEGIN;
SET LOCAL lock_timeout = '750ms';
SET LOCAL statement_timeout = '2s';
UPDATE orders
SET total_cents = total_cents + 100
WHERE id = 1001;
ROLLBACK;

From a restricted diagnostic session, inspect the graph without exporting SQL text:

SELECT a.pid,
       a.application_name,
       a.state,
       now() - a.xact_start AS transaction_age,
       a.wait_event_type,
       a.wait_event,
       pg_blocking_pids(a.pid) AS blocking_pids
FROM pg_stat_activity AS a
WHERE a.datname = current_database()
  AND (a.xact_start IS NOT NULL OR a.wait_event_type = 'Lock')
ORDER BY a.xact_start NULLS LAST;

Inspect lock requests for a PID only within the lab:

SELECT locktype, mode, granted, relation, transactionid, virtualxid
FROM pg_locks
WHERE pid = 12345;

Replace the example PID locally; do not publish production PIDs. End Session A with ROLLBACK, then confirm Session B’s outcome and that the connection returns to its pool. Reproduce a two-order update in opposite orders to study deadlock separately, with bounded data and cleanup. Do not turn the exercise into an automatic production termination script.

Failure modes and dangerous misconceptions

Killing the oldest transaction. Age alone does not establish blocking, ownership, rollback cost, or business safety. Build the graph and evaluate impact.

Joining pg_locks incorrectly. Lock objects have different identity fields, and lock queues can include soft blocking. Prefer documented functions and validate custom queries.

Treating every wait as a deadlock. Normal blocking can resolve; deadlocks are cycles detected and broken by the server. Timeouts are configured bounds. Preserve separate outcomes.

Increasing the pool. More sessions can deepen the lock queue and consume memory. Admission must reflect database throughput and deadlines.

Holding transactions across remote calls. Network latency and retries extend lock lifetime. Move nontransactional work outside and keep the database unit minimal while preserving invariants.

Retrying every database error. Retry only classified transient outcomes with remaining deadline, bounded attempts, backoff, jitter, and idempotency. A repeated lock conflict can amplify load.

Setting no lock timeout for DDL. An operational command can wait and interact with subsequent requests. Use a reviewed lock budget and observe the queue.

Assuming cancellation reverts committed work. It can stop cooperative in-flight work; already committed effects remain and may need business compensation.

Using only current query duration. A session can be idle in a much older transaction, and a waiter can have spent time in the application pool before reaching PostgreSQL. Compare transaction, statement, lock, and request clocks.

Ignoring the blocker’s downstream work. Cancelling a report may be safe, while terminating a payment or fulfillment transaction may violate an operational workflow even if database rollback is correct. Database atomicity does not replace business recovery analysis.

Creating an automated PID killer. PIDs are recycled and ownership changes. A delayed action can target the wrong session. Use fresh server-side predicates, least privilege, human-approved policy, and post-action verification.

Security, privacy, capacity, and cost implications

Activity and lock diagnostics can expose raw SQL, PII or personal data, application names, client addresses, object names, and business timing. Restrict access, project only necessary columns, avoid query text in shared dashboards, audit cancellation privileges, and retain incident artifacts for a defined period.

Never use PID, virtual transaction, transaction ID, query ID, user, tenant, order, request, trace, or span identity as metric labels. Use bounded route, operation, environment, database role, wait class, and outcome. The detailed blocker graph belongs in an access-controlled incident record.

Long transactions consume connection capacity, retain locks and snapshots, increase rollback time, and can delay vacuum. Terminating one can create CPU, WAL, I/O, and retry bursts during rollback and recovery. Model the operational cost before intervention.

Timeouts protect capacity only when the application handles them correctly. A too-short value can increase aborts and retries; a too-long value permits queue growth. Define budgets from user deadlines and measured service time, then test every layer.

Testing and production validation

Test lock behavior with deterministic synthetic rows and bounded sessions. Cover same-row updates, ordered multi-row updates, opposite ordering, DDL versus DML, foreign-key interactions relevant to the schema, statement cancellation, session termination, and idle-in-transaction handling. Assert data correctness and transaction outcomes.

Use the real application pool in staging. Measure checked-out connections, acquisition wait, database sessions, lock wait, statement duration, timeouts, retries, and user tails under concurrency. Confirm cancellation reaches the driver and connection, rollback completes, and a connection is healthy before reuse.

Validate timeout precedence: the downstream lock and statement budgets must fit inside the remaining user deadline. Ensure retry logic sees the classified error, has idempotency, and cannot exceed an attempt or concurrency budget. Simulate an unavailable diagnostic role so responders do not depend on privileged raw query text.

After production mitigation, compare the blocker graph, transaction ages, lock wait, pool wait, retries, and user SLI. Verify order invariants and any interrupted job. Keep monitoring through rollback completion and replica catch-up. A vanished blocker does not alone prove recovery.

Test deploy interactions explicitly. Begin a realistic old application transaction, attempt each planned DDL statement with a bounded lock timeout, and then run new and old application versions concurrently. Confirm the migration either acquires its lock within budget or exits without a partial compatibility state. Measure how queued DDL affects later reads and writes.

For continuous validation, alert on bounded user or resource symptoms rather than individual lock identities: sustained pool wait, lock-wait duration, old active or idle transactions, retry amplification, and SLO burn. Provide drill-down to a restricted blocker graph. This keeps metric cardinality stable while preserving actionable evidence for responders.

After changing transaction scope, test failure at every boundary: before the first write, between writes, before commit, after commit but before response, and during retry. The expected database and business outcome must be explicit for each point. Faster lock release is not acceptable if it moves an invariant outside the transaction without a replacement protocol.

Operations checklist

• Confirm affected journey, time window, route, operation, database role, and user outcome.
• Separate pool acquisition, statement execution, lock wait, rollback, and retry time.
• Use wait events and pg_blocking_pids() to build an explicit blocker/waiter graph.
• Record transaction age, state, application ownership, and business operation without exporting SQL values.
• Distinguish blocking, deadlock, lock timeout, statement timeout, serialization failure, and pool timeout.
• Pause optional reports, backfills, and DDL before admitting more contenders.
• Keep transactions free of remote calls and unrelated application work.
• Apply lock and statement budgets inside the remaining end-to-end deadline.
• Cancel or terminate only with ownership, side-effect, rollback, idempotency, and retry analysis.
• Keep PIDs, transaction IDs, query text, tenant data, and correlation IDs out of metric labels.
• Verify connection reuse, rollback completion, business correctness, replicas, and retry volume.
• Close after the user SLI and capacity evidence remain healthy.

Official sources

• PostgreSQL 18: Explicit Locking — lock modes, compatibility, row locks, and deadlocks; accessed 2026-08-04.
• PostgreSQL 18: Viewing Locks — pg_locks and activity relationships; accessed 2026-08-04.
• PostgreSQL 18: pg_blocking_pids — documented blocker identification; accessed 2026-08-04.
• PostgreSQL 18: pg_stat_activity — state, transaction timing, query, and wait fields; accessed 2026-08-04.
• PostgreSQL 18: Client Connection Defaults — lock, statement, and idle-transaction timeout settings; accessed 2026-08-04.

Continue the learning path

Translate the blocker graph back into code with Spring Service Layers and Transaction Boundaries Explained, especially when a remote call or propagation choice extends connection and lock ownership. During a live order-service event, Production Spring Boot Incident Troubleshooting keeps session cancellation conditional on transaction outcome, idempotency, and user recovery evidence.

Understand retained versions in MVCC, VACUUM, and Table Bloat, then stage lock-sensitive changes through Zero-Downtime Database Schema Migrations. Database Deadlocks focuses on cycles and safe retry, while Database Connection Pooling explains the application capacity boundary. Continue through System Design or Topics.