Joel Jacobson <j...@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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers