LEARN. PRACTICE. GROW.Create an explainable wait
The local fixture has two teaching balance rows, each holding 100 units. Session A opens a transaction and changes the first to 90 without committing. B opens another transaction and tries to subtract five units from the same row. A third client observes the server while B waits. Ordering is controlled and the data belongs only to the exercise. There are no real banking transactions. This setup attributes blocking to a known relationship rather than immediately concluding that a slow query needs another index or more CPU. The hypothesis is validated before choosing an intervention.
Combine state, wait and blocker
At the observed point, A is idle in transaction with xact_start set. B is active, but wait_event_type is Lock. Applying pg_blocking_pids to B’s PID includes A’s PID. These facts are compatible: active identifies a query in progress even when waiting for a resource. The holder need not be executing SQL at that moment to retain locks from an open transaction. In an incident report, record the time, database, client application, transaction age and blocking relationship. Avoid attributing all elapsed time to CPU consumption.
An ordinary read can continue
The observer performs SELECT without FOR UPDATE and receives 100, the committed value. It does not receive the 90 still private to transaction A and does not need the same row lock requested by writer B. Being able to query data therefore does not establish that writes are free from blocking. This distinction explains incidents where dashboards work while an update batch stalls. The experiment concerns a specific row conflict; other locks, including table locks used by some DDL operations, can block reads. Always establish the operation, lock mode and object involved.
Cancel the waiting query
The coordinator sends pg_cancel_backend to B’s PID, created by the script itself. The function returns true and the client receives SQLSTATE 57014 with a user-request diagnostic. The connection stays open, but its explicit transaction enters an error state; the next SELECT returns 25P02 until ROLLBACK occurs. Signaling and transaction outcome are different evidence. In a pooled application, a connection responding at the transport level may still need transaction cleanup before returning to the pool. The workshop demonstrates recovery of this connection without implementing or validating an actual pool.
Canceling an idle session does not close its transaction
In a new wait, the script sends cancellation to holder A while it is idle in transaction. The signal is sent, but A remains in that state and B remains blocked. There is no active A query to cancel at that point. Then, only in the disposable cluster, pg_terminate_backend with a positive timeout terminates A; the observer confirms that its PID disappears. The uncommitted change to 90 rolls back, B proceeds from 100 and returns 95. In production, terminating a session requires understanding ownership, lost work and impact under the authorized process. It is not an automatic recipe for every wait.
Preserve context and observation boundaries
The script uses a local administrative role to observe and signal only owned sessions. An application user may neither see the same details nor have the same permissions. Activity information also needs temporal context: a long monitoring transaction may retain a snapshot, and query_start for a nonactive session refers to its last query. Before acting on a PID, reconfirm identity and the current incident. The exercise closes connections and stops its cluster. It does not validate cross-team authorization, crash recovery, replication, representative load or actual application functional acceptance.
-- Read-only diagnosis: correlate state, transaction and blockers.
SELECT pid, application_name, state, xact_start, query_start,
wait_event_type, wait_event, pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE datname = current_database
AND pid <> pg_backend_pid;
-- Run cancellation/termination only through the owned lab or an authorized runbook.
An idle-in-transaction session retains an uncommitted update; another appears active with wait_event_type=Lock.
Common pitfalls
Interpreting active as CPU use; confusing idle with idle in transaction; canceling the wrong session; taking true as recovery evidence.
Related topics: Transactions and concurrency · Diagnosis and support handover
Diagnosis should explain who waits, why and what work belongs to the holder. Intervention requires confirmation of its outcome.
Reference: Activity state and observation snapshots · PostgreSQL 18 reference semantics;18.6 current stable at review