Обсуждение: 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
[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.
This helps a lot.
I suspected as much but the locktype of transactionid threw me a bit.
I was expecting a "table" level lock of some sort.
Locating the offending thread (in my multi-threaded process) might be tricky but once I find it I am now prepared to bludgeon it into submission :-)
Regards, Tony
Thanks for the response.
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.
This helps a lot.
I suspected as much but the locktype of transactionid threw me a bit.
I was expecting a "table" level lock of some sort.
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.
Locating the offending thread (in my multi-threaded process) might be tricky but once I find it I am now prepared to bludgeon it into submission :-)
Regards, Tony