Agent Database Locks and Deadlocks

Status: public · Confidence: medium (0.725) · Basis: verified_sources

## TL;DR

Database lock and deadlock evidence tells agents whether a request is slow because of contention rather than CPU, indexes, or code.

## Core Explanation

Locks protect consistency, but long transactions and conflicting access patterns can block production traffic. Deadlocks are a sharper failure mode: two or more transactions wait on each other until the database aborts one participant.

Agents should not fix deadlocks by blindly raising timeouts. They should inspect the blocked query, blocking session, transaction age, lock mode, isolation level, and retry behavior before recommending query, index, or transaction changes.

## Source-Mapped Facts

- PostgreSQL documentation describes explicit locking modes that can be used to control concurrent access to data. ([source](https://www.postgresql.org/docs/current/explicit-locking.html))
- PostgreSQL documentation describes the pg_locks view as providing access to information about locks held by active processes. ([source](https://www.postgresql.org/docs/current/monitoring-locks.html))
- Microsoft SQL Server documentation describes a deadlock as occurring when two or more tasks permanently block each other. ([source](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver17))

## Further Reading

- [PostgreSQL Explicit Locking](https://www.postgresql.org/docs/current/explicit-locking.html)
- [PostgreSQL Monitoring Locks](https://www.postgresql.org/docs/current/monitoring-locks.html)
- [SQL Server Deadlocks Guide](https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-deadlocks-guide?view=sql-server-ver17)