Lock Contention

Contention appears as latency that rises with concurrency while CPU and I/O stay unremarkable — the database is busy waiting, not working.

Finding the blocker

-- PostgreSQL: who blocks whom, right now.
SELECT blocked.pid AS blocked_pid, left(blocked.query, 60) AS blocked_query,
       blocking.pid AS blocking_pid, left(blocking.query, 60) AS blocking_query,
       now() - blocking.xact_start AS blocking_xact_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- MySQL 8.0
SELECT r.trx_mysql_thread_id AS waiting, r.trx_query AS waiting_query,
       b.trx_mysql_thread_id AS blocking, b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id;

The blocking transaction is nearly always long-running application code — a transaction left open across an external call, or a batch job that locks a large range.

Deadlocks

A deadlock is a cycle: transaction A holds a lock B wants, and B holds one A wants. Engines detect the cycle and abort one participant.

-- PostgreSQL: deadlocks are logged; count them per database.
SELECT datname, deadlocks FROM pg_stat_database ORDER BY deadlocks DESC;

-- MySQL: the most recent deadlock in detail.
SHOW ENGINE INNODB STATUS\G      -- LATEST DETECTED DEADLOCK
-- Log all of them, not just the last:
SET GLOBAL innodb_print_all_deadlocks = ON;

Reducing them:

• Acquire locks in a consistent order across every code path. Most deadlocks are two code paths updating the same two rows in opposite orders.
• Keep transactions short, and touch as few rows as possible.
• Update in a deterministic sequence — sort the ids before a multi-row update.
• Index the columns used in WHERE clauses of updating statements. Without an index, InnoDB locks every row it examines, which turns a targeted update into a range lock.

Design changes that remove contention

Do not read-modify-write in the application when an atomic expression will do:

UPDATE counters SET value = value + 1 WHERE id = 42;    -- one statement, no window

Shard hot rows. A single counter row updated by every request serialises the whole workload. Keep N counter rows and sum on read.

Move work out of the transaction. Compute, call external services and serialise data before BEGIN.

Use optimistic concurrency where contention is genuinely rare: a version column, and a retry if zero rows were updated. It holds no locks while the application thinks.

Consider SKIP LOCKED for queue-like access, so workers take different rows instead of queueing on the same one:

SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 10 FOR UPDATE SKIP LOCKED;

Lock timeouts

ALTER ROLE app_service SET lock_timeout = '2s';     -- PostgreSQL
SET SESSION innodb_lock_wait_timeout = 5;           -- MySQL

A bounded wait converts an invisible pile-up into a fast, visible error. This matters most for migrations, where DDL waiting for a lock blocks every subsequent query on the table.