Thread: Updates, ShareLocks, RowExclusiveLocks, and deadlocks
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?
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
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!
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
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
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!
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