Thread: What is locktype=transactionid ?

Hi

[Apologies for the long lines - not sure how to format them better]

I have a process that is waiting for a lock and the locktype of the lock is "transactionid".

Despite a fair bit of googling I have been unable to find more information on this type of lock.

Here are the relevant rows from pg_locks:

   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode      | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------
 transactionid |          |          |      |       |            |      83827390 |         |       |          | 5/153427           | 20128 | ExclusiveLock | t
 transactionid |          |          |      |       |            |      83827390 |         |       |          | 8/171365           | 20289 | ShareLock     | f

And here are the relevant rows from pg_stat_activity:

 client_addr | client_port |       age       |  datid  |      datname      | procpid | usesysid | usename  |                            current_query                             | waiting |          xact_start           |          query_start          |         backend_start         | client_addr | client_port
-------------+-------------+-----------------+---------+-------------------+---------+----------+----------+----------------------------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+-------------+-------------
 127.0.0.1   |       40926 | 05:28:08.349825 | 1569605 | nova_experimental |   20128 |       10 | postgres | <IDLE> in transaction                                                | f       | 2010-05-17 22:12:15.083799+00 | 2010-05-17 22:12:15.90937+00  | 2010-05-17 22:10:00.024239+00 | 127.0.0.1   |       40926
 127.0.0.1   |       36054 | 05:28:08.345873 | 1569605 | nova_experimental |   20289 |       10 | postgres |               UPDATE "users"                                         | t       | 2010-05-17 22:12:15.122643+00 | 2010-05-17 22:12:15.913322+00 | 2010-05-17 22:10:31.817664+00 | 127.0.0.1   |       36054
                                                                                                           :               SET "lock_version" = 2955, "previous_passwords" = NULL                                                                                                                          
                                                                                                           :               WHERE id = 185                                                                                                                                                                  
                                                                                                           :               AND "lock_version" = 2954                                                                                                                                                       
                                                                                                           :                                                                                                                                                                                               


Can anybody point me to where I can find more information?


I am using PostgreSQL 8.3.9 on Ubuntu 9.04


Thanks

Regards, Tony

Tony Day <tonyd@panztel.com> writes:
> I have a process that is waiting for a lock and the locktype of the lock is
> "transactionid".
> Despite a fair bit of googling I have been unable to find more information
> on this type of lock.

In Postgres, every transaction takes an exclusive lock on its own
transactionid when it starts.  Sometimes, when a transaction wants to
wait for another transaction to complete, it'll try to take share lock
on that other transaction's id.  This will of course block until the
exclusive lock goes away.

Currently, the only case where anything will try to take a sharelock on
transaction id is when it is blocking on a row-level lock as a result of
trying to modify or delete or SELECT FOR UPDATE/FOR SHARE a row that the
other transaction already modified or deleted or selected FOR
UPDATE/SHARE.  (Why this doesn't show up as a more obvious row-level
lock in pg_locks is an interesting technical detail, but you probably
don't care that much about that.)

Given what you're showing in pg_stat_activity, the most likely bet is
that the "idle in transaction" client is sitting on an uncommitted row
modification.  You need to whack it upside the head and convince it to
commit or abort its modifications a bit more promptly.  The dependency
could be a bit indirect --- for instance, modifying a row that is linked
by a foreign key dependency to the one the second transaction wants to
change --- but it's a very general rule that sitting on uncommitted
modifications for any length of time is Bad Behavior.

            regards, tom lane

Hi Tom

Thanks for the response.