Search Postgresql Archives

Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux