postgresRC隔离级别下可见性实验

小伙伴一起讨论的case，感觉比较有助于理解mvcc
https://www.postgresql.org/docs/13/transaction-iso.html

More complex usage can produce undesirable results in Read Committed mode. For example, consider a DELETE command operating on data that is being both added and removed from its restriction criteria by another command, e.g., assume website is a two-row table with website.hits equaling 9 and 10:

BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

The DELETE will have no effect even though there is a website.hits = 10 row before and after the UPDATE. This occurs because the pre-update row value 9 is skipped, and when the UPDATE completes and DELETE obtains a lock, the new row value is no longer 10 but 11, which no longer matches the criteria.

Because Read Committed mode starts each command with a new snapshot that includes all transactions committed up to that instant, subsequent commands in the same transaction will see the effects of the committed concurrent transaction in any case. The point at issue above is whether or not a single command sees an absolutely consistent view of the database.

taria=# create table snapshottest(id int);
CREATE TABLE
taria=# insert into snapshottest values (9);
INSERT 0 1
taria=# insert into snapshottest values (10);
INSERT 0 1
taria=# begin;
BEGIN
taria=*# update snapshottest set id = id +1;  
UPDATE 2
          ---- 这时候开启一个delete snapshottest where id = 10; 会被卡住因为update 持锁
taria=*# commit;

update commit之后 delete开始执行 返回删除0行
taria=# delete from snapshottest where id = 10;
DELETE 0

已知xmin记录插入事务id，xmax控制删除事务id，这里update属于删除再插入，事务id大的可见。

create table rctest2(id int);
INSERT INTO rctest2  values(9);
INSERT INTO rctest2  values(10);
INSERT 0 1
INSERT 0 1

--开启update事务
taria=# begin;
BEGIN
taria=*# SELECT txid_current();
 txid_current 
--------------
          546
(1 row)

这时候再开一个观察trx

taria=# begin;
BEGIN
taria=*# SELECT txid_current();
 txid_current 
--------------
          547
(1 row)
taria=*# SELECT xmin,xmax,id FROM rctest2 WHERE id = 10; --当前id = 10 的状态
 xmin | xmax | id 
------+------+----
  540 |  544 | 10
(1 row)

第一个事务update

taria=*#  update rctest2 set id = 13 where id = 10;
UPDATE 1

第二个事务

taria=*# SELECT xmin,xmax,id FROM rctest2 WHERE id = 10;
 xmin | xmax | id 
------+------+----
  540 |  544 | 10
(1 row)

--第一个事务update后变了 说明read commited事务内xmin和xmax是会变的

taria=*# SELECT xmin,xmax,id FROM rctest2 WHERE id = 10;
 xmin | xmax | id 
------+------+----
  540 |  546 | 10
(1 row)

taria=*# SELECT * FROM  v_pageinspect;
 ctid  |  case  | xmin | xmax | t_ctid 
-------+--------+------+------+--------
 (0,3) | normal |  540 |  546 | (0,7)
 (0,4) | normal |  540 |  541 | (0,4)
 (0,7) | normal |  546 |    0 | (0,7)
(7 rows)
taria=*# delete from rctest2 where id = 10;

在发起 依然卡住 commit第一个事务后
taria=*# SELECT xmin,xmax,id FROM rctest2 WHERE id = 13;
 xmin | xmax | id 
------+------+----
  546 |  547 | 13  看到id 13 xmax标记547  这里不是因为删除 是因为加了行锁 后面验证
(1 row)
taria=*# SELECT xmin,xmax,id FROM rctest2 WHERE id = 11;
 xmin | xmax | id 
------+------+----
  540 |  541 | 11
(1 row)

所以到这里，开头的update后接着delete 导致删除0行就可以解释了，因为rc隔离级别下，xmin，xmax是在其他事务中变化的，因此数据可见性也在变化。

现在验证下为什么xmax会被标记（不为0）。

xmax本是标记删除事务id，但是这里数据没有被删除为什么被标记了delete的事务id。通过查询资料了解到，行锁也会标记xmax。

taria=# begin;
BEGIN
taria=*# SELECT xmin,xmax,id FROM rctest2 WHERE id = 13;
 xmin | xmax | id 
------+------+----
  546 |  547 | 13
(1 row)

taria=*#  SELECT txid_current();
 txid_current 
--------------
          548
(1 row)

taria=*# select * from rctest2 where id = 13 for update;
 id 
----
 13
(1 row)

taria=*# SELECT xmin,xmax,id FROM rctest2 WHERE id = 13;
 xmin | xmax | id 
------+------+----
  546 |  548 | 13
(1 row)

taria=*# rollback;
ROLLBACK
taria=#  SELECT xmin,xmax,id FROM rctest2 WHERE id = 13;
 xmin | xmax | id 
------+------+----
  546 |  548 | 13
(1 row)

可见加了锁也会更新xmax。

Reference

• https://blog.csdn.net/qq_31156277/article/details/90551978

• https://my.oschina.net/postgresqlchina/blog/5073822

• https://www.modb.pro/db/61894

回家后突发奇想想看看升一个隔离级别会怎么样：

taria=#  show default_transaction_isolation;
 default_transaction_isolation 
-------------------------------
 repeatable read
(1 row)

这里就不描述执行细节了
总结就是，在update提交之前 delete依然是被卡住 因为没有获得锁，但是update提交后，delete会报错：

taria=*#  delete from rctest2 where id = 19;
ERROR:  could not serialize access due to concurrent update
taria=!# commit;
ROLLBACK
提交也只会被rollback；

Reference

• https://blog.csdn.net/qq_32924343/article/details/77834955