Multiversion Concurrency Control

How can we verify that there can be several versions of the same row in a database?

Let's create a one-row table:

α=> CREATE TABLE t(s text);

CREATE TABLE

α=> INSERT INTO t VALUES ('First version');

INSERT 0 1

Start the first transaction and get its id (xid):

α=> BEGIN;

BEGIN

α=> SELECT txid_current();

 txid_current 
--------------
        36653
(1 row)

This transaction sees the first (so far the only) row version (tuple):

α=> SELECT *, xmin, xmax FROM t;

       s       | xmin  | xmax 
---------------+-------+------
 First version | 36652 |    0
(1 row)

Here we also show transaction ids which are determines the tuple visibility. This version is created by the previous transaction, and xmax=0 means that this is the actual row version.

Now let's start the second transaction in another session:

α=> BEGIN;

BEGIN

α=> SELECT txid_current();

 txid_current 
--------------
        36654
(1 row)

Transaction sees the same tuple:

α=> SELECT *, xmin, xmax FROM t;

       s       | xmin  | xmax 
---------------+-------+------
 First version | 36652 |    0
(1 row)

Now let's change the row in the second transaction.

α=> UPDATE t SET s = 'Second version';

UPDATE 1

Here is what we get:

α=> SELECT *, xmin, xmax FROM t;

       s        | xmin  | xmax 
----------------+-------+------
 Second version | 36654 |    0
(1 row)

And what version will the first transaction see?

As the change is not yet committed, the first transaction continues to see the first row version:

α=> SELECT *, xmin, xmax FROM t;

       s       | xmin  | xmax  
---------------+-------+-------
 First version | 36652 | 36654
(1 row)

Note that xmax has changed: it shows that another transaction is changing the row. Strictly speaking such "peeking" violates isolation rules. That's why xmin and xmax fields are hidden by default and you shouldn't rely on them in production work.

Now let's commit the changes.

α=> COMMIT;

COMMIT

What will the first transaction see now?

Now both transactions see the second version of the row:

α=> SELECT *, xmin, xmax FROM t;

       s        | xmin  | xmax 
----------------+-------+------
 Second version | 36654 |    0
(1 row)

α=> COMMIT;

COMMIT

The first row is no longer needed and can be cleaned up by vacuum.

Locks

Let's repeat the same experiment, except that both transactions will be trying to update the same row.

α=> BEGIN;

BEGIN

α=> UPDATE t SET s = 'Third version';

UPDATE 1

And in the second transaction:

α=> BEGIN;

BEGIN

α=> UPDATE t SET s = 'Forth version';

Now the second transaction "hangs": it cannot change the row until the first transaction releases the lock.

α=> COMMIT;

COMMIT

Now the second transaction can move on:

UPDATE 1

α=> COMMIT;

COMMIT

The end of demonstration.