On Wed, Apr 25, 2012 at 01:12:37PM -0600, Ben Chobot wrote: > So, if I understand what you're saying, if I have two connections > each transactionally updating many rows, then each transaction will > need to acquire a RowExclusiveLock for each row (as documented), and > also (as not documented?) each acquisition will temporarily acquire a > ShareLock on the other transaction's transactionid? That seems to > fit what I'm seeing in pg_locks, and I suppose if there is an overlap > in rows between to two transactions, and if those updates happened in > the wrong order, then we'd get deadlock. I just assumed we'd see > that in the logs as deadlocks due to waiting for RowExclusiveLocks, > while it sounds like you are saying the log will show them as > ShareLocks? I think what you're missing here is that RowExclusiveLocks are taken by marking the row itself. If two transactions want to lock the same row, transaction A marks the row, then transactions B sees the marking and so must wait until transaction A completes. To do this transaction B tries to take a lock on the transaction A. Since each transaction has an exclusive lock on itself, the effect is that transaction B waits for transaction A to complete. Apparently this is done using a ShareLock, but I think any locktype would do. But taking a lock on another transaction is a pretty common way to wait on another transaction. And these locks only appear when needed. > If that's the case, would doing the updates in, say, primary key > order solve this problem? I'm pretty sure we're just pulling things > out of the queue and running them in random order. If you're taking locks it's always better to be consistant about the order, so it may help, yes. > If that's not the case, then what information would be helpful in > understanding what's going on? All of pg_locks or just the locks > related to the virtualtransactionid of the update with the > SharedLock? There are no foreign keys related to this table. Updating a row locks it against other updates, because the second update needs to know which version of the row it's updating. Hope this helps, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
Attachment:
signature.asc
Description: Digital signature
