snippetsqlMinor
How to check for row lock level when FOR UPDATE is used in PostgreSQL?
Viewed 0 times
postgresqlupdatelevelusedforwhenhowcheckrowlock
Problem
Related to previous question here
From the pg_locks documentation, row level lock information is not available when
Is there any way to check for row level lock when
If yes, how can I do so with Postgres 11?
Thank you
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
Solution
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
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);Code Snippets
SELECT id FROM mytable
WHERE id NOT IN (SELECT id FROM mytable
FOR UPDATE SKIP LOCKED);Context
StackExchange Database Administrators Q#282736, answer score: 5
Revisions (0)
No revisions yet.