Hi Melvin, hi everyone else,
thank you for your support, and for your query example. And oh yes, I forgot to mention the postgres version, which is 9.6; but if I find a solution which works in Version 10 then I could probably update.
I guess with your query I can figure out which connection holds a lock, but it seems I cannot correlate those locks to the rows which actually are locked, since pg_locks seems not to reference this in any way.
To be more explicit: I can find out about all locks in the current database that are held by other connections using
select l.* from pg_locks l
left join pg_database d on l.database=d.oid
where pid <> pg_backend_pid()
and d.datname = current_database()
and relation::regclass='<tablename>'::regclass;
which, with one locked row, results in something like this:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------+---------+----------
relation | 629976 | 638971 | | | | | | | | 3/983554 | 60515 | RowShareLock | t | t
(1 row)
And here is where I am stuck.
database, relation and pid are accounted for - the only value I can't make sense of is the virtualtransaction entry.
I was hoping that objid or objsubid would contain the OID of the locked row, but obviously I miss a crucial piece of understanding :)
(Note that I tried this both WITH OID and without oid in my table.)
Best,
/eno
--
me at github: https://github.com/radiospiel
me at linked.in: https://www.linkedin.com/in/radiospiel
On 15 Mar 2018, at 22:12, Melvin Davidson wrote:
On Thu, Mar 15, 2018 at 4:48 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:Greetings,
* Enrico Thierbach (eno@xxxxxxxxxxxx) wrote:
> I am using `SELECT * FROM queue ... FOR UPDATE SKIP LOCKED` to implement a
> queueing system.
>
> Now I wonder if it is possible, given the id of one of the locked rows in
> the queue table, to find out which connection/which transaction owns the
> lock.
Sure, you can attempt to lock the record and then run pg_blocking_pids()
(in another session) against the pid which is trying to acquire the
lock.
Session #1:
Connect
SELECT * FROM queue ... FOR UPDATE SKIP LOCKED;
... gets back some id X
... waits
Session #2:
Connect
SELECT pg_backend_pid(); -- save this for the 3rd session
SELECT * FROM queue WHERE id = X FOR UPDATE;
... get blocked waiting for #1
... waits
Session #3:
SELECT pg_blocking_pids(SESSION_2_PID);
-- returns PID of Session #1
Obviously there's race conditions and whatnot (what happens if session
#1 releases the lock?), but that should work to figure out who is
blocking who.
If you're on a version of PG without pg_blocking_pids then you can look
in the pg_locks view, though that's a bit more annoying to decipher.
Thanks!
Stephen
> Now I wonder if it is possible, given the id of one of the locked rows in
> the queue table, to find out which connection/which transaction ownsYou have not specified which version of PostgreSQL, but try this query.
SELECT c.datname,
c.pid as pid,
c.client_addr,
c.usename as user,
c.query,
c.wait_event,
c.wait_event_type,
/* CASE WHEN c.waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
*/
l.pid as blocked_by,
c.query_start,
current_timestamp - c.query_start as duration
FROM pg_stat_activity c
LEFT JOIN pg_locks l1 ON (c.pid = l1.pid and not l1.granted)
LEFT JOIN pg_locks l2 on (l1.relation = l2.relation and l2.granted)
LEFT JOIN pg_stat_activity l ON (l2.pid = l.pid)
LEFT JOIN pg_stat_user_tables t ON (l1.relation = t.relid)
WHERE pg_backend_pid() <> c.pid
ORDER BY datname,
query_start;
--Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
