Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_locks-exclusivelock for select queries

pg_locks-exclusivelock for select queries

From
arun chirappurath
Date:
Dear all,

I am running below query on a database. why is it creating a exclusive lock on a virtualxid?  I am running some SELECT queries and its creating an ExclusiveLock in virtualxid? is this normal?

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age

FROM pg_stat_activity

WHERE state <> 'idle'

--AND query NOT LIKE '% FROM pg_stat_activity %'

ORDER BY age;


|locktype  |database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid   |mode           |granted|fastpath|waitstart|
|----------|--------|--------|----|-----|----------|-------------|-------|-----|--------|------------------|------|---------------|-------|--------|---------|
|relation  |58,007  |12,073  |    |     |          |             |       |     |        |5/165             |21,912|AccessShareLock|true   |true    |         |
|virtualxid|        |        |    |     |5/165     |             |       |     |        |5/165             |21,912|ExclusiveLock  |true   |true    |         |

Thanks,
ACDBA



Re: pg_locks-exclusivelock for select queries

From
Laurenz Albe
Date:
On Fri, 2024-03-22 at 12:43 +0530, arun chirappurath wrote:
> I am running below query on a database. why is it creating a exclusive lock on a virtualxid?
> I am running some SELECT queries and its creating an ExclusiveLock in virtualxid? is this normal?
>
> SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age
> FROM pg_stat_activity
> WHERE state <> 'idle'
>     --AND query NOT LIKE '% FROM pg_stat_activity %'
> ORDER BY age;
>
> |locktype  |database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid 
 |mode          |granted|fastpath|waitstart| 
>
|----------|--------|--------|----|-----|----------|-------------|-------|-----|--------|------------------|------|---------------|-------|--------|---------|
> |relation  |58,007  |12,073  |    |     |          |             |       |     |        |5/165           
 |21,912|AccessShareLock|true  |true    |         | 
> |virtualxid|        |        |    |     |5/165     |             |       |     |        |5/165           
 |21,912|ExclusiveLock |true   |true    |         | 

That's normal.  Every transaction has an exclusive lock on its own transaction ID.

Yours,
Laurenz Albe

Re: pg_locks-exclusivelock for select queries

From
Tom Lane
Date:
arun chirappurath <arunsnmimt@gmail.com> writes:
> I am running below query on a database. why is it creating a exclusive lock
> on a virtualxid?  I am running some SELECT queries and its creating an
> ExclusiveLock in virtualxid? is this normal?

Yes.  That lock has nothing to do with any table, only with the
transaction's own existence.  It can't conflict when acquired,
because the virtual XID is unique (at least across existing sessions).
It exists so that other sessions can wait for this one if needful, by
trying to take share lock on the virtualxid.

            regards, tom lane