Next

Open psql session blocks order updates

On-call incidentEasy15 min

Your task

You're on call for the order service, which runs on one Postgres primary. At 16:35 an alert fires: 22% of PATCH /orders/:id requests fail with ERROR: canceling statement due to lock timeout, and the label-printing worker that updates order status has stopped making progress. Reads and new orders are fine. Database CPU is 12% and connections are at 80 of 400. Every failing request is for an order assigned to warehouse 3.

In pg_stat_activity you find one session with application_name = psql, user jordan_ops, state = idle in transaction, xact_start = 16:05, and last query:

UPDATE orders SET warehouse_id = 7 WHERE warehouse_id = 3 AND status = 'open';

At 16:04 Jordan from operations posted in the ops channel that warehouse 3 closed early for weather and they were moving its open orders to warehouse 7. Jordan's status says "in a meeting until 17:30". How do you triage and resolve this?

It covers

  • Row locks
  • Idle in transaction
  • Pg stat activity