Thread: finding the other statement causing a sharelock
I get DETAIL: Process 24749 waits for ShareLock on transaction 113443492; blocked by process 25199. Process 25199 waits for ShareLock on transaction 113442820; blocked by process 24749. I would like to know both statements that caused the sharelock problem. This is a long running transaction. I know one of the statement. I'd like to know the other. How? -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> I get
> DETAIL: Process 24749 waits for ShareLock on transaction 113443492;
> blocked by process 25199. Process 25199 waits for ShareLock on
> transaction 113442820; blocked by process 24749.
> I would like to know both statements that caused the sharelock
> problem.
Recent versions of PG record both (or all) statements involved in a
deadlock in the postmaster log.
regards, tom lane
On Mon, 08 Nov 2010 15:45:12 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > > I get > > DETAIL: Process 24749 waits for ShareLock on transaction > > 113443492; blocked by process 25199. Process 25199 waits for > > ShareLock on transaction 113442820; blocked by process 24749. > > > I would like to know both statements that caused the sharelock > > problem. > Recent versions of PG record both (or all) statements involved in a > deadlock in the postmaster log. What about not so recent 8.3.9? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Mon, Nov 8, 2010 at 2:18 PM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > On Mon, 08 Nov 2010 15:45:12 -0500 > Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Ivan Sergio Borgonovo <mail@webthatworks.it> writes: >> > I get >> > DETAIL: Process 24749 waits for ShareLock on transaction >> > 113443492; blocked by process 25199. Process 25199 waits for >> > ShareLock on transaction 113442820; blocked by process 24749. >> >> > I would like to know both statements that caused the sharelock >> > problem. > >> Recent versions of PG record both (or all) statements involved in a >> deadlock in the postmaster log. > Don't know how much it helps here, but this page: http://wiki.postgresql.org/wiki/Lock_Monitoring is priceless when you're having issues midday with a lock that won't go away.
On Mon, 8 Nov 2010 14:22:16 -0700 Scott Marlowe <scott.marlowe@gmail.com> wrote: > Don't know how much it helps here, but this page: > http://wiki.postgresql.org/wiki/Lock_Monitoring > is priceless when you're having issues midday with a lock that > won't go away. I was thinking to reinvent the wheel and write something similar. But I was already thinking how am I supposed to "intercept" a lock that is caused by a long transaction that I know and a process happening at some unknown time? I've some strong suspect... and I'd like to exit earlier from a function if a process is running but I'm not really sure how to add a semaphore... -- Ivan Sergio Borgonovo http://www.webthatworks.it
Ivan Sergio Borgonovo <mail@webthatworks.it> writes: > I've some strong suspect... and I'd like to exit earlier from a > function if a process is running but I'm not really sure how to add a > semaphore... Maybe pg_try_advisory_lock() would help you there? http://www.postgresql.org/docs/8.3/static/explicit-locking.html#ADVISORY-LOCKS http://www.postgresql.org/docs/8.3/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support