1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL MVCC: future transaction should not be visible to the current transaction

• 数据库

之前读到手册以下这段话一直不太理解，如下：

手册内容的一段话

PostgreSQL’s MVCC transaction semantics depend on being able to compare transaction ID (XID) numbers:
a row version with an insertion XID greater than the current transaction’s XID is “in the future” and should
not be visible to the current transaction

备注：要理解这段话，首先得知道每行记录插入时 pg 会分配给每行 tuple 一个 xid, 这个 xid 为系统隐含字段 xmin，那么任何比 xmin 值要大的事务被认为是将来的，而且将来的事务在当前事务中不可见；这样翻译有点难懂。

后来通过实验，总算理解了。

实验一: 验证未来事务在当前事务不可见

1.1 创建测试表并插入三条记录

1
2
3
4
5
6
7
8
 
mydb=> create table test_29 (id integer,name varchar(32));  
CREATE TABLE
mydb=> insert into test_29 values (1,'a');  
INSERT 0 1  
mydb=> insert into test_29 values (2,'b');  
INSERT 0 1  
mydb=> insert into test_29 values (3,'c');  
INSERT 0 1

1.2 查询记录的 xmin

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3341  
3339 | 2 | b | 3341  
3340 | 3 | c | 3341  
(3 rows)

mydb=> select txid_current();  
txid_current  
--------------  
 3342  
(1 row)

mydb=> select txid_current();  
txid_current  
--------------  
 3343  
(1 row)

1.3 再次插入三条记录，并查询记录 xmin

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
mydb=> insert into test_29 values (4,'d');  
INSERT 0 1  
mydb=> insert into test_29 values (5,'e');  
INSERT 0 1  
mydb=> insert into test_29 values (6,'f');  
INSERT 0 1

mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3347  
3339 | 2 | b | 3347  
3340 | 3 | c | 3347  
3344 | 4 | d | 3347  
3345 | 5 | e | 3347  
3346 | 6 | f | 3347  
(6 rows)

备注：注意记录的 xmin 值。

1.4 查看控制文件：pg_controldata 输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
[postgres@pgb ~]$ pg_controldata  
pg_control version number: 903  
Catalog version number: 201105231  
Database system identifier: 5652547536925883229  
Database cluster state: in production  
pg_control last modified: Wed 02 May 2012 09:27:56 PM CST  
Latest checkpoint location: 1/BC000020  
Prior checkpoint location: 1/B8015934  
Latest checkpoint's REDO location: 1/BC000020  
Latest checkpoint's TimeLineID: 5  
Latest checkpoint's NextXID: 0/3336  
Latest checkpoint's NextOID: 66605  
Latest checkpoint's NextMultiXactId: 1  
Latest checkpoint's NextMultiOffset: 0  
Latest checkpoint's oldestXID: 1670  
Latest checkpoint's oldestXID's DB: 16386  
Latest checkpoint's oldestActiveXID: 0  
Time of latest checkpoint: Wed 02 May 2012 09:26:34 PM CST  
Minimum recovery ending location: 0/0  
Backup start location: 0/0  
Current wal_level setting: hot_standby  
Current max_connections setting: 100  
Current max_prepared_xacts setting: 0  
Current max_locks_per_xact setting: 64  
Maximum data alignment: 4  
Database block size: 8192  
Blocks per segment of large relation: 1048576  
WAL block size: 65536  
Bytes per WAL segment: 67108864  
Maximum length of identifiers: 64  
Maximum columns in an index: 32  
Maximum size of a TOAST chunk: 2000  
Date/time type storage: 64-bit integers  
Float4 argument passing: by value  
Float8 argument passing: by reference

备注：Latest checkpoint s NextXID:值为 3336

1.5 关闭 PostgreSQL

1
2
3
 
[postgres@pgb ~]$ pg_ctl stop -m fast -D $PGDATA  
waiting for server to shut down...... done  
server stopped

