Managing database locks

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...