I am trying to understand locks in postgres and here is what I have understood till now.
As per my understanding virtualxids are not real transactions and transactionidxid is what indicates a transaction.
When I do a simple connect to the psql shell, and I query the pg_locks table there is already a virtualxid present there.
I have not initiated a transaction or ran a query, so why this virtualxid is created ? Is it because that this connection might initiate a transaction in the future?
What am I missing here?
Connecting does not create a transaction but issuing select * from pg_locks
implicitly does if the session hasn't a current open transaction already.
This is per the rule that you quoted in the question: All statements in postgres runs inside of a transaction either explicit/implicit
The query select * from pg_locks
cannot be a neutral observer, as it requires for its own use a transaction and a lock on pg_locks
. As a consequence, this select will always report at least two entries as in the example below:
test=> \x test=> SELECT relation::regclass AS relname, * FROM pg_locks; -[ RECORD 1 ]------+---------------- relname | pg_locks locktype | relation database | 113270 relation | 11000 page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 2/5789 pid | 31376 mode | AccessShareLock granted | t -[ RECORD 2 ]------+---------------- relname | locktype | virtualxid database | relation | page | tuple | virtualxid | 2/5789 transactionid | classid | objid | objsubid | virtualtransaction | 2/5789 pid | 31376 mode | ExclusiveLock granted | t
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With