Re: share lock when only one user connected?

On Thu, 28 Oct 2010 13:57:18 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> writes:
> > I'm running this query when I'm the only user and this should be
> > the only thing running.
>
> > And I get
>
> > DETAIL: Process 7188 waits for ShareLock on transaction
> > 110562621; blocked by process 7244. Process 7244 waits for
> > ShareLock on transaction 110562544; blocked by process 7188.
>
> It's pretty hard to believe that you haven't got two processes ...
> maybe there's something using dblink down in there somewhere?
>
> Also, if you're using a recent PG release, the postmaster log
> should contain additional information about the deadlock, like the
> specific queries involved on both sides.

I can't think of any other process running concurrently... but yeah
I'm surprised as well, and no there is no dblink running.

I may be wrong on assuming there is no other process running and
I'll check further, but at least I'm pretty sure there is no query
that is writing in that table other than the update.
These start to happen when involved row are above 80K and chances it
happens grow up as the number of rows involved grow (not a big
surprise).
Chances that this happens decrease if I drop the triggers. This may
just be related to the chance that 2 queries clashes since leaving
the triggers there increase the execution time.

There is nothing else on the log other than:
2010-10-26 18:12:09 CEST ERROR: deadlock detected
2010-10-26 18:12:09 CEST DETAIL: Process 9729 waits for ShareLock
on transaction 110146905; blocked by process 11082. Process 11082
waits for ShareLock on transaction 110145470; blocked by process
9729.

Followed by the update statement...

How am I going to find which statements where locking each other?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it