Re: Exclusive Locks Taken on User Tables?

This is the query that I'm running to view locks:

SELECT pg_class.relname AS table,
pg_database.datname AS database,
transaction, pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid
ORDER BY pg_class.relname, mode

I'm pretty sure this filters out transactionid lock types because I'm
joining to pg_database and pg_class. Pls correct me if I'm wrong though.

On Nov 6, 2007 2:22 PM, Richard Huxton <dev(at)archonet(dot)com> wrote:

> Marc wrote:
> > Hi,
> >
> > I'm seeing an "EXCLUSIVE" lock being taken on a table even though the
> > documentation says that "This lock mode is not automatically acquired on
> > user tables by any PostgreSQL command."
>
> Hmm - are you sure?
>
> > My SQL is
> > UPDATE users SET online = $1 where username = $2
> >
> > username is the PK on the users table.
>
> Difficult to believe that's locking the whole table.
>
> > Other locks taken by the transaction are 1 RowExclusiveLock for the
> users
> > table and 1 RowExclusiveLock on each of the 6 explict indexes on that
> table
> > and another for the implicity users_pkey index.
>
> Fair enough.
>
> > The result of these locks is that concurrent calls for the same
> statement
> > are being serialized because the ExclusiveLock being requested is not
> being
> > granted.
>
> Doesn't sound right.
>
> Are you sure your ExclusiveLock isn't on a "transactionid" rather than a
> "relation"? Every transaction has an exclusive lock on itself.
>
> Are you sure subsequent transactions affecting that row aren't just
> waiting to see if the original commits? That's normal behaviour.
>
> --
> Richard Huxton
> Archonet Ltd
>