Alright, let’s get our hands dirty with pg_locks. This is the system view that pulls back the curtain on the database’s backstage bouncers—the locks that are either granting or denying access to your data. Think of it as the VIP list for your rows and tables, and we’re here to see who’s on it, who’s waiting, and who’s causing a scene.

The first thing you need to internalize is that locking is not inherently evil. It’s the mechanism that maintains data integrity, ensuring two clowns (I mean, transactions) don’t try to change the same row at the same time. The problem isn’t locking; it’s contention—when transactions start queueing up behind each other, grinding your application’s throughput to a halt. pg_locks is your primary tool for seeing that contention in real-time.

The Anatomy of a Lock Row

Before you can diagnose a problem, you need to know what you’re looking at. Querying pg_locks gives you a wealth of info, but it’s a bit like reading a spreadsheet full of foreign keys. Let’s break down the most critical columns.

SELECT
    locktype,
    relation::regclass, -- This magic casts the OID to a readable table name
    mode,
    granted,
    pid,
    transactionid,
    virtualxid
FROM pg_locks
WHERE relation::regclass = 'your_problem_child_table'::regclass;
  • locktype: What kind of resource is being locked? Table (relation), row (tuple), transaction ID (transactionid), etc. This tells you the scope of the dispute.
  • relation: The OID of the table involved. Using ::regclass is your best friend here; it instantly translates that internal number into the table name you actually recognize.
  • mode: This is the heart of the matter. It’s the type of lock requested. The hierarchy is crucial. An AccessExclusiveLock on a table (taken by a DROP TABLE or VACUUM FULL) will block literally everything, even SELECTs. A mere RowExclusiveLock (taken by an UPDATE or DELETE) is far less intrusive.
  • granted: This is a simple but vital boolean. true means the transaction has the lock. false means it’s sitting there, patiently or not, waiting for whoever has the conflicting lock to release it. These are the rows that indicate active contention.

The Lock Modes Hierarchy and Conflicts

PostgreSQL’s lock modes exist in a hierarchy, and knowing which ones conflict is the key to understanding the traffic jam. You can think of it like this: a SELECT acquires an AccessShareLock on a table. This is like politely looking at a painting in a museum. It only gets annoyed if someone else tries to restructure the entire gallery (an AccessExclusiveLock). An UPDATE, however, acquires a RowExclusiveLock. This is like putting a “Wet Paint” sign on a single canvas. It doesn’t stop other people from looking at other paintings, but it will stop someone else from trying to put their own “Wet Paint” sign on the same one.

The most common conflict you’ll see is between RowExclusiveLock (from an UPDATE) and another RowExclusiveLock—two transactions trying to modify the same row. But the real performance killers are the heavier locks. A SHARE lock (used by CREATE INDEX) blocks an AccessExclusiveLock (and vice versa). It’s this hierarchy that explains why a long-running transaction holding a light lock can still be a problem if something else needs a heavy lock.

Connecting Locks to Queries

Seeing a granted = false lock is a five-alarm fire, but it’s useless if you don’t know which query is holding the lock. That’s where the pid (process ID) comes in. You can join pg_locks to another powerhouse view, pg_stat_activity, to see the full picture.

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.query AS blocking_query,
    blocking_activity.state AS blocking_state
FROM pg_locks AS blocked_locks
JOIN pg_stat_activity AS blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_locks AS blocking_locks ON (
    blocked_locks.locktype = blocking_locks.locktype AND
    blocked_locks.database IS NOT DISTINCT FROM blocking_locks.database AND
    blocked_locks.relation IS NOT DISTINCT FROM blocking_locks.relation AND
    blocked_locks.page IS NOT DISTINCT FROM blocking_locks.page AND
    blocked_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple AND
    blocked_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid AND
    blocked_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid AND
    blocked_locks.classid IS NOT DISTINCT FROM blocking_locks.classid AND
    blocked_locks.objid IS NOT DISTINCT FROM blocking_locks.objid AND
    blocked_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid AND
    blocked_locks.pid != blocking_locks.pid
)
JOIN pg_stat_activity AS blocking_activity ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

This query is a mouthful, but it’s the gold standard. It finds all ungranted locks (blocked_locks) and matches them to the granted lock (blocking_locks) that’s causing the blockage, then pulls in the actual SQL queries for both the victim and the perpetrator. Now you know exactly what to kill (the blocking_pid) if you need to, and, more importantly, what code in your application is causing the conflict.

Best Practices and Pitfalls

  1. Keep Transactions Short: This is the number one rule. The longer a transaction runs, the longer it holds its locks, creating a larger window for contention. Do your work in the database and get out. Don’t fetch a result set to your application, let the user ponder the meaning of life for minutes, and then try to update a row.
  2. AccessExclusiveLock is a Sledgehammer: Operations like ALTER TABLE and VACUUM FULL take this lock. They will bring all activity on that table to a standstill. Schedule them during maintenance windows. For adding a nullable column without a default, modern PostgreSQL can do it nearly instantly without the heavy lock, but for almost everything else, assume it’s disruptive.
  3. Beware of Lock Escalation: While PostgreSQL is generally good about row-level locking, it’s not a magic forcefield. If you update a huge number of rows in a single transaction, the system might decide it’s more efficient to just lock the entire table. Your “efficient” bulk operation just became a denial-of-service attack on your own app.
  4. The idle in transaction Connection: This is a classic foot-gun. A client begins a transaction, does a SELECT, and then just… sits on the connection without committing or rolling back. It’s holding every lock it acquired during that transaction open until the connection is closed or the transaction is terminated. Always, always ensure your application frameworks are properly finalizing transactions.