pg mvcc

Session A:
mydb=# select * from car;
id | name
----+------
1 | aa
2 | bb
(2 rows)
Time: 0.264 ms
mydb=# begin;
BEGIN
Time: 0.069 ms
mydb=*# delete from car;
DELETE 2
Time: 0.227 ms
mydb=*# select pg_current_xact_id();
pg_current_xact_id
--------------------
810
(1 row)
Time: 0.162 ms
mydb=*# select ctid,xmin,xmax,* from car;
ctid | xmin | xmax | id | name
------+------+------+----+------
(0 rows)
Time: 0.132 ms
mydb=*# select pg_current_xact_id();
pg_current_xact_id
--------------------
810
(1 row)
Time: 0.098 ms

mydb=*# commit;
COMMIT
Time: 5.824 ms
mydb=# select ctid,xmin,xmax,* from car;
ctid | xmin | xmax | id | name
------+------+------+----+------
(0 rows)
Time: 0.210 ms
mydb=# select pg_current_xact_id();
pg_current_xact_id
--------------------
814
(1 row)

Session B:

mydb=# select pg_current_xact_id();
pg_current_xact_id
--------------------
813
(1 row)

Time: 0.210 ms
mydb=# select ctid,xmin,xmax,* from car;
ctid | xmin | xmax | id | name
-------+------+------+----+------
(0,2) | 809 | 810 | 1 | aa
(0,3) | 809 | 810 | 2 | bb
(2 rows)

Time: 0.417 ms
mydb=# select ctid,xmin,xmax,* from car;
ctid | xmin | xmax | id | name
------+------+------+----+------
(0 rows)

Time: 0.201 ms
mydb=# select pg_current_xact_id();
pg_current_xact_id
--------------------
815
(1 row)

Time: 0.598 ms

====================================

mydb=# select ctid,xmin,xmax,* from car;
ctid | xmin | xmax | id | name
-------+------+------+----+------
(0,4) | 816 | 0 | 1 | aa
(0,5) | 816 | 0 | 2 | bb
(0,6) | 819 | 0 | 3 | cc
(3 rows)

mydb=# select ctid,xmin,xmax,* from car;
ctid | xmin | xmax | id | name
-------+------+------+----+------
(0,4) | 816 | 0 | 1 | aa
(0,5) | 816 | 0 | 2 | bb
(2 rows)

Time: 0.393 ms
mydb=# insert into car values(3,'cc');
INSERT 0 1
Time: 3.099 ms
mydb=# select ctid,xmin,xmax,* from car;
ctid | xmin | xmax | id | name
-------+------+------+----+------
(0,4) | 816 | 0 | 1 | aa
(0,5) | 816 | 0 | 2 | bb
(0,7) | 821 | 0 | 3 | cc
(3 rows)