Thread: Exclusive Locks Taken on User Tables?

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

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


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.

Marc <pgsql-general@mbreslow.net> writes:
> 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.

It won't filter out row-level locks on rows within tables.  You're
probably looking at a transient row lock taken by a blocked SELECT FOR
UPDATE.  You didn't show exactly what the real problem was, but I'm
wondering if this is foreign-key conflicts in a pre-8.1 PG version.

            regards, tom lane

Marc <pgsql-general@mbreslow.net> writes:
> Version of postgres is 8.2.4.
> ...
> second report that I always see that UPDATE statement and in the list of
> locks I see ExclusiveLock granted on the users table for one of the running
> pids but not the others.

Well, if it's not a foreign key issue then I think that the UPDATE is
blocked waiting for some previous updater of the same row to commit.
If you poke around a bit harder in pg_locks you'll probably find that
the UPDATE is waiting to acquire ShareLock on someone else's transaction
ID, and that someone else is the culprit.

            regards, tom lane

Ok.  I'll keep looking at pg_locks. 

My original reason for reaching out to the list was over confusion as to when an EXCLUSIVE lock would be taken table level since the documentation says this should never happen except to some system catalogs.  Is there something missing from the documentation?  I feel like that would be a big clue.  Here is what I'm referencing: http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html

Thanks for your help!
---Marc

Marc <pgsql-general@mbreslow.net> writes:
> My original reason for reaching out to the list was over confusion as to
> when an EXCLUSIVE lock would be taken table level since the documentation
> says this should never happen except to some system catalogs.  Is there
> something missing from the documentation?

It's not a lock on a table, as you would soon find out if you were
paying attention to more columns of the pg_locks view.  It's a lock
on a row.

            regards, tom lane

On Tue, 2007-11-06 at 15:53 -0500, Marc wrote:
> Ok.  I'll keep looking at pg_locks.
>
> My original reason for reaching out to the list was over confusion as
> to when an EXCLUSIVE lock would be taken table level since the
> documentation says this should never happen except to some system
> catalogs.  Is there something missing from the documentation?  I feel
> like that would be a big clue.  Here is what I'm referencing:
> http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html

OK, I see what you mean.

This page:
http://www.postgresql.org/docs/8.2/interactive/view-pg-locks.html
references the section on titled Table-level Locks, rather than
referencing the locking chapter in general.

The docs don't discuss that ExclusiveLock applies to the locktype, not
to the Table in all cases, which could easily be inferred from what is
there. I've had that question before myself.

The tuple level ExclusiveLocks you are seeing are locking only the rows;
the table containing those rows will not be ExclusiveLock-ed.

The docs are correct in what they say about *table-level* (i.e. relation
as referred to by pg_locks) ExclusiveLocks.

I'll submit a patch.

--
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

Version of postgres is 8.2.4.

Maybe it will help to give more b/g on how I'm identifying the problem? 

The way this materializes as a real issue surrounds transactions left idle.  There is a bug in our app that we haven't tracked down yet where on occasion we end up with connections marked "<IDLE> in transaction".  As a stop-gap for now, I wrote a script that does the following:

1) queries postgres to identify those connections

2) When it finds PIDs that match the criteria, we run some diagnostic queries before killing the PIDs (to help us track down the bug in our app that's the root cause)
a) List of non-idle statements
b) List of database locks
3) It then kills the PIDs and sleeps for 30s before again printing a list of the non-idle statements that are running for more then 1 minute.  At this point, I kill those because I presume they are deadlocked.  It's in this second report that I always see that UPDATE statement and in the list of locks I see ExclusiveLock granted on the users table for one of the running pids but not the others.