Find transactions that are locked (and who to blame for!)PostgreSQL is great at looking in the sense it does its best effort to not lock at all! Nevertheless, in order to keep things coherent, even PostgreSQL requires locks to be acquired at diffent times and with different levels. PostgreSQL provides a rich set of statistical views to introspect a running database, including single transaction activity and locks. Therefore, combining these information togethere there’s a chance to find out if and who is blocking due to not performing a lock release. The idea proposed in this article is quite simple and represents a poor man way to find out locks information. The example presented here is a live example I produced while preparing some teaching material. The path to discover what and who is blocking is done thru three main steps:
- find out if there are blocked transactions, this is done querying the activity statistic view having a glance at its state and/or the time it is running since (long running transactions could be possibly waiting for a lock to acquire);
- find out what blocked transaction are waiting for and on the other hand, what lock is not acquired, and this is extracted by the lock statistical view, so that it is possible to see the lock not acquired and, by mean, the transaction that have acquired it;
- decide what to do this is not an easy task. When the blocking transaction has been found, is it worth terminating or should it be kept running for another period of time in the case it volountary releases the lock? The choice strongly depends on the execution context of the database and transaction itself.
Step 1: is anyone waiting for a lock acquisition?Use the
pg_stat_activityview to see if is there any transaction waiting for some kind of lock:
The above query provides a list of longest waiting transactions, so for example:
# SELECT query, backend_start, xact_start, query_start, state_change, state, now()::time - state_change::time AS locked_since, pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event_type IS NOT NULL ORDER BY locked_since DESC;
Now, the transaction associated to the process id
-[ RECORD 1 ]---|----------------------------------------- query | UPDATE persona SET nome = upper( nome ); backend_start | 2017-11-14 22:37:55.88444+01 xact_start | 2017-11-14 22:37:58.186149+01 query_start | 2017-11-14 22:38:16.362419+01 state_change | 2017-11-14 22:38:16.362424+01 state | active locked_since | 00:05:10.078716 pid | 786 wait_event_type | Lock wait_event | transactionid
786is waiting for a lock hold by another transaction (see
wait_eventcolumns). Moreover, the transaction has waited for around five minutes, and this is a quite clear indication that there is a possible lock contention. It is therefore worth investigating some more. Let’s get some information out from the
pg_locksview with the process identifier, or better, let’s join
pg_stat_activityso that it is possible to get information at glance:
thanks to the process id it is possible to confirm that the transaction is waiting for a lock:
# SELECT a.usename, a.application_name, a.datname, a.query, l.granted, l.mode, transactionid FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid WHERE granted = false AND a.pid = 786;
Now, the transaction
-[ RECORD 1 ]----|----------------------------------------- usename | luca application_name | psql datname | testdb query | UPDATE persona SET nome = upper( nome ); granted | f mode | ShareLock transactionid | 3031
3031is the one that is preventing the
ShareLockto be acquired.
Step 2: see who is blockingHaving find out the transaction id that is preventing another one to acquire a lock it is possible to get some information about what such transaction is actually performing:
Of course, we it is interesting to see acquired locks by transaction
# SELECT a.usename, a.application_name, a.datname, a.query, l.granted, l.mode, transactionid, now()::time - a.state_change::time AS acquired_since, a.pid FROM pg_locks l JOIN pg_stat_activity a ON a.pid = l.pid WHERE granted = true AND transactionid = 3031;
Now, the transaction kept the
usename | luca application_name | psql datname | testdb query | UPDATE persona SET cognome = lower( cognome ); granted | t mode | ExclusiveLock transactionid | 3031 acquired_since | 00:27:20.264908 pid | 780
ExclusiveLockfor more than 27 minutes (this is ad-hoc example)! It is quite clear something nasty is happening, but at least it is clear so far the transaction that is blocking other transactions and what query it is executing causing the lock to not be released.
Step 3: what now?Having found out the transaction not releasing the lock, the query it is performed, chances are an administrator can understand if this is a normal behavior or not. Please note that the system provided us with the information about both running queries:
UPDATE persona SET cognome = lower( cognome );
UPDATE persona SET nome = upper( nome );
but really try to better understand what is going on before brutaly nuke a running transaction!
# SELECT pg_terminate_backend( 780 );