Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ben Chobot <bench(at)silentmedia(dot)com>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks
Date: 2012-04-25 19:31:46
Message-ID: 25157.1335382306@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Ben Chobot <bench(at)silentmedia(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2012-04-25 19:33:49 Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks
Previous Message Ben Chobot 2012-04-25 19:12:37 Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks