Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

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(at)svana(dot)org> 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