Locks and Contention

A 11:14 page. A single customer is hitting “submit order.” The request has been queued for twenty seconds. The application pool is healthy. The database is showing pg_stat_activity_count{state="active"} at ten, well below max_connections. The application is timing out for one user, not for everyone. The on-call engineer queries pg_locks and finds forty entries with granted = false, all on the same oid — the orders table — all waiting on the same transactionid. A long-running report against the same table is holding the row lock and serialising every writer behind it.

This is lock contention. It is the second most common cause of “the database is slow” reports (after query latency), and the harder shape to detect because the database CPU stays low, the pool stays empty, and the disk stays idle. Every waiting connection is doing nothing the dashboard can see; the database is paying for it in session time and lock-manager cycles.

What locks and contention are

A database lock is a coordination primitive the engine uses to serialise access to a shared resource. There are three families in production:

• Row-level locks (PostgreSQL ROW SHARE, ROW EXCLUSIVE; MySQL RECORD LOCK, GAP LOCK, NEXT-KEY LOCK). Acquired implicitly by SELECT FOR UPDATE, INSERT, UPDATE, DELETE. Held until end of transaction. The most common production shape.
• Table-level locks (PostgreSQL ACCESS EXCLUSIVE, SHARE, EXCLUSIVE; MySQL TABLE LOCK). Acquired by DDL, LOCK TABLE, certain bulk operations on MySQL. Rare in steady state; a frequent cause of incidents when a migration script runs against a busy table.
• Advisory locks (PostgreSQL pg_advisory_lock). Acquired deliberately by application code or by tools (pg_dump, vacuum). Useful for application-level coordination; a common cause of incidents when a forgotten pg_advisory_lock call has no matching pg_advisory_unlock.

Each lock has a mode (the granularity of access granted) and a state (granted or waiting). Locks are acquired hierarchically: a row lock requires a row-share table lock, a table-level DDL operation requires an ACCESS EXCLUSIVE table lock which blocks every other writer. A row-lock waiter that needs the table to drain will wait for every holder of the same table-mode to release first.

  Holder                       Waiter
  ------                       ------
  Transaction T1               Transaction T2
  BEGIN                        BEGIN
  UPDATE orders SET status = ...; UPDATE orders SET status = ...;
  -- holds ROW EXCLUSIVE        -- BLOCKED waiting on ROW EXCLUSIVE
                                  state = "active"
                                  wait_event_type = "Lock"
                                  wait_event = "transactionid"
COMMIT;                       -- continues only after T1 commits

Contention is the metric of how many transactions are waiting, how long they are waiting, and which locks they are waiting for. A database with no contention has zero waiting rows in pg_locks. A database with contention has dozens or hundreds, all on the same relation or transaction.

Why a sysadmin cares

Contention is a leading indicator of two distinct production shapes:

1. The lock-holder shape. A long-running transaction holds a row lock; every subsequent writer queues behind it; the table hot-spot grows; the application’s tail latency rises past the user-visible timeout. The fix is in the application (commit faster, scope transactions smaller) or in the database schema (reduce the hot row’s fan-out).
2. The lock-mode shape. A migration or VACUUM FULL acquires an ACCESS EXCLUSIVE lock; every reader and writer stops. The fix is in the change-management process (do DDL during a maintenance window, or with a tooling that avoids the blocking lock such as pg_repack/pg_squeeze).

The two shapes have different evidence. The first produces a queue of normal-mode waiters on a single relation. The second produces a single waiting lock with mode = AccessExclusiveLock on the same relation. The diagnostic order that distinguishes them is the same five queries for both.

How it works

