I have a few questions on pgsql locking terms and such...
I created the following view to make viewing the locks
a little easier:
-- CREATE OR REPLACE VIEW locksview AS
-- SELECT l.*, r.*, a.*, now() - a.query_start as query_age,
-- substring(replace(current_query, '\n', ' '), 1, 30) as sql30,
-- case when granted then 'granted' else 'WAITING' end as status,
-- case l.mode when 'AccessShareLock' then 1
-- when 'RowShareLock' then 2
-- when 'RowExclusiveLock' then 3
-- when 'ShareUpdateExclusiveLock' then 4
-- when 'ShareLock' then 5
-- when 'ShareRowExclusiveLock' then 6
-- when 'ExclusiveLock' then 7
-- when 'AccessExclusiveLock' then 8
-- else 100 end as exclusivity
-- FROM pg_locks l LEFT OUTER JOIN pg_class r ON r.oid = l.relation
-- LEFT OUTER JOIN pg_stat_activity a ON l.pid = a.procpid;
I then started two transactions that try to update the
same row, and then ran the following query on the view
above:
SELECT now(), client_addr, pid, query_age, status, locktype, mode,
relname, page, tuple as tup, current_query
FROM locksview
WHERE now() - query_start > '3 minute'::INTERVAL
ORDER BY query_age DESC, exclusivity DESC;
now | client_addr | pid | query_age | status | locktype | mode | relname | page | tup | current_query
-------------------------------+-------------+------+-----------------+---------+---------------+------------------+---------+------+-----+--------------------------------------------
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6484 | 00:01:25.830264 | granted | transactionid | ExclusiveLock | | | | <IDLE> in transaction
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6484 | 00:01:25.830264 | granted | relation | RowExclusiveLock | foo | | | <IDLE> in transaction
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6484 | 00:01:25.830264 | granted | relation | AccessShareLock | foo | | | <IDLE> in transaction
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6508 | 00:01:18.862623 | granted | tuple | ExclusiveLock | foo | 0 | 7 | update foo set msg = 'maybe' where id = 3;
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6508 | 00:01:18.862623 | granted | transactionid | ExclusiveLock | | | | update foo set msg = 'maybe' where id = 3;
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6508 | 00:01:18.862623 | WAITING | transactionid | ShareLock | | | | update foo set msg = 'maybe' where id = 3;
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6508 | 00:01:18.862623 | granted | relation | RowExclusiveLock | foo | | | update foo set msg = 'maybe' where id = 3;
2006-11-09 20:37:25.680662-08 | 10.0.1.82 | 6508 | 00:01:18.862623 | granted | relation | AccessShareLock | foo | | | update foo set msg = 'maybe' where id = 3;
(8 rows)
In this case, pid 6484 updated the row first but did not
commit, then 6508 tried to update the same row and naturally
blocked. I understand at a very basic level why this
would block, so that's not the spirit of my questions. I
also understand each transaction holds its acquired locks
until the end of its transaction. But I'd like to better
understand the pg_locks view and pgsql locking terms.
First, I don't see rhyme or reason in the transactionid locks.
Can someone explain why 6508 has a transactionid ExclusiveLock,
but now is waiting on a transactionid ShareLock? That seems
unintuitive. It would seem that if you hold a more exclusive
lock, getting a less exclusive lock would not be a problem. Is
there rhyme or reason for this? From the docs, I'd guess the
ExclusiveLock is given to block "select for updates" that might
want to grab that row after 6508 already got in line, and that
it is grabbing the ShareLock for... what?
Second, 6508, the *blocked* process, also holds a tuple
ExclusiveLock on the page and tuple of foo on which it is
actually blocked. Again, is there rhyme or reason I'm missing?
Third, what is it that causes values to show up in the page and
tuple columns?
Finally, both processes, the blocked and the blocking, hold
relation RowExclusiveLocks, which again, hardly seems intuitive.
It would seem that the blocked process would not have that lock
until it actually had access to update the row in question.
The docs do not say RowExclusiveLock conflicts with
RowExclusiveLock, but I guess I'm expecting to to that conflict
since one process is blocking on the other for updating the same
row.
TIA.
Ed
