Re: 8.1.2 locking issues

Hi Ed,

In order to get which entities are responsible for the lock that you have
try using the following query.

select loc.pid , cls.relname,loc.granted as lock_status from pg_locks loc ,
pg_class cls where loc.locktype like '%sharelock%' and cls.oid=loc.relationand
loc.pid in(pids of the process from the error that are blocking each
other);

Regards
Talha Khan

On 11/9/06, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>
> Ed L. wrote:
> >
> > We are having locking issues in 8.1.2 on HP 11.23 ia64. I'm
> > trying to better understand how to conclusively identify who is
> > waiting on who and why.
> >
> > We have a series of "select for updates" on our 'sessions' table.
> > One of those queries is stuck waiting for a "transactionid"
> > locktype ShareLock. How can I tell what it's actually waiting
> > for?
>
> There should be tuple locks on pg_locks for the transaction that holds
> the transactionid that your transaction is waiting on.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>