Обсуждение: virtualidx exclusive lock
I've googled, but there's 0 hits.
I have an issue with a ton of "idle in transaction" backends.
What I noticed is when I look at pg_locks, pretty much all of the processes being idle in transaction have an exclusive
lockof locktype "virtualidx".
Well, that doesn't make sense to me, but maybe someone here can tell me where a "virtualidx" locktype would come from.
I'msure it has to be some type of query.
There is no info about table or anything, all the records look like:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid |
virtualtransaction| pid | mode | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------
virtualxid | | | | | 63/10150 | | | | | 63/10150
| 31932 | ExclusiveLock | t
virtualxid | | | | | 48/48530 | | | | |
48/48530 | 31323 | ExclusiveLock | t
virtualxid | | | | | 47/52387 | | | | |
47/52387 | 31321 | ExclusiveLock | t
virtualxid | | | | | 76/4086 | | | | | 76/4086
| 32074 | ExclusiveLock | t
virtualxid | | | | | 15/6007096 | | | | |
15/6007096 | 31169 | ExclusiveLock | t
virtualxid | | | | | 10/5689919 | | | | |
10/5689919 | 31595 | ExclusiveLock | t
virtualxid | | | | | 32/603998 | | | | |
32/603998 | 31213 | ExclusiveLock | t
virtualxid | | | | | 42/117511 | | | | |
42/117511 | 31270 | ExclusiveLock | t
virtualxid | | | | | 39/279415 | | | | |
39/279415 | 31267 | ExclusiveLock | t
Is there a way to find out what query, or in lack of that at least what table is involved?
Thanks
Uwe
On 9 Nov 2009, at 8:38, Uwe Schroeder wrote: > > I've googled, but there's 0 hits. That's because you were looking for the wrong keyword, it doesn't read "virtualidx" ;) > There is no info about table or anything, all the records look like: > > locktype | database | relation | page | tuple | virtualxid | > transactionid | classid | objid | objsubid | virtualtransaction | > pid | mode | granted > ---------------+----------+----------+------+-------+------------ > +---------------+---------+-------+----------+-------------------- > +-------+------------------+--------- > virtualxid | | | | | 63/10150 > | | | | | 63/10150 | > 31932 | ExclusiveLock | t Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4af7f65211071086815692!
On Mon, Nov 9, 2009 at 7:38 AM, Uwe Schroeder <uwe@oss4u.com> wrote: > What I noticed is when I look at pg_locks, pretty much all of the processes being idle in transaction have an exclusivelock of locktype "virtualidx". It's "virtualxid" as in "virtual transaction id" and hopefully more than pretty much all have a lock of this type -- *all* transactions start with a lock on their own transaction id and hold it until they finish. That's how other transactions wait for a transaction to finish, by attempting to get a lock on the transaction id of the transaction they're waiting on. -- greg
Uwe Schroeder wrote: > I've googled, but there's 0 hits. > > I have an issue with a ton of "idle in transaction" backends. > What I noticed is when I look at pg_locks, pretty much all of the processes being idle in transaction have an exclusivelock of locktype "virtualidx > "Idle in Transaction" occurs when a client has issued a BEGIN; and is then just sitting there. We had this problem extensively with our Java code some years back when the PostgreSQL JDBC module was issuing a BEGIN; right after a COMMIT or ROLLBACK when not in autocommit mode. An updated version of JDBC postponed this automatic BEGIN until the first command was issued. Since we had some connections which would sit idle for hours, this would prevent VACUUM from cleaning anything newer than the oldest pending transaction. .
On Sunday 08 November 2009 11:38:28 pm Uwe Schroeder wrote: > I've googled, but there's 0 hits. > > I have an issue with a ton of "idle in transaction" backends. > What I noticed is when I look at pg_locks, pretty much all of the processes > being idle in transaction have an exclusive lock of locktype "virtualidx". > > Well, that doesn't make sense to me, but maybe someone here can tell me > where a "virtualidx" locktype would come from. I'm sure it has to be some > type of query. There is no info about table or anything, all the records > look like: > Is there a way to find out what query, or in lack of that at least what > table is involved? Thanks everyone. No wonder I didn't find anything on google :-) Turns out the issue was related to the ORM my app is using. That darn thing keeps a cursor open for every select - which certainly keeps the transaction alive (why it uses a transaction for a simple select is the other thing). Anyways, I got it fixed. Thanks Uwe
Uwe Schroeder wrote: > On Sunday 08 November 2009 11:38:28 pm Uwe Schroeder wrote: > (why it uses a transaction for a simple select is the other thing). Every database interaction happens within a transaction. -- Guy Rouillier