DB‎ > ‎Postgres‎ > ‎

PostgreSQL locks

Thank you for visiting this page, this page has been update in another link 
How to check postgresql locks

Generally speaking, PostgreSQL makes locks less of an issue than in some other databases. However, high performance applications till need to be careful to miimize how often they take stronger locks.
You can find details of how locking works in the PostgreSQL
http://www.postgresql.org/docs/current/static/explicit-locking.html.
Or in the description of the pg_locks view,
http://www.postgresql.org/docs/current/static/view-pg-locks.html.

The information provided by pg_locks is very basic, here is an example tells you that gives you overview of locks information in running system

select locktype,virtualtransaction,transactionid,nspname,relname,mode,granted,cast(date_trunc('second',query_start) as timestamp) as query_start, substr(current_query,1,60) as query
from pg_locks
     left outer join pg_class on (pg_locks.relation = pg_class.oid) left outer join pg_namespace on (pg_namespace.oid = pg_class.relnamespace), pg_stat_activity
where not pg_locks.pid=pg_backend_pid() and pg_locks.pid = pg_stat_activity.procpid order by virtualtransaction;


 locktype | virtualtransaction | transactionid | nspname | relname | mode | granted | query_start | query
----------+--------------------+---------------+---------+---------+------+---------+-------------+-------
 

Here is another example tells you transaction lock waits, shows excess of clients that are waiting for a lock to be granted.

select locked.pid as locked_pid, locker.pid as locker_pid, locked_act.usename as locked_user,
       locker_act.usename as locker_user, locked.virtualtransaction, locked.transactionid,locked.locktype

from pg_locks locked,
        pg_locks locker,
        pg_stat_activity locked_act,
        pg_stat_activity locker_act
where
        locker.granted=true and
        locked.granted=false and
        locked.pid=locked_act.procpid and
        locker.pid=locker_act.procpid and
        (locked.virtualtransaction=locker.virtualtransaction or locked.transactionid=locker.transactionid);

 locked_pid | locker_pid | locked_user | locker_user | virtualtransaction | transactionid | locktype
------------+------------+-------------+-------------+--------------------+---------------+----------

One more for table lock waits, -- Clients waiting to acuire a lock on an extire table

select locked.pid as locked_pid, locker.pid as locker_pid, locked_act.usename as locked_user,
       locker_act.usename as locker_user, locked.virtualtransaction, locked.transactionid,locked.locktype
from pg_locks locked,
        pg_locks locker,
        pg_stat_activity locked_act,
        pg_stat_activity locker_act
where
        locker.granted=true and
        locked.granted=false and
        locked.pid=locked_act.procpid and
        locker.pid=locker_act.procpid and
        (locked.virtualtransaction=locker.virtualtransaction or locked.relation=locker.relation);

 locked_pid | locker_pid | locked_user | locker_user | virtualtransaction | transactionid | locktype
------------+------------+-------------+-------------+--------------------+---------------+----------




Comments