Using pgadmin to Check the Status of Postgres Alert Locks
I'm trying to use Postgres advisory locks in my Django app and it looks like the purchase is frozen.
I want to use a GUI pgadmin
to see which locks have been acquired and which have not. I tried using it and went to pg_locks
and looked around for a while, but I couldn't find where I could see which locks were acquired. How can I see this?
source to share
Specific locks are listed in pg_locks
, with locktype = advisory
and objid
containing the locked value:
regress=> SELECT pg_advisory_lock(12345);
pg_advisory_lock
------------------
(1 row)
regress=> SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+-----------------+---------+----------
relation | 144654 | 11090 | | | | | | | | 2/24979 | 22097 | AccessShareLock | t | t
virtualxid | | | | | 2/24979 | | | | | 2/24979 | 22097 | ExclusiveLock | t | t
advisory | 144654 | | | | | | 0 | 12345 | 1 | 2/24979 | 22097 | ExclusiveLock | t | f
(3 rows)
regress=> SELECT objid, mode, granted FROM pg_locks WHERE locktype = 'advisory';
objid | mode | granted
-------+---------------+---------
456 | ExclusiveLock | t
12345 | ExclusiveLock | t
(2 rows)
For two-digit locks, the first part is in classid
and objsubid
is equal 2
(instead of 1
for locks with one argument):
regress=> SELECT pg_advisory_lock(123, 456);
pg_advisory_lock
------------------
(1 row)
regress=> SELECT classid, objid, mode, granted, objsubid FROM pg_locks WHERE locktype = 'advisory';
classid | objid | mode | granted | objsubid
---------+-------+---------------+--------------------
123 | 456 | ExclusiveLock | t | 2
(1 row)
Update:
The field mode
is the lock mode.
regress=> SELECT pg_advisory_lock_shared(1234);
pg_advisory_lock_shared
-------------------------
(1 row)
regress=> SELECT classid, objid, objsubid, mode, granted FROM pg_locks WHERE locktype = 'advisory';
classid | objid | objsubid | mode | granted
---------+-------+----------+-----------+---------
0 | 1234 | 1 | ShareLock | t
(1 row)
If the given lock is not obtained at all, there will be no row for it.
regress=> SELECT classid, objid, objsubid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory'
AND objsubid = 1 /* One-argument form lock */
AND objid = 1235; /* argument = 1235 */
classid | objid | objsubid | mode | granted
---------+-------+----------+------+---------
(0 rows)
If the lock is locked pending another session, it will have granted = 'f'
.
You can see which process id is holding / trying to acquire the lock using the field pid
. Joining to pg_stat_activity
can be useful, as is self- joining to pg_locks
, to see which session is blocking a given lock.
For details, see the user manual forpg_locks
.
source to share
You may be able to list it yourself from the data provided. Here's a note from the documentation :
provided true on a string representing the lock held by the specified transaction. False indicates that this transaction is currently waiting to acquire this lock, which implies that some other transaction is holding a contested lock on the same locked object.
source to share