Vivek Khera <vivek@xxxxxxxxx> writes: > On Jul 11, 2008, at 4:24 AM, Richard Huxton wrote: >> If you just want to see if a lock has been taken (e.g. SELECT FOR >> UPDATE) then that shows in pg_locks. If you want details on the >> actual rows involved, then you probably want "pgrowlocks" mentioned >> in Appendix F. Additional Supplied Modules. > pg_locks tells you the page/tuple so you can select it with those > values. Assuming they are page=132 and tuple=44 and relation=99 you > can find the tuple thusly: Although pg_locks does have columns that might make you think it shows row locks, relying on it to do so is erroneous. A row lock shows in pg_locks only transiently while it is being taken (or waited for). Once the tuple is marked as locked on-disk, the pg_locks entry is removed --- otherwise we'd soon run out of the limited space in the in-memory locks table. So you really need contrib/pgrowlocks if you are interested in looking at held rowlocks. > What I need to see is which locks my other queries are waiting on. If > pg_locks would show me which process is also blocking on this lock, > I'd be a much happier man today (actually, last tuesday, when this was > a problem for me to debug something). Hmm? pg_locks will tell you about it, though you need to know how to interpret it. For example: (session 1) regression=# create table t(f1 int); CREATE TABLE regression=# insert into t values(0); INSERT 0 1 regression=# begin; BEGIN regression=# update t set f1=f1+1; UPDATE 1 regression=# (now start session 2, and do) regression=# update t set f1=f1+1; (blocks) (now in session 3) regression=# select * from pg_locks order by pid, granted desc; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+------------------+--------- virtualxid | | | | | 1/4054 | | | | | 1/4054 | 4371 | ExclusiveLock | t transactionid | | | | | | 4658 | | | | 1/4054 | 4371 | ExclusiveLock | t relation | 16384 | 40014 | | | | | | | | 1/4054 | 4371 | RowExclusiveLock | t virtualxid | | | | | 2/168 | | | | | 2/168 | 4373 | ExclusiveLock | t transactionid | | | | | | 4659 | | | | 2/168 | 4373 | ExclusiveLock | t relation | 16384 | 40014 | | | | | | | | 2/168 | 4373 | RowExclusiveLock | t tuple | 16384 | 40014 | 0 | 1 | | | | | | 2/168 | 4373 | ExclusiveLock | t transactionid | | | | | | 4658 | | | | 2/168 | 4373 | ShareLock | f virtualxid | | | | | 3/8 | | | | | 3/8 | 4383 | ExclusiveLock | t relation | 16384 | 10960 | | | | | | | | 3/8 | 4383 | AccessShareLock | t (10 rows) The first three of these are locks held by session 1 on its own xids plus RowExclusiveLock (writer's lock) on table t. The next three are the corresponding locks held by session 2. Next there is a tuple lock, which is *held* not awaited by session 2 --- but that's just the transient lock that it holds to prevent race conditions while it's marking the tuple as locked. Since session 1 has already marked the tuple as locked, session 2 has to wait for session 1's transaction to end, and that's what the awaited ShareLock in the next row is for. (The last two rows are from session 3, which is reading pg_locks.) So in short, if you see someone blocked on ShareLock for someone else's transaction ID, look for a tuple lock held by the first someone. That will tell you what the contention is really about. regards, tom lane
