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 <[EMAIL PROTECTED]> 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 >