Monitoring Locks and Conflicts

Imagine you’re working in an office where all the doors get locked because someone forgot their keys inside a room. That’s pretty much how locks work in PostgreSQL. If a query or transaction locks a resource, other operations trying to access that same resource have to wait until it’s done. This can cause delays, conflict scenarios, and in the worst case, bring your system to a halt.

When do locks happen?

Locks in PostgreSQL are used to manage concurrent access to data. They happen:

1. When you run write operations: UPDATE, DELETE, INSERT.
2. When you have transactions that hold onto a resource longer than needed.
3. When different transactions are fighting over the same resource.

A real database is a “battlefield” for resources, and even if you think your system is running perfectly, one careless transaction can “jam everything up” like a bad merge in Git.

Lock Analysis Tools: pg_locks

pg_locks is a PostgreSQL system view that shows current locks held and awaited by transactions. It answers the question: "Who’s holding a lock and who’s waiting?"

Main fields in pg_locks:

• locktype: type of lock (for example, relation, transaction, page, tuple).
• database: database ID.
• relation: table ID (if the lock is related to a table).
• mode: lock mode (for example, RowExclusiveLock, AccessShareLock).
• granted: flag showing if the lock is granted (true) or if the transaction is still waiting for it (false).

Note: PostgreSQL uses what’s called a “lock hierarchy.” That means different operations can put less strict locks (like AccessShareLock for reading data) or stricter ones (ExclusiveLock for changing table structure).

Example: viewing all current locks

SELECT *
FROM pg_locks;

But if you just dump everything from pg_locks, you’ll get way too much noise. Let’s try something more useful!

Example: locks that haven’t been granted yet (so transactions are waiting)

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

What’s going on here?

• We filter for rows where granted = false, meaning the lock hasn’t been granted yet.
• relation::regclass converts the table ID to its name for readability.

The output might look like this:

These queries help you figure out which table/resource is locked and which transaction might be the culprit.

Conflict Analysis: pg_blocking_pids()

Locks are only half the trouble, but what if one transaction is blocking another? PostgreSQL gives you a handy way to find the “bad guy” using the pg_blocking_pids() function.

The pg_blocking_pids() function returns a list of process IDs (pid) that are blocking the current transaction.

Example: finding transactions that are blocking others

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

What’s happening here?

• We use the pg_stat_activity view to get active processes in the system.
• The pg_blocking_pids(pid) function returns a list of blockers for each pid. If the list isn’t empty (length > 0), the process is blocked.

Example output:

The transaction with pid = 4567 is blocked by processes 1234 and 5678. We’ve found our “bad guys.”

Terminating Blocking Processes

Once you’ve found the blocking processes, you can stop them using the pg_terminate_backend() function:

SELECT pg_terminate_backend(1234); -- "Kill" process 1234

But be careful! Forcibly killing a process can roll back data in the current transaction. Use this “nuclear button” only as a last resort.

Practical Example: Lock Analysis Scenario

Let’s say we have a university database with students and enrollments tables. Several transactions are trying to write to enrollments at the same time, and we’re running into locks.

1. Identifying locks:

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. Finding blocking processes:

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Clearing locks:

Force-terminate one of the conflicting processes:

SELECT pg_terminate_backend(1234); -- Terminate process 1234

Note: Before you “kill” a process, try to figure out why the lock happened. Maybe you need to rethink your transaction logic.

Common Mistakes and How to Avoid Them

Locks often happen because of bad transaction management. For example:

Mistake: one transaction holds a lock for too long without doing anything (the “idle in transaction” state).

Solution: keep an eye on transaction states using pg_stat_activity and end “stuck” transactions.

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Mistake: forgot to use indexes in queries, which led to table-level locks.

Solution: optimize your queries by adding indexes for frequently used conditions.

“Who’s Waiting for Whom” Table Output

For easier troubleshooting, you can build a dependency tree showing which transaction is blocking which:

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

Result:

You can see here that process 5678 is blocking 1234, and that one is blocking 4567.