Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Understanding the virtualxid transaction type in postgres

I am trying to understand locks in postgres and here is what I have understood till now.

  • pg_locks contains lock information
  • All statements in postgres runs inside of a transaction either explicit/implicit. (Postgres 9.0 high performance)

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?

like image 494
Greedy Coder Avatar asked Oct 07 '15 15:10

Greedy Coder


1 Answers

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
like image 77
Daniel Vérité Avatar answered Sep 17 '22 13:09

Daniel Vérité