How to check for row lock level when FOR UPDATE is used in PostgreSQL?

Question

Related to previous question here

From the pg_locks documentation, row level lock information is not available when FOR UPDATE is used in a SELECT statement.

Is there any way to check for row level lock when FOR UPDATE is used in a SELECT statement? If yes, how can I do so with Postgres 11?

Thank you

score 6 · Accepted Answer · answered Jan 07 '21 at 13:12

Row locks are not permanently stored in the shared lock table, but on the row itself, so you cannot simply query for them.

To figure out which rows in a table are locked by concurrent transactions, you could run

SELECT id FROM mytable
WHERE id NOT IN (SELECT id FROM mytable
                 FOR UPDATE SKIP LOCKED);

1 Answers1