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 18:35:18
Message-ID: 19670.1335378918@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Ben Chobot <bench(at)silentmedia(dot)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

In response to

Responses

pgsql-general by date

Next:From: George WeaverDate: 2012-04-25 18:39:44
Subject: Re: Using copy with a file containing blank rows
Previous:From: Ben ChobotDate: 2012-04-25 17:13:59
Subject: Updates, ShareLocks, RowExclusiveLocks, and deadlocks

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