Thread: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From
Ben Chobot
Date:
We have a few daemon process that constantly pull batches of logs from a work queue and then insert into or update a
singletable in a single transaction, ~1k rows at a time. I've been told the transaction does nothing other than insert
andupdate on that table, and I can verify the table in question has no triggers. This is 9.1.3. 

What we've noticed is that most updates come with a RowExclusiveLock, according to pg_locks, which is what you'd expect
fromthe manual. Unfortunately, sometimes, some of those update transactions have ShareLocks, which doesn't make any
senseto us, because we're not taking explicit locks and the manual claims ShareLocks only automatically come from index
creation,which we're also not doing explicitly. This is a problem because it's showing up as deadlocks, every few
minutes.

I've enabled logging all queries and can verify there are no explicit locks an ORM might be doing under our noses. So
atthis point, I'm confused. If we're not explicitly locking, running triggers that might be locking, or creating
indices,where are these ShareLocks coming from? Any suggestions on how to track it down? 

Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From
Tom Lane
Date:
Ben Chobot <bench@silentmedia.com> writes:
> We have a few daemon process that constantly pull batches of logs from a work queue and then insert into or update a
singletable in a single transaction, ~1k rows at a time. I've been told the transaction does nothing other than insert
andupdate on that table, and I can verify the table in question has no triggers. This is 9.1.3. 
> What we've noticed is that most updates come with a RowExclusiveLock, according to pg_locks, which is what you'd
expectfrom the manual. Unfortunately, sometimes, some of those update transactions have ShareLocks, which doesn't make
anysense to us, because we're not taking explicit locks and the manual claims ShareLocks only automatically come from
indexcreation, which we're also not doing explicitly. This is a problem because it's showing up as deadlocks, every few
minutes.

You'd really need to provide more information here, but what I suspect
is that you're seeing row-level locks in process of being acquired.
For implementation reasons that type of activity will transiently
try to acquire ShareLock on another transaction's XID.  Or this might
be something different, but without seeing a full copy of the pg_locks
rows you're talking about, it's hard to be sure.  In any case, I'd
speculate that the underlying cause is conflicting updates on the same
row, and/or foreign-key-related row locks.

            regards, tom lane

Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From
Ben Chobot
Date:
On Apr 25, 2012, at 12:35 PM, Tom Lane wrote:

> Ben Chobot <bench@silentmedia.com> writes:
>> We have a few daemon process that constantly pull batches of logs from a work queue and then insert into or update a
singletable in a single transaction, ~1k rows at a time. I've been told the transaction does nothing other than insert
andupdate on that table, and I can verify the table in question has no triggers. This is 9.1.3. 
>> What we've noticed is that most updates come with a RowExclusiveLock, according to pg_locks, which is what you'd
expectfrom the manual. Unfortunately, sometimes, some of those update transactions have ShareLocks, which doesn't make
anysense to us, because we're not taking explicit locks and the manual claims ShareLocks only automatically come from
indexcreation, which we're also not doing explicitly. This is a problem because it's showing up as deadlocks, every few
minutes.
>
> You'd really need to provide more information here, but what I suspect
> is that you're seeing row-level locks in process of being acquired.
> For implementation reasons that type of activity will transiently
> try to acquire ShareLock on another transaction's XID.  Or this might
> be something different, but without seeing a full copy of the pg_locks
> rows you're talking about, it's hard to be sure.  In any case, I'd
> speculate that the underlying cause is conflicting updates on the same
> row, and/or foreign-key-related row locks.


So, if I understand what you're saying, if I have two connections each transactionally updating many rows, then each
transactionwill need to acquire a RowExclusiveLock for each row (as documented), and also (as not documented?) each
acquisitionwill temporarily acquire a ShareLock on the other transaction's transactionid? That seems to fit what I'm
seeingin pg_locks, and I suppose if there is an overlap in rows between to two transactions, and if those updates
happenedin the wrong order, then we'd get deadlock. I just assumed we'd see that in the logs as deadlocks due to
waitingfor RowExclusiveLocks, while it sounds like you are saying the log will show them as ShareLocks?  

If that's the case, would doing the updates in, say, primary key order solve this problem? I'm pretty sure we're just
pullingthings out of the queue and running them in random order. 

If that's not the case, then what information would be helpful in understanding what's going on? All of pg_locks or
justthe locks related to the virtualtransactionid of the update with the SharedLock? There are no foreign keys related
tothis table. 

Either way, thanks!

Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From
Tom Lane
Date:
Ben Chobot <bench@silentmedia.com> writes:
> So, if I understand what you're saying, if I have two connections each transactionally updating many rows, then each
transactionwill need to acquire a RowExclusiveLock for each row (as documented), and also (as not documented?) each
acquisitionwill temporarily acquire a ShareLock on the other transaction's transactionid? That seems to fit what I'm
seeingin pg_locks, and I suppose if there is an overlap in rows between to two transactions, and if those updates
happenedin the wrong order, then we'd get deadlock. I just assumed we'd see that in the logs as deadlocks due to
waitingfor RowExclusiveLocks, while it sounds like you are saying the log will show them as ShareLocks?  

I don't have all the details in my head, but if you deliberately provoke
a deadlock by making two transactions update the same two rows in
opposite orders, you'll soon find out what it looks like in the log.

> If that's the case, would doing the updates in, say, primary key order solve this problem? I'm pretty sure we're just
pullingthings out of the queue and running them in random order. 

Any consistent ordering ought to dodge that type of problem.

            regards, tom lane

Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From
Martijn van Oosterhout
Date:
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@svana.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

Attachment

Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From
Ben Chobot
Date:

On Apr 25, 2012, at 1:31 PM, Tom Lane wrote:

I don't have all the details in my head, but if you deliberately provoke
a deadlock by making two transactions update the same two rows in
opposite orders, you'll soon find out what it looks like in the log.

Heh, duh. Looks like your first guess was correct, as usual. Conflicting updates in two transactions show up in the logs as a ShareLock deadlock.

Thanks!

Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> I think what you're missing here is that RowExclusiveLocks are taken by
> marking the row itself.

More specifically: row-level locks are not reflected in pg_locks at all.
A RowExclusiveLock entry in pg_locks reflects a *table* level lock,
which is taken by any INSERT/UPDATE/DELETE command on the table,
independently of how many rows it modifies (even zero).  The purpose
of that type of lock is to prevent concurrent DDL changes on the table,
not to serialize the row-level operations.

There are locks that will show up in pg_locks that are taken transiently
when attempting to mark a row modified, but they don't persist after the
mark has been made, and the details have changed from time to time in
different PG versions.  So I'd suggest testing it to see exactly what
you get.

            regards, tom lane