Locks live inside the database’s lock manager, a hash table keyed by resource type (relation, transaction, tuple, advisory). The lock manager is a hash table of lock objects, each one a counter of holders (granted) and a queue of waiters (blocked). Contention manifests as the queue growing.

   pg_locks view (PostgreSQL)
   ----------------------------------------------------------------
   locktype | relation | mode         | granted | pid | query
   ---------+----------+--------------+---------+-----+----------------
   relation | orders   | RowExclusive | true    | 1234| UPDATE orders...
   relation | orders   | RowExclusive | true    | 5678| UPDATE orders...
   tuple    | orders   | Exclusive    | true    | 1234| UPDATE orders...
   tuple    | orders   | Exclusive    | false   | 5678| UPDATE orders... (waiting)
   transactionid |        Exclusive | true    | 1234|
   transactionid |        Exclusive | false   | 5678| (waiting on T1)

A row waiting on transactionid is a transaction-id wait: it means the holder is in the middle of a transaction and the waiter cannot proceed until it ends. The mode is Exclusive on the transaction id, and the lock-type row holds the waiter in the queue until the transaction commits.

In MySQL, the equivalent is performance_schema.data_locks: each row is a granted or waiting lock; the lock type, index name, and lock mode identify the resource. The events_waits_current and events_waits_history_long views show what the waiters are doing; the events_statements_* views show what query the waiters are stuck inside.

A deadlock is a special case where two transactions hold locks the other needs. The PostgreSQL backend detects the cycle and aborts one of the two (the one with the lower priority, configurable per session). The killed transaction sees ERROR: deadlock detected. The surviving transaction proceeds. This is logged in pg_stat_database.deadlocks and in the database’s stderr.

How to configure it

The exporter is configured through pg_locks and pg_stat_database queries. Both are present in the default collector set for the PostgreSQL exporter; neither requires special configuration. The MySQL exporter collects data_locks through the perf_schema collector.

PostgreSQL — enable the relevant collectors.

# /etc/default/prometheus-postgres-exporter
ARGS="--collector.locks \
      --collector.database \
      --collector.stat_activity"

The --collector.locks flag emits per-grant-state lock counts as a single Prometheus series; finer-grained lock analysis (per-mode, per-relation) requires a custom query file.

Custom queries.yaml for per-relation lock states.

# queries.yaml (excerpt)
- name: pg_lock_relations
  query: |
    SELECT
      pg_class.relname AS relation,
      pg_locks.mode     AS mode,
      pg_locks.granted AS granted,
      count(*)          AS count
    FROM pg_locks
    LEFT JOIN pg_class
      ON pg_locks.relation = pg_class.oid
    WHERE pg_class.relname IS NOT NULL
    GROUP BY 1, 2, 3
    ORDER BY 1, 2, 3 DESC;
  metrics:
    - relation:
        usage: LABEL
    - mode:
        usage: LABEL
    - granted:
        usage: LABEL
    - count:
        usage: GAUGE

The exporter will then expose one sample per (relation, mode, granted) tuple, suitable for a Grafana heatmap panel.

MySQL — enable the lock collectors.

# /etc/default/prometheus-mysqld-exporter
ARGS="--collect.perf_schema.data_locks \
      --collect.perf_schema.data_lock_waits \
      --collect.info_schema.innodb_trx"

The exporter emits mysql_perf_schema_data_lock_waits_total and a per-engine breakdown. The InnoDB transaction view is exposed through collect.info_schema.innodb_trx with each trx id as a label.

Alerts.

# /etc/prometheus/rules/locks.yaml (excerpt)
groups:
  - name: locks
    rules:
      - alert: DatabaseLockWaits
        # Any waiting lock state sustained for more than five minutes.
        expr: pg_locks_count{granted="false"} > 0
        for: 5m
        labels:
          severity: page
        annotations:
          summary: 'Lock waits on {{ $labels.instance }}'

      - alert: DatabaseDeadlockSpike
        # Even one deadlock per minute on a production database is suspect.
        expr: rate(pg_stat_database_deadlocks_total[5m]) > 0
        for: 5m
        labels:
          severity: ticket

      - alert: LockWaitTimeHigh
        # The mean time a waiter spends blocked on a lock.
        expr: pg_locks_wait_seconds > 5
        for: 5m
        labels:
          severity: ticket

The first alert catches steady-state contention. The second catches the cyclic shape. The third catches the long-wait case where a waiter has been blocked for seconds without release.

How to validate it

Every step is READ-ONLY.

# 1. Confirm the exporter is publishing lock-state data.
curl -sf http://10.0.4.7:9187/metrics | grep -E '^pg_locks_count'

# HELP pg_locks_count Number of locks by relation and mode.
# TYPE pg_locks_count gauge
pg_locks_count{relname="orders",mode="RowExclusive",granted="true"} 14
pg_locks_count{relname="orders",mode="RowExclusive",granted="false"} 6
pg_locks_count{relname="audit_log",mode="RowExclusive",granted="true"} 2

# 2. Read the waiting locks directly. Sorted by wait time, oldest first.
psql -h 10.0.4.10 -U db_exporter -d app <<'SQL'
SELECT
  pg_class.relname AS relation,
  pg_locks.mode,
  pg_locks.granted,
  date_trunc('second', now() - xact_start) AS wait_age,
  left(pg_stat_activity.query, 60) AS query
FROM pg_locks
LEFT JOIN pg_class ON pg_locks.relation = pg_class.oid
LEFT JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_locks.granted = false
  OR pg_locks.mode = 'AccessExclusiveLock'
ORDER BY pg_locks.granted DESC, wait_age DESC NULLS LAST
LIMIT 10;
SQL

 relation |      mode       | granted | wait_age |             query
----------+-----------------+---------+----------+--------------------------------
 orders   | RowExclusive    | false   | 00:00:42 | UPDATE orders SET status = $1
 orders   | RowExclusive    | false   | 00:00:38 | UPDATE orders SET status = $1
 orders   | RowExclusive    | false   | false   | SELECT * FROM orders FOR UPDATE

# 3. Read the deadlock count from the database's stats view.
psql -h 10.0.4.10 -U db_exporter -d app \
  -c "SELECT datname, deadlocks, conflicts FROM pg_stat_database WHERE datname='app';"

 datname | deadlocks | conflicts
---------+-----------+-----------
 app     |         3 |         0

# 4. Read MySQL data locks from performance_schema.
mysql -h 10.0.4.10 -u db_exporter -p \
  -e "SELECT object_name, lock_type, lock_mode, lock_status \
      FROM performance_schema.data_lock_waits \
      LIMIT 10;"

# 5. Confirm the alert rules parse and evaluate.
promtool check rules /etc/prometheus/rules/locks.yaml

The first four outputs confirm the exporter is publishing, the database has visible waiting locks, the relation is identifiable, the deadlock counter is reporting, the alert rules parse. The fifth step is load generation; do not run it on a production host.

How it can fail

Six failure shapes cover the overwhelming majority of production lock-contention incidents.

1. The single hot-row lock. All writers to a single row (for example, an accounts row, a session table, a counter row). Symptom: pg_locks_count{relname="accounts",granted="false"} climbs on every transaction burst; the same row is the target; the buffer-pin counter (pg_stat_io) shows the row’s block is constantly contended. Fix: redesign the schema (sharded counter row, optimistic locking, batching).
2. The DDL lock storm. A migration script runs an ALTER TABLE that acquires AccessExclusiveLock. Symptom: a single lock on the relation, in AccessExclusiveLock mode, with all readers and writers queued; pg_stat_activity shows readers as waiting. Fix: run the migration with lock_timeout set, or use pg_repack/pg_squeeze for a rebuild without the blocking lock.
3. The advisory-lock leak. Application code calls pg_advisory_lock(...) but never calls the matching pg_advisory_unlock(...). Symptom: pg_locks_count{locktype="advisory",granted="true"} rises without bound; the application’s otherwise-rare op eventually blocks on the leaked lock. Fix: instrument the application’s unlock; use pg_try_advisory_lock for short critical sections.
4. The idle-in-transaction holder. A connection opened a transaction, took a row lock, and never commits. Symptom: pg_stat_activity_count{state="idle in transaction"} is non-zero; pg_locks shows the same pid holding a long-lived row lock. Fix: client-side statement_timeout and the application transaction discipline (the previous lesson’s leak shape).
5. The deadlocked pair. Two transactions hold row locks the other needs. Symptom: pg_stat_database_deadlocks counts the aborts; the database log shows ERROR: deadlock detected; one of the two retries. Fix: the underlying ordering is wrong; fix the application to take locks in the same order across paths.
6. The application pool as the lock holder. HikariCP or a similar pool holds the connection for the duration of a long in-process transaction. Symptom: a row lock held while the application is in an external call (HTTP, Redis, or sleep). The same as the previous lesson’s leak shape but visible through lock waits rather than pool metrics. Fix: application transaction scoping.

