本節介紹了Tuple可見性判斷的基本邏輯,以下判斷邏輯來自於Bruce Momjian寫的[Mvcc Unmasked - Bruce Momjian](https://momjian.us/main/writings/pgsql/mvcc.pdf),推薦閱讀.

**判斷邏輯**
((Xmin == my-transaction &&　　　　　　　inserted by the current transaction
　Cmin < my-command &&　　　　　　　　　　before this command, and
　(Xmax is null ||　　　　　　　　　　　　the row has not been deleted, or
　　(Xmax == my-transaction &&　　　　　　it was deleted by the current transaction
　　　Cmax >= my-command)))　　　　　　　but not before this command,
||　　　　　　　　　　　　　　　　　　　　　or
　(Xmin is committed &&　　　　　　　　　　the row was inserted by a committed transaction, and
　　(Xmax is null ||　　　　　　　　　　　　the row has not been deleted, or
　　　(Xmax == my-transaction &&　　　　　the row is being deleted by this transaction
　　　　Cmax >= my-command) ||　　　　　　but it’s not deleted "yet", or
　　　　(Xmax != my-transaction &&　　　　the row was deleted by another transaction
　　　　　Xmax is not committed))))　　　　that has not been committed

其中,my-command在PG中透過cmin/cmax體現.
*cmin/cmax*
如下例所示,遊標在刪除命令前定義,因此Tuple可見.
```
10:51:21 (xdb@[local]:5432)testdb=# DELETE FROM mvcc_demo;
DELETE 3
10:51:22 (xdb@[local]:5432)testdb=# 
10:51:22 (xdb@[local]:5432)testdb=# BEGIN WORK;
BEGIN
10:51:22 (xdb@[local]:5432)testdb=#* INSERT INTO mvcc_demo VALUES (1);
INSERT 0 1
10:51:22 (xdb@[local]:5432)testdb=#* INSERT INTO mvcc_demo VALUES (2);
INSERT 0 1
10:51:22 (xdb@[local]:5432)testdb=#* INSERT INTO mvcc_demo VALUES (3);
INSERT 0 1
10:51:22 (xdb@[local]:5432)testdb=#* SELECT xmin, cmin, xmax, cmax, * FROM mvcc_demo;
 xmin | cmin | xmax | cmax | val 
------+------+------+------+-----
 2400 |    0 |    0 |    0 |   1
 2400 |    1 |    0 |    1 |   2
 2400 |    2 |    0 |    2 |   3
(3 rows)

10:51:23 (xdb@[local]:5432)testdb=#* DECLARE c_mvcc_demo CURSOR FOR
[local] xdb@testdb-# SELECT xmin, cmin, xmax, cmax, * FROM mvcc_demo; --> 定義遊標
DECLARE CURSOR
10:51:34 (xdb@[local]:5432)testdb=#* DELETE FROM mvcc_demo; --> 刪除資料
DELETE 3
10:51:34 (xdb@[local]:5432)testdb=#* SELECT xmin, cmin, xmax, cmax, * FROM mvcc_demo;
 xmin | cmin | xmax | cmax | val 
------+------+------+------+-----
(0 rows)

10:51:35 (xdb@[local]:5432)testdb=#* FETCH ALL FROM c_mvcc_demo; --> 遊標在刪除命令前定義,因此可見
 xmin | cmin | xmax | cmax | val 
------+------+------+------+-----
 2400 |    0 | 2400 |    0 |   1
 2400 |    1 | 2400 |    1 |   2
 2400 |    2 | 2400 |    2 |   3
(3 rows)
```

**參考資料**
[Mvcc Unmasked - Bruce Momjian](https://momjian.us/main/writings/pgsql/mvcc.pdf)
[HeapTupleSatisfiesSelf](https://doxygen.postgresql.org/tqual_8c_source.html#l00176)
[PostgreSQL Source Code](https://doxygen.postgresql.org/snapmgr_8c_source.html#l00304)

PostgreSQL DBA(26) - MVCC#6(Tuple可見性判斷-簡化版)

相關文章