1.6 再次查看 pg_controldata

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
pg_control version number: 903  
Catalog version number: 201105231  
Database system identifier: 5652547536925883229  
Database cluster state: shut down  
pg_control last modified: Wed 02 May 2012 09:29:34 PM CST  
Latest checkpoint location: 1/C0000020  
Prior checkpoint location: 1/BC000020  
Latest checkpoint's REDO location: 1/C0000020  
Latest checkpoint's TimeLineID: 5  
Latest checkpoint's NextXID: 0/3348  
Latest checkpoint's NextOID: 66608  
Latest checkpoint's NextMultiXactId: 1  
Latest checkpoint's NextMultiOffset: 0  
Latest checkpoint's oldestXID: 1670  
Latest checkpoint's oldestXID's DB: 16386  
Latest checkpoint's oldestActiveXID: 0  
Time of latest checkpoint: Wed 02 May 2012 09:29:32 PM CST  
Minimum recovery ending location: 0/0  
Backup start location: 0/0  
Current wal_level setting: hot_standby  
Current max_connections setting: 100  
Current max_prepared_xacts setting: 0  
Current max_locks_per_xact setting: 64  
Maximum data alignment: 4  
Database block size: 8192  
Blocks per segment of large relation: 1048576  
WAL block size: 65536  
Bytes per WAL segment: 67108864  
Maximum length of identifiers: 64  
Maximum columns in an index: 32  
Maximum size of a TOAST chunk: 2000  
Date/time type storage: 64-bit integers  
Float4 argument passing: by value  
Float8 argument passing: by reference

备注： Latest checkpoint s NextXID: 值发生了变化，值为 3348，这是因为关闭数据库时触发了 checkpoint。

1.7 我们尝试把 NextXID 设置成 3341 ，看看会出现什么情况

1
2
3
4
 
[postgres@pgb ~]$ pg_resetxlog -x 3341 $PGDATA  
Transaction log reset  
[postgres@pgb ~]$ pg_ctl start -D $PGDATA  
server starting

1.8 再次查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
[postgres@pgb ~]$ psql -h 127.0.0.1 mydb mydb  
psql (9.1.0)  
Type "help" for help.

mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3341  
3339 | 2 | b | 3341  
3340 | 3 | c | 3341  
(3 rows)

mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3342  
3339 | 2 | b | 3342  
3340 | 3 | c | 3342  
(3 rows)

mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3343  
3339 | 2 | b | 3343  
3340 | 3 | c | 3343  
(3 rows)

mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3344  
3339 | 2 | b | 3344  
3340 | 3 | c | 3344  
(3 rows)

备注：上面一直只能查看到 test_29 表的前三条记录，后三条记录消失，那是因为后三条记录的 xmin 大于 3344，而当前事务ID 小于或等于 3344，所以后三条记录对于当前事务来说是未来的，所以在当前事务中不可见，接着查询看会出现什么情况。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3345  
3339 | 2 | b | 3345  
3340 | 3 | c | 3345  
3344 | 4 | d | 3345  
(4 rows)

mydb=> select xmin,*,txid_current() from test_29;  
xmin | id | name | txid_current  
------+----+------+--------------  
3338 | 1 | a | 3347  
3339 | 2 | b | 3347  
3340 | 3 | c | 3347  
3344 | 4 | d | 3347  
3345 | 5 | e | 3347  
3346 | 6 | f | 3347  
(6 rows)

备注：随着当前事务号的增加，后面的三条记录居然出现了。

按照上面 PostgreSQL MVCC 原理，那么可以模拟下数据库因为 Transaction ID Wraparound 失败，而造成所有数据消失的情况。

实验二: 模拟事务 “Transaction ID Wraparound” 失败，所有数据消失

2.1 操作前数据库信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
[postgres@pgb ~]$ psql -h 127.0.0.1 mydb mydb  
psql (9.1.0)  
Type "help" for help.
mydb=> /dt  
 List of relations  
Schema | Name | Type | Owner  
--------+----------------+-------+-------  
mydb | ad_position_ad | table | mydb  
mydb | array_test | table | mydb  
mydb | phone | table | mydb  
mydb | phone_201202 | table | mydb  
mydb | test | table | mydb  
mydb | test_10 | table | mydb  
mydb | test_11 | table | mydb  
mydb | test_12 | table | mydb  
mydb | test_13 | table | mydb  
mydb | test_14 | table | mydb  
mydb | test_15 | table | mydb  
mydb | test_16 | table | mydb  
mydb | test_17 | table | mydb  
mydb | test_18 | table | mydb  
mydb | test_19 | table | mydb  
mydb | test_2 | table | mydb  
mydb | test_20 | table | mydb  
mydb | test_21 | table | mydb  
mydb | test_23 | table | mydb  
mydb | test_24 | table | mydb  
mydb | test_25 | table | mydb  
mydb | test_26 | table | mydb  
mydb | test_27 | table | mydb  
mydb | test_28 | table | mydb  
mydb | test_29 | table | mydb  
mydb | test_3 | table | mydb  
mydb | test_4 | table | mydb  
mydb | test_5 | table | mydb  
mydb | test_6 | table | mydb  
mydb | test_7 | table | mydb  
mydb | test_8 | table | mydb  
mydb | test_9 | table | mydb  
mydb | test_array | table | mydb  
mydb | test_integer | table | mydb  
mydb | test_name | table | mydb  
mydb | test_standby | table | mydb  
mydb | test_unlogged | table | mydb  
mydb | test_user | table | mydb  
(38 rows)

2.2 pg_controldata 输出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
[postgres@pgb ~]$ pg_controldata  
pg_control version number: 903  
Catalog version number: 201105231  
Database system identifier: 5652547536925883229  
Database cluster state: in production  
pg_control last modified: Sat 05 May 2012 09:25:43 PM CST  
Latest checkpoint location: 1/C4000270  
Prior checkpoint location: 1/C4000020  
Latest checkpoint's REDO location: 1/C4000240  
Latest checkpoint's TimeLineID: 5  
Latest checkpoint's NextXID: 0/3348  
Latest checkpoint's NextOID: 66608  
Latest checkpoint's NextMultiXactId: 1  
Latest checkpoint's NextMultiOffset: 0  
Latest checkpoint's oldestXID: 1670  
Latest checkpoint's oldestXID's DB: 16386  
Latest checkpoint's oldestActiveXID: 3348  
Time of latest checkpoint: Sat 05 May 2012 09:25:43 PM CST  
Minimum recovery ending location: 0/0  
Backup start location: 0/0  
Current wal_level setting: hot_standby  
Current max_connections setting: 100  
Current max_prepared_xacts setting: 0  
Current max_locks_per_xact setting: 64  
Maximum data alignment: 4  
Database block size: 8192  
Blocks per segment of large relation: 1048576  
WAL block size: 65536  
Bytes per WAL segment: 67108864  
Maximum length of identifiers: 64  
Maximum columns in an index: 32  
Maximum size of a TOAST chunk: 2000  
Date/time type storage: 64-bit integers  
Float4 argument passing: by value  
Float8 argument passing: by reference

2.3 关闭数据库

1
2
3
 
[postgres@pgb ~]$ pg_ctl stop -m fast -D $PGDATA  
waiting for server to shut down...... done  
server stopped

2.4 重置 xid，设置较小的 xid

1
2
3
 
[postgres@pgb ~]$ pg_resetxlog -x 100 $PGDATA  
Transaction log reset
备注：这里将 xid 重置成 100, 看下接下来会发生什么情况。

2.5 重启数据库并查看

1
2
3
4
5
6
7
8
9
10
 
[postgres@pgb ~]$ psql -h 127.0.0.1 mydb mydb  
psql (9.1.0)  
Type "help" for help.
mydb=> /dt  
No relations found.
mydb=> select txid_current();  
txid_current  
--------------  
 100  
(1 row)

备注：所有表消失。

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/237847.html

赞 (0)
0
上一篇 2022年1月29日
下一篇 2022年1月29日

相关推荐

发表回复

登录后才能评论