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

 

Below I'll show you some useful SQLs to get postgreSQL locks information

Locks information overview

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

Transaction lock waits

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

Table lock waits, -- Clients waiting to acuire a lock on an extire table

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