Обсуждение: Concurrency question
Any help here appreciated. I ran a vacuum verbose analyze on a database over the weekend. It ran fine until it tried to vacuum a table less than 2000 pages. It successfully acquired a ShareUpdateExclusiveLock as I would expect. There was an idle thread that had an AccessSharelock on the same table. Compatible locks I would think. But the vacuum hung until the AccessSharelock thread was cancelled - 11 hours in all. This table normally vacuums in less than 15 seconds. This AccessSharelock came from a query that formerly was part of a transaction sent from a remote server. Could it be that it hung because it was A transaction? Even so I thought those lock types were compatible. As always thanks for your time. Mark Steben│Database Administrator│ @utoRevenueR "Join the Revenue-tion" 95 Ashley Ave. West Springfield, MA., 01089 413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax) @utoRevenue is a registered trademark and a division of Dominion Enterprises
2009/7/7 Mark Steben <msteben@autorevenue.com>: > Any help here appreciated. > > I ran a vacuum verbose analyze on a database over the weekend. It ran fine > until it tried to vacuum a table less than 2000 pages. It successfully > acquired a ShareUpdateExclusiveLock as I would expect. > There was an idle thread that had an AccessSharelock on the same table. > Compatible locks I would think. But the vacuum hung until the > AccessSharelock thread was cancelled - 11 hours in all. > This table normally vacuums in less than 15 seconds. This AccessSharelock > came from a query that formerly was part of a transaction sent from a remote > server. Not sure what you mean by formerly was part of a transaction. If the transaction has rolled back, then the vacuum can proceed. If the transaction is till open, then it's not formerly a part of it, it IS a part of it. Either way, open transactions block vacuum on updated tables. > Could it be that it hung because it was > A transaction? Even so I thought those lock types were compatible. Nope. If you've got an idle transaction that's updated tuples, the vacuum waits on it. Long running / idle transactions are generally a bad thing.
Scott Marlowe <scott.marlowe@gmail.com> writes:
> 2009/7/7 Mark Steben <msteben@autorevenue.com>:
>> I ran a vacuum verbose analyze on a database over the weekend. �It ran fine
>> until it tried to vacuum a table less than 2000 pages. �It successfully
>> acquired a ShareUpdateExclusiveLock as I would expect.
>> There was an idle thread that had an AccessSharelock on the same table.
>> Compatible locks I would think. But the vacuum hung until the
>> AccessSharelock thread was cancelled - 11 hours in all.
>> This table normally vacuums in less than 15 seconds. � This AccessSharelock
>> came from a query that formerly was part of a transaction sent from a remote
>> server.
> Not sure what you mean by formerly was part of a transaction. If the
> transaction has rolled back, then the vacuum can proceed. If the
> transaction is till open, then it's not formerly a part of it, it IS a
> part of it. Either way, open transactions block vacuum on updated
> tables.
Uh, no, they don't.
The described situation is impossible: AccessSharelock doesn't block
ShareUpdateExclusiveLock. There must have been some other lock or
attempted lock involved (perhaps at a page or tuple level rather than
the whole-relation level). But we can't tell much from this much detail.
regards, tom lane
On Tue, Jul 7, 2009 at 3:40 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Scott Marlowe <scott.marlowe@gmail.com> writes: >> 2009/7/7 Mark Steben <msteben@autorevenue.com>: >>> I ran a vacuum verbose analyze on a database over the weekend. It ran fine >>> until it tried to vacuum a table less than 2000 pages. It successfully >>> acquired a ShareUpdateExclusiveLock as I would expect. >>> There was an idle thread that had an AccessSharelock on the same table. >>> Compatible locks I would think. But the vacuum hung until the >>> AccessSharelock thread was cancelled - 11 hours in all. >>> This table normally vacuums in less than 15 seconds. This AccessSharelock >>> came from a query that formerly was part of a transaction sent from a remote >>> server. > >> Not sure what you mean by formerly was part of a transaction. If the >> transaction has rolled back, then the vacuum can proceed. If the >> transaction is till open, then it's not formerly a part of it, it IS a >> part of it. Either way, open transactions block vacuum on updated >> tables. > > Uh, no, they don't. > > The described situation is impossible: AccessSharelock doesn't block > ShareUpdateExclusiveLock. There must have been some other lock or > attempted lock involved (perhaps at a page or tuple level rather than > the whole-relation level). But we can't tell much from this much detail. So something like alter table or something? I do know that vacuum full is blocked by updates and such.
Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Tue, Jul 7, 2009 at 3:40 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> The described situation is impossible: AccessSharelock doesn't block
>> ShareUpdateExclusiveLock. �There must have been some other lock or
>> attempted lock involved (perhaps at a page or tuple level rather than
>> the whole-relation level). �But we can't tell much from this much detail.
> So something like alter table or something?
Well, one possible way to block vacuum is that the idle transaction is
holding AccessSharelock, and some third session is trying to acquire
an exclusive lock (eg for ALTER TABLE), and then vacuum comes along and
wants ShareUpdateExclusiveLock. That would cause vacuum to queue up
behind the waiting exclusive lock request. However, Mark asserts that
the vacuum did successfully acquire ShareUpdateExclusiveLock, which if
accurate would eliminate this scenario. But there might have been
something similar going on at a page or tuple level. We can't tell with
no knowledge of what else was in the system, but I'm betting there was
at least one other lock request involved.
regards, tom lane
On Tue, Jul 7, 2009 at 11:11 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Scott Marlowe <scott.marlowe@gmail.com> writes: >> On Tue, Jul 7, 2009 at 3:40 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >>> The described situation is impossible: AccessSharelock doesn't block >>> ShareUpdateExclusiveLock. There must have been some other lock or >>> attempted lock involved (perhaps at a page or tuple level rather than >>> the whole-relation level). But we can't tell much from this much detail. > >> So something like alter table or something? > > Well, one possible way to block vacuum is that the idle transaction is Another way to block vacuum is to be in the middle of scanning the table and have a pin on a page that vacuum wants to clean. For example, say that transaction has a cursor open for a sequential scan of that table and has stopped reading from the cursor, just sitting "idle" but with the cursor still stuck on that page. In that case vacuum would wait on that page waiting for the query to make progress and move onto a new page so it can clean it. -- greg http://mit.edu/~gsstark/resume.pdf
Well I think Greg hit on the problem. The remote server that issues The sql had an outdated ODBC driver that was not issuing code to properly Close cursors. So cursors remained open and apparently locks were never Released. A newer driver version is currently being tested, cursors are Being closed and when we push this out, everything will once again Be right with the world. Thanks to all for the help. Mark Steben│Database Administrator│ @utoRevenue-R- "Join the Revenue-tion" 95 Ashley Ave. West Springfield, MA., 01089 413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax) @utoRevenue is a registered trademark and a division of Dominion Enterprises -----Original Message----- From: gsstark@gmail.com [mailto:gsstark@gmail.com] On Behalf Of Greg Stark Sent: Tuesday, July 07, 2009 8:13 PM To: Tom Lane Cc: Scott Marlowe; Mark Steben; pgsql-admin@postgresql.org Subject: Re: Concurrency question On Tue, Jul 7, 2009 at 11:11 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > Scott Marlowe <scott.marlowe@gmail.com> writes: >> On Tue, Jul 7, 2009 at 3:40 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >>> The described situation is impossible: AccessSharelock doesn't block >>> ShareUpdateExclusiveLock. There must have been some other lock or >>> attempted lock involved (perhaps at a page or tuple level rather than >>> the whole-relation level). But we can't tell much from this much detail. > >> So something like alter table or something? > > Well, one possible way to block vacuum is that the idle transaction is Another way to block vacuum is to be in the middle of scanning the table and have a pin on a page that vacuum wants to clean. For example, say that transaction has a cursor open for a sequential scan of that table and has stopped reading from the cursor, just sitting "idle" but with the cursor still stuck on that page. In that case vacuum would wait on that page waiting for the query to make progress and move onto a new page so it can clean it. -- greg http://mit.edu/~gsstark/resume.pdf