Updates, ShareLocks, RowExclusiveLocks, and deadlocks

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 single table in a single transaction, ~1k rows at a time. I've been told the transaction does nothing other than insert and update 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 from the manual. Unfortunately, sometimes, some of those update transactions have ShareLocks, which doesn't make any sense to 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.

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 single table in a single transaction, ~1k rows at a time. I've been told the transaction does nothing other than insert and update 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 from the manual. Unfortunately, sometimes, some of those update transactions have ShareLocks, which doesn't make any sense to 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.

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 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?

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 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.

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 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 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 pulling things 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

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