这篇文章主要介绍“PostgreSQL中Tuple可见性判断的基本逻辑是什么”，在日常操作中，相信很多人在PostgreSQL中Tuple可见性判断的基本逻辑是什么问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL中Tuple可见性判断的基本逻辑是什么”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
判断逻辑
((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)到此，关于“PostgreSQL中Tuple可见性判断的基本逻辑是什么”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
