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 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
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.137.167.205