postgresqldemomvcc

How to showcase the work of MVCC with several parallel sessions in PostgreSQL?

I need to demonstrate the work of MVCC in PostgreSQL with the help of parallel sessions that will access the same table at the same time.

I have no idea how to do it. Tell me, please.

Solution

  • Session 1:

    CREATE TABLE test(id integer);

INSERT INTO test VALUES (1);

START TRANSACTION;

SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,1) | 5163 |    0 |  1
(1 row)

    This row version was created by transaction 5163. It is item 1 in table block 0.

    UPDATE test SET id = 2;

SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,2) | 5164 |    0 |  2
(1 row)

    The update inserts a new row version. This is item 2 in table block 0, created by transaction 5164 (this transaction).

    Session 2:

    SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,1) | 5163 | 5164 |  1
(1 row)

    Session 2 still sees the old row version, because the deleting transaction 5164 has not yet committed.

    Session 1:

    COMMIT;

    Session 2:

    SELECT ctid, xmin, xmax, id FROM test;
 ctid  | xmin | xmax | id 
-------+------+------+----
 (0,2) | 5164 |    0 |  2
(1 row)

    Now that session 1 has committed, session 2 sees the new row version as well.