Learning PostgreSQL

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