Database lock pile-up
A database lock pile-up in SysAdmin Crisis: web servers look healthy while latency climbs. Learn the log signature and how to tell it apart from others.
Last updated:
The incident that punishes pattern matching. Everything on the rack looks fine, the traffic bar is normal, and the night is falling apart anyway.
What it does
On a database machine, queries start waiting on locks instead of finishing. Nothing crashes, no machine goes into saturation — but requests queue up behind the database, latency climbs, and uptime follows it.
The web tier is healthy from end to end. That is the whole character of this incident: the symptom is slowness across the fleet, and the cause is a single machine that never turns red.
The log signature
The database machine's PostgreSQL log carries the wait and duration lines, while at the edge only a latency warning shows up:
04:03:11 db-01 postgres[1187]: [2210] LOG: process 2210 still waiting for ShareLock on transaction 48212 after 1000.051 ms
04:03:11 db-01 postgres[1187]: [2210] DETAIL: Process holding the lock: 2207. Wait queue: 2210.
04:03:40 web-01 monit[598]: 'shop-api' failed protocol test [HTTP] at [localhost]:80/healthz [TCP/IP] -- HTTP: response time 2100 ms exceeds 1000 ms
04:04:12 db-01 postgres[1187]: [1187] FATAL: remaining connection slots are reserved for non-replication superuser connections
The session holding the lock only reports its duration; the ones waiting say
who they are waiting for. You find the root by walking the chain from the end
back to the start. db_stat lists active queries by ID,
wait time, and name, and marks the ones stuck on a lock.
If you leave it alone
The lock queue grows and latency stays high for the rest of the shift. Since no
machine goes DOWN, the uptime loss comes entirely from latency and then from
SLA debt. That is why it is possible to
lose a night with the whole rack green.
Telling it apart
| Looks similar | What separates it |
|---|---|
| DDoS flood | Both raise latency. A flood also raises CPU on the web machines and brings SYN warnings from the kernel; a lock pile-up brings neither. |
| Runaway job | Either can involve a database machine, but a runaway job locks up the CPU and names a process; this one names a query. |
When it shows up
| Earliest night | 3 |
| Needs | a db machine |
| Weight | 80 |
| At once | one instance |
The incident can close with a real uptime cost; the closing line is what tells that apart from a clean close.