How to troubleshoot it

Diagnose in this order.

1. Read pg_locks ordered by wait_age desc. The longest waiting session is the one with the most user-visible impact. Confirm it’s a real waiter (granted = false).
2. Read the state and wait_event of the waiters. A wait_event_type of Lock is what you want; any other wait_event_type is not a lock-wait and not this lesson.
3. Identify the holding lock. Join pg_locks to pg_stat_activity for the waiter, then to pg_locks for that waiter’s pid to find the granted lock; join to pg_class to find the relation.
4. Inspect the holding query. Is it an UPDATE? A SELECT FOR UPDATE? An application wait inside a transaction? A foreign-key check?
5. Read pg_stat_database.deadlocks and conflicts. A deadlocks counter that climbs is a different shape (the application takes locks in inconsistent order).
6. Check recent application changes. A new FOR UPDATE clause, a new long-running report, or a new batch processing job is the most common cause of a sudden contention rise.

Security implications

Lock contention has a small but real surface.

• pg_locks exposes transaction IDs. Each row in pg_locks carries a pid, a transactionid, and a query snippet. PIDs and query snippets are not secret, but they can be correlated against other logs to map activity. Treat the exporter endpoint as confidential.
• Advisory locks reveal application logic. An advisory_lock(bigint) that uses a deterministic key (for example, a hash of a feature flag name) tells the reader what feature the application is locking. Bind the exporter to an internal address.

Performance implications

Performance comes from three levers, all in the database’s configuration.

• max_locks_per_transaction. PostgreSQL default 64. Determines the size of the lock manager hash table (max_locks_per_transaction * max_connections). Raise cautiously; the cost is shared memory.
• lock_timeout. Per-session; the maximum time a transaction will wait for a lock before failing. Set this at the application level so that application code surfaces the failure quickly rather than holding the slot.
• statement_timeout. Per-session; the maximum time a statement runs. A statement that holds a row lock for longer than the application timeout is a bug; setting the server-level timeout is a coarse safety net.

The cost of lock contention is paid in wait time, not in CPU. A database at 2% CPU with a thousand waiting sessions is in worse shape than a database at 50% CPU with no waiters.

Production guidance

• Alert on granted = false lock count sustained for any period above a threshold.
• Read pg_stat_database.deadlocks daily; the rate of deadlocks is a leading signal of an application-side ordering bug.
• Set lock_timeout at the application level; do not rely on the database’s default of “wait forever”.
• Schedule DDL in maintenance windows or use a no-blocking rebuilder when the row count makes the locking operation too long.
• Audit pg_advisory_lock callers in the application quarterly; ensure each caller has a matching unlock.
• Pair pg_locks with pg_stat_activity so the application’s view and the database’s view meet in one query.

Verification

You should now be able to answer:

• What are the three families of database locks and what causes each in production?
• What is the difference between a granted=false row in pg_locks and a deadlocked transaction?
• What two alerts are worth defining for lock contention, and what is the right severity for each?
• What is the most common cause of a sudden contention rise after a deployment?
• How does pg_locks differ from the application’s view of the same contention?

Quiz