pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_locks-exclusivelock for select queries
3+ messages / 3 participants
[nested] [flat]

* pg_locks-exclusivelock for select queries
@ 2024-03-22 07:13 arun chirappurath <arunsnmimt@gmail.com>
  2024-03-22 10:10 ` Re: pg_locks-exclusivelock for select queries Laurenz Albe <laurenz.albe@cybertec.at>
  2024-03-22 14:18 ` Re: pg_locks-exclusivelock for select queries Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: arun chirappurath @ 2024-03-22 07:13 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: pg_locks-exclusivelock for select queries
  2024-03-22 07:13 pg_locks-exclusivelock for select queries arun chirappurath <arunsnmimt@gmail.com>
@ 2024-03-22 10:10 ` Laurenz Albe <laurenz.albe@cybertec.at>
  1 sibling, 0 replies; 3+ messages in thread

From: Laurenz Albe @ 2024-03-22 10:10 UTC (permalink / raw)
  To: arun chirappurath <arunsnmimt@gmail.com>; pgsql-general@lists.postgresql.org

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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: pg_locks-exclusivelock for select queries
  2024-03-22 07:13 pg_locks-exclusivelock for select queries arun chirappurath <arunsnmimt@gmail.com>
@ 2024-03-22 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2024-03-22 14:18 UTC (permalink / raw)
  To: arun chirappurath <arunsnmimt@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2024-03-22 14:18 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-22 07:13 pg_locks-exclusivelock for select queries arun chirappurath <arunsnmimt@gmail.com>
2024-03-22 10:10 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-22 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox