virtualidx exclusive lock

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 exclusive lock 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 exclusive lock 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