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
pgsql-general by date
|Next:||From: Martijn van Oosterhout||Date: 2012-04-25 19:33:49|
|Subject: Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks|
|Previous:||From: Ben Chobot||Date: 2012-04-25 19:12:37|
|Subject: Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks |