Thread: pg_locks - what is a virtualxid locktype
Hey,
I noticed that pg_locks has an addition row for every transaction that is created with a locktype "virtualxid". Tried to search it online but I didnt find an explanation for this behavior. Does anyone can explain why it happens ?
The virtualxid lock is special. It’s a exclusive lock on the transaction’s own virtual transaction ID that every transaction always holds. No other transaction can ever acquire it while the transaction is running.
The purpose of this is to allow one transaction to wait until another transaction commits or rolls back using PostgreSQL’s locking mechanism, and it’s used internally.
On Tue, Jan 29, 2019 at 2:27 PM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hey,I noticed that pg_locks has an addition row for every transaction that is created with a locktype "virtualxid". Tried to search it online but I didnt find an explanation for this behavior. Does anyone can explain why it happens ?
The virtualxid lock is special. It’s a exclusive lock on the transaction’s own virtual transaction ID that every transaction always holds. No other transaction can ever acquire it while the transaction is running.
The purpose of this is to allow one transaction to wait until another transaction commits or rolls back using PostgreSQL’s locking mechanism, and it’s used internally.
On Tue, Jan 29, 2019 at 2:27 PM Mariel Cherkassky <mariel.cherkassky@gmail.com> wrote:
Hey,I noticed that pg_locks has an addition row for every transaction that is created with a locktype "virtualxid". Tried to search it online but I didnt find an explanation for this behavior. Does anyone can explain why it happens ?
On 2019-Jan-29, Shreeyansh Dba wrote: > The virtualxid lock is special. It’s a exclusive lock on the transaction’s > own virtual transaction ID that every transaction always holds. No other > transaction can ever acquire it while the transaction is running. > The purpose of this is to allow one transaction to wait until another > transaction commits or rolls back using PostgreSQL’s locking mechanism, and > it’s used internally. A little more specific than that: it's used when some process (such as CREATE INDEX CONCURRENTLY) needs to wait even on sessions that might be read-only. Such transactions don't have transaction-ids that write transactions have, which is why the only way is to wait on the virtual transaction-id. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2019-Jan-29, Shreeyansh Dba wrote: > The virtualxid lock is special. It’s a exclusive lock on the transaction’s > own virtual transaction ID that every transaction always holds. No other > transaction can ever acquire it while the transaction is running. > The purpose of this is to allow one transaction to wait until another > transaction commits or rolls back using PostgreSQL’s locking mechanism, and > it’s used internally. A little more specific than that: it's used when some process (such as CREATE INDEX CONCURRENTLY) needs to wait even on sessions that might be read-only. Such transactions don't have transaction-ids that write transactions have, which is why the only way is to wait on the virtual transaction-id. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services