Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks - Mailing list pgsql-general
From | Ben Chobot |
---|---|
Subject | Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks |
Date | |
Msg-id | 9C8B85AA-B22B-43DA-A857-FF3435D5CD15@silentmedia.com Whole thread Raw |
In response to | Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Updates, ShareLocks, RowExclusiveLocks, and deadlocks (Martijn van Oosterhout <kleptog@svana.org>) |
List | pgsql-general |
On Apr 25, 2012, at 12:35 PM, Tom Lane wrote: > Ben Chobot <bench@silentmedia.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 singletable in a single transaction, ~1k rows at a time. I've been told the transaction does nothing other than insert andupdate 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 expectfrom the manual. Unfortunately, sometimes, some of those update transactions have ShareLocks, which doesn't make anysense to us, because we're not taking explicit locks and the manual claims ShareLocks only automatically come from indexcreation, 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. So, if I understand what you're saying, if I have two connections each transactionally updating many rows, then each transactionwill need to acquire a RowExclusiveLock for each row (as documented), and also (as not documented?) each acquisitionwill temporarily acquire a ShareLock on the other transaction's transactionid? That seems to fit what I'm seeingin pg_locks, and I suppose if there is an overlap in rows between to two transactions, and if those updates happenedin the wrong order, then we'd get deadlock. I just assumed we'd see that in the logs as deadlocks due to waitingfor RowExclusiveLocks, while it sounds like you are saying the log will show them as ShareLocks? If that's the case, would doing the updates in, say, primary key order solve this problem? I'm pretty sure we're just pullingthings out of the queue and running them in random order. If that's not the case, then what information would be helpful in understanding what's going on? All of pg_locks or justthe locks related to the virtualtransactionid of the update with the SharedLock? There are no foreign keys related tothis table. Either way, thanks!
pgsql-general by date: