Updates, ShareLocks, RowExclusiveLocks, and deadlocks

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Updates, ShareLocks, RowExclusiveLocks, and deadlocks
Date: 2012-04-25 17:13:59
Message-ID: 2B4CB0E8-7281-43E7-93F9-5BCD3B0FAC10@silentmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

I've enabled logging all queries and can verify there are no explicit locks an ORM might be doing under our noses. So at this point, I'm confused. If we're not explicitly locking, running triggers that might be locking, or creating indices, where are these ShareLocks coming from? Any suggestions on how to track it down?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2012-04-25 18:35:18 Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks
Previous Message Steve Crawford 2012-04-25 15:49:07 Re: postgresql log parsing to report on user/db access