In the last post we’ve looked at the basics when it comes to row locking in PostgreSQL and how that looks like in pg_locks. In this post we’ll look at transaction IDs and virtual transaction IDs in the area of locking.

We’ll start with the same simple table as in the previous post:

postgres=# create table t ( a int );
CREATE TABLE
postgres=# insert into t values(1);
INSERT 0 1

The same test as last time, two transactions updating the same row:

-- session 2
postgres=# begin;
BEGIN
postgres=*# update t set a = 2 where a = 1;
UPDATE 1
postgres=*# 

-- session 3
postgres=# begin;
BEGIN
postgres=*# update t set a = 2 where a = 1;

In the first session we look at pg_locks again but this time we’re only interested in lock types “virtualxid” and “transactionid”:

postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,transactionid,virtualxid from pg_locks where pid != pg_backend_pid() and locktype in ('virtualxid','transactionid') order by pid;
   locktype    | database | relation | pid  |     mode      | granted |           waitstart           | transactionid | virtualxid 
---------------+----------+----------+------+---------------+---------+-------------------------------+---------------+------------
 virtualxid    |          |          | 1809 | ExclusiveLock | t       |                               |               | 9/2
 transactionid |          |          | 1809 | ExclusiveLock | t       |                               |           857 | 
 virtualxid    |          |          | 2142 | ExclusiveLock | t       |                               |               | 18/2
 transactionid |          |          | 2142 | ShareLock     | f       | 2026-01-19 12:54:25.658982+01 |           857 | 
 transactionid |          |          | 2142 | ExclusiveLock | t       |                               |           858 | 
(5 rows)

What we can see here is that both sessions have an exclusive lock on their on virtual transaction ID and on their own transaction ID, which makes sense as noone else must do anything with those. The second session however also tries to get a “ShareLock” on the other sessions transaction ID (which is 857). This, of course, will block as this transaction needs to wait until the first one (857) either commits or rollbacks. As soon as that happens:

-- session 2
postgres=*# commit;
COMMIT

… that lock goes away and we only see the two locks of the remaining session.

-- session 1
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,transactionid,virtualxid from pg_locks where pid != pg_backend_pid() and locktype in ('virtualxid','transactionid') order by pid;
   locktype    | database | relation | pid  |     mode      | granted | waitstart | transactionid | virtualxid 
---------------+----------+----------+------+---------------+---------+-----------+---------------+------------
 virtualxid    |          |          | 2142 | ExclusiveLock | t       |           |               | 18/2
 transactionid |          |          | 2142 | ExclusiveLock | t       |           |           858 | 
(2 rows)

The question is: What is a virtual transaction ID? The answer is in the documentation, for short: To prevent burning real transaction IDs when it is not required, each transaction gets it’s own virtual transaction ID. Only when the transaction makes changes to the database a real transaction ID will be assigned. If the whole transaction is read only, this assignment never happens.

We can demonstrate that easily using the first and second session. Currently we do not see anything in pg_locks in the first session as no transaction is running (session three has committed in the meantime):

postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,transactionid,virtualxid from pg_locks where pid != pg_backend_pid() and locktype in ('virtualxid','transactionid') order by pid;
 locktype | database | relation | pid | mode | granted | waitstart | transactionid | virtualxid 
----------+----------+----------+-----+------+---------+-----------+---------------+------------
(0 rows)

In the second session we start a transaction and check the contents of our test table:

-- session 2
postgres=# begin;
BEGIN
postgres=*# select * from t;
 a 
---
 2
(1 row)

postgres=*#

Back to session one, what do we see?

-- session 1
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,transactionid,virtualxid from pg_locks where pid != pg_backend_pid() and locktype in ('virtualxid','transactionid') order by pid;
  locktype  | database | relation | pid  |     mode      | granted | waitstart | transactionid | virtualxid 
------------+----------+----------+------+---------------+---------+-----------+---------------+------------
 virtualxid |          |          | 1809 | ExclusiveLock | t       |           |               | 9/3
(1 row)

The session only show up with a lock on it’s virtual transaction ID because we did not change anything in the database. As soon as we do this:

-- session 2
postgres=*# insert into t values(3);
INSERT 0 1
postgres=*# select * from t;
 a 
---
 2
 3
(2 rows)

… we get a second row in pg_locks and the real transaction ID shows up. As before, both are protected by an “ExclusiveLock”:

-- session 1
postgres=# select locktype,database::regdatabase,relation::regclass,pid,mode,granted,waitstart,transactionid,virtualxid from pg_locks where pid != pg_backend_pid() and locktype in ('virtualxid','transactionid') order by pid;
   locktype    | database | relation | pid  |     mode      | granted | waitstart | transactionid | virtualxid 
---------------+----------+----------+------+---------------+---------+-----------+---------------+------------
 virtualxid    |          |          | 1809 | ExclusiveLock | t       |           |               | 9/3
 transactionid |          |          | 1809 | ExclusiveLock | t       |           |           859 | 
(2 rows)

Post Views: 1
Thumbnail [60x60]
by
Daniel Westermann