Joel Jacobson <joel@gluefinance.com> writes:
> a) both processes have been granted a RowExclusiveLock on table B. How can
> both be granted a RowExclusiveLock on the same table? Since the table only
> contains one row, it must be a lock on the same row, which should be
> impossible, right?
This complaint seems to be based on a complete misunderstanding of what
RowExclusiveLock is. Please see
http://www.postgresql.org/docs/8.4/static/explicit-locking.html
RowExclusiveLock on a table is just a type of lock on a *table*.
It is not taken on any particular row, and it does not prevent other
processes from also taking RowExclusiveLock on the same table. (As
the docs note, the names of the lock modes aren't terribly mnemonic.)
There will also be row-level locks (either shared or exclusive) on
specific rows, but those generally aren't visible in pg_locks because
of implementation restrictions.
> b) process 1 (which is currently waiting) has been granted a lock of type
> "tuple", page 0, tuple 1, mode "ExclusiveLock" on table B. I don't know what
> a "tuple" lock is, but what surprises me is process 1 being granted the
> lock, and not process 2 (since process 2 updated B before 1).
Well, what that really means is that process 1 is waiting to acquire
exclusive row-level lock on that row. Process 2 has got that lock,
but you can't see that in pg_locks. What you can see is a transient
heavyweight lock that is taken out while waiting. IIRC the main
reason for doing that is to ensure that the heavyweight lock manager
can resolve any conflicts that might come from multiple processes
trying to acquire the same row-level lock.
> 5. Process 2 tries to execute "UPDATE B SET Col2 = 1 WHERE BID = 2;" which
> is exactly the same query as in step 2 above.
> Since process 2 already hold a granted RowExclusiveLock on the row in table
> B it tries to update, I think this query should be executed instantly
> without any problem. Instead, it causes a deadlock in process 2, allowing
> process 1 to commit. Very strange.
It does go through without any deadlock, *if* there is no foreign key
involved. You didn't tell us exactly what the FK relationship is, but
I suspect the reason for the deadlock is that one process is trying to
update a row that references some row already updated by the other.
That will require a row-level share lock on the referenced row, so you
can get a deadlock.
regards, tom lane