Lock management is essential in a production environment and should be monitored closely. There are several types of locks, such as deadlocks, advisory locks, and table locks. For tables, there are several lock modes; the only mode that blocks SELECT statements is ACCESS EXCLUSIVE. Access to the exclusive mode can be acquired through statements that alter the table's physical structure, such as ALTER, DROP, TRUNCATE, VACUUM FULL, CLUSTER, and REINDEX and finally using the LOCK command in the ACCESS EXCLUSIVE mode.
The table's looks might cause some queries to wait until the lock is lifted, depending on the lock mode and the query type. Also, some queries may wait for a long time due to keeping the transaction uncommitted or the connection idle in transaction.
In the case of idle in-transaction queries, one could detect the locks using the pg_stat_activity and pg_lock tables, as follows:
SELECT lock1.pid as locked_pid, stat1.usename as locked_user, stat1.query as locked_statement, stat1.state as state, stat2.query as locking_statement, stat2.state as state, now() - stat1.query_start as locking_duration, lock2.pid as locking_pid, stat2.usename as locking_user FROM pg_catalog.pg_locks lock1 JOIN pg_catalog.pg_stat_activity stat1 on lock1.pid = stat1.pid JOIN pg_catalog.pg_locks lock2 on (lock1.locktype,lock1.database,lock1.relation, lock1.page,lock1.tuple,lock1.virtualxid, lock1.transactionid,lock1.classid,lock1.objid, lock1.objsubid) IS NOT DISTINCT FROM (lock2.locktype,lock2.DATABASE, lock2.relation,lock2.page, lock2.tuple,lock2.virtualxid, lock2.transactionid,lock2.classid, lock2.objid,lock2.objsubid) JOIN pg_catalog.pg_stat_activity stat2 on lock2.pid = stat2.pid WHERE NOT lock1.granted AND lock2.granted;
To test the query, let's open three sessions to create locks on the car table. In the first session, let's lock the table, as follows:
car_portal=# BEGIN; BEGIN car_portal=# LOCK TABLE car_portal_app.car IN ACCESS EXCLUSIVE MODE; LOCK TABLE car_portal=#
In the second session, let's try to delete all entries from the car table, as follows:
car_portal=# DELETE FROM car; -- this will wait until the lock is removed
In the third session, when executing the query that finds the locks, one should get a result as follows:
car_portal=# x Expanded display is on. car_portal=# SELECT lock1.pid as locked_pid, ...; -[ RECORD 1 ]-----+----------------------------------------- locked_pid | 8536 locked_user | postgres locked_statement | DELETE FROM car; state | active locking_statement | LOCK TABLE CAR IN ACCESS EXCLUSIVE MODE; | state | idle in transaction locking_duration | 00:01:02.378 locking_pid | 11884 locking_user | postgres