Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group