Thread: finding the other statement causing a sharelock

finding the other statement causing a sharelock

From
Ivan Sergio Borgonovo
Date:
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


Re: finding the other statement causing a sharelock

From
Tom Lane
Date:
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

Re: finding the other statement causing a sharelock

From
Ivan Sergio Borgonovo
Date:
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


Re: finding the other statement causing a sharelock

From
Scott Marlowe
Date:
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.

Re: finding the other statement causing a sharelock

From
Ivan Sergio Borgonovo
Date:
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


Re: finding the other statement causing a sharelock

From
Dimitri Fontaine
Date:
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