I've recently moved a system from Oracle to Postgre, and I'm having
locking problems.
Here's a simple scenario, with a one-row table:
Process 1 does the following:
netbill=> begin;
BEGIN
netbill=> update t1 set f1=2;
UPDATE 1
netbill=>
and at this point, the locks are:
netbill=> select * from pg_locks order by pid;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+------------------+---------
| | 422665 | 28283 | ExclusiveLock | t
17715590 | 17203 | | 28283 | AccessShareLock | t
17715590 | 17203 | | 28283 | RowExclusiveLock | t
Now Process 2 does the same thing:
netbill=> begin;
BEGIN
netbill=> update t1 set f1=3;
(blocks)
And now, the locks:
netbill=> select * from pg_locks order by pid;
relation | database | transaction | pid | mode | granted
----------+----------+-------------+-------+------------------+---------
| | 422665 | 28283 | ExclusiveLock | t
17715590 | 17203 | | 28283 | AccessShareLock | t
17715590 | 17203 | | 28283 | RowExclusiveLock | t
| | 422665 | 28284 | ShareLock | f
17715590 | 17203 | | 28284 | AccessShareLock | t
17715590 | 17203 | | 28284 | RowExclusiveLock | t
| | 422670 | 28284 | ExclusiveLock | t
Some questions:
1) What exactly is the transaction id? I see that not only does Process 2
have two different xid's (in addition to the nulls), but that one of
them is shared with process 1. This seems odd to the casual observer.
2) What resource is the blocking lock attempting to lock? It has no
relation or database value.
3) According to http://www.postgresql.org/idocs/index.php?locking-tables.html,
all of those locking modes are table-level locks. Am I silly in expecting
that the blocked Process 2 should be waiting on a row-level lock?
4) What is it that both processes have an ExclusiveLock on?
5) Have I missed some documentation somewhere?
Thanks for any help.
clarence@netlojix.com
