Troubleshooting · Databases
Diagnose PostgreSQL Lock Waits and Deadlocks
A lock wait is waiting for another transaction; a deadlock is circular waiting. PostgreSQL can abort a transaction when detecting a deadlock. Slow queries do not always have poor plans;…
Technical review:
Architecture and operating model
A lock wait is waiting for another transaction; a deadlock is circular waiting. PostgreSQL can abort a transaction when detecting a deadlock. Slow queries do not always have poor plans; a blocked query may simply be waiting for a lock.
Inspect pg_stat_activity, pg_blocking_pids and transaction start times together. Query text may contain sensitive data; restrict viewing permissions/report scope. Idle in transaction can indicate an application holding a transaction open while doing no queries.
Identify the blocker owner, purpose and rollback impact first. pg_cancel_backend cancels a statement; transaction state still requires handling. Random termination can create large rollbacks and retry storms. Consistent lock ordering and short transactions provide lasting fixes.
- 1Slow transaction
- 2Wait type
- 3Root blocker
- 4Scoped intervention/design fix
Design parameters
- Time distinction
- Inspect query and transaction durations separately.
- Blocking graph
- Follow waiter-to-blocker chains to the root blocker.
- Timeout
- lock_timeout differs from statement_timeout; test application error handling alongside both.
Worked example
Session A updates account 1 while B updates account 2; if A then needs 2 and B needs 1, a cycle can form. Locking accounts in ascending ID order reduces this risk. After a deadlock, handle whole-transaction retry and external side effects.
Example commands: replace lab values and confirm permissions and software versions before use.
SELECT pid, application_name, state, wait_event_type, wait_event,
clock_timestamp()-xact_start AS transaction_age,
pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE wait_event_type = 'Lock' OR state = 'idle in transaction';Troubleshooting
| Observation | Likely cause / distinction | Verification |
|---|---|---|
| Persistent lock wait | Long or idle transaction. | Inspect xact_start and blocker application. |
| Repeated 40P01 | Inconsistent lock ordering. | Compare both transaction access sequences. |
Acceptance checks
- Verify the wait type.
- Find the root blocker.
- Identify the intervention owner.
- Assess rollback impact.
- Standardize lock order.
- Prevent retry storms.
Related concepts
Locks, waits and deadlocks
Concurrent operations use locks or versioning to preserve integrity. Long transactions can block others; a deadlock forms a cycle of mutual waits. The database may terminate one participant, requiring safe application retries. Examine blocked and blocking queries together. Arbitrarily reducing isolation can change consistency guarantees. Test shorter transactions, consistent access order and suitable indexes under the same workload.
Transaction boundary
A transaction groups operations that an application treats as jointly successful or failed. Database atomicity does not automatically include email delivery or changes in other services. Define the commit point and retry behaviour. Evaluate idempotency to prevent processing the same request twice. Test connection loss before and after commit separately: a client-side error does not always mean the transaction failed.
Queues and concurrency
A queue holds work arriving faster than a resource can process it. More concurrency can improve utilization up to a point, then increase waiting time. In a stable system Little’s law relates L = λ × W: average work in the system equals throughput multiplied by average total time. Units must agree. At 2,000 operations/s and 5 ms total time, roughly 10 operations are present concurrently. This is a planning relationship; queue limits, bursts and highly variable service times still require measurement.
Latency distribution
Latency is the time between starting an operation and receiving its result. An average can hide a small number of very slow operations; medians and p95/p99 percentiles answer different questions. Network RTT, storage waits, processor queues and application processing contribute to end-to-end time. State whether measurements come from the client or server. Check whether increases coincide with traffic growth, maintenance or capacity limits. Record normal and peak-hour baselines before selecting an alert threshold.
Telemetry and time correlation
Telemetry combines logs, metrics and events that explain system behaviour. A log describes an event, a metric shows behaviour over time, and a distributed trace follows a request across components. Clock differences can make one event appear to occur at several times. Use synchronized clocks, reliable source identifiers and consistent time-zone handling. Alarm design should consider duration and user impact alongside thresholds. Monitor gaps in collection separately: absence of logs must not be interpreted as absence of incidents.
Resource contention
Contention occurs when workloads sharing CPU, memory, storage or a link make each other wait. Apparent spare total capacity can hide a hot core or single-queue bottleneck. Correlate backup jobs, antivirus scans, index maintenance and user traffic on a common timeline. Confirm the bottleneck before adding resources. Run a workload alone and with its usual competitors to separate shared-resource effects, and report peak-hour latency alongside average utilization.
Primary documentation
Prepared by the Doz Teknoloji technical team using the primary references below. Calculations and lab scenarios state their assumptions; validate the applicable product version before rollout.