Postgresql - 内部原理 - 插入更新删除对行版本的操作_compiled by gcc (gcc) 4.8.5 20150623 (red hat 4.8.-优快云博客

本文链接：https://blog.youkuaiyun.com/chuckchen1222/article/details/90371969

博客围绕PostgreSQL数据库展开，通过创建测试表，详细展示了插入、更新、删除以及事务内多操作等不同场景下的操作过程，涉及Transaction A、Transaction B、Session A和Session B等不同会话，有助于了解PostgreSQL事务的实际应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

0. 创建测试表

mytest=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)
mytest=# create table test0520_01(id int, col1 varchar(10));
CREATE TABLE

1. 插入

1.1 Transaction A

mytest=# begin;
BEGIN
mytest=# select txid_current();
 txid_current
--------------
      5076039
(1 row)
mytest=# insert into test0520_01 values (1,'a');
INSERT 0 1
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076039 |    0 |    0 |    0 | (0,1) |  1 | a
(1 row)

1.2 Transaction B

mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
 xmin | xmax | cmin | cmax | ctid | id | col1
------+------+------+------+------+----+------
(0 rows)

1.3 Transaction A

mytest=# commit;
COMMIT
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076039 |    0 |    0 |    0 | (0,1) |  1 | a
(1 row)

2. 更新

2.1 Session A

mytest=# begin;
BEGIN
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076039 |    0 |    0 |    0 | (0,1) |  1 | a
(1 row)

mytest=# update test0520_01 set col1 = 'b' where id = 1;
UPDATE 1
mytest=# select txid_current();
 txid_current
--------------
      5076042
(1 row)

mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076042 |    0 |    0 |    0 | (0,2) |  1 | b
(1 row)

2.2 Session B

mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   |  xmax   | cmin | cmax | ctid  | id | col1
---------+---------+------+------+-------+----+------
 5076039 | 5076042 |    0 |    0 | (0,1) |  1 | a
(1 row)

2.3 Session A

mytest=# commit;
COMMIT
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076042 |    0 |    0 |    0 | (0,2) |  1 | b
(1 row)

3. 删除

3.1 Session A

mytest=# begin;
BEGIN
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076042 |    0 |    0 |    0 | (0,2) |  1 | b
(1 row)
mytest=# delete from test0520_01 where id = 1;
DELETE 1
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
 xmin | xmax | cmin | cmax | ctid | id | col1
------+------+------+------+------+----+------
(0 rows)

3.2 Session B

mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   |  xmax   | cmin | cmax | ctid  | id | col1
---------+---------+------+------+-------+----+------
 5076042 | 5076043 |    0 |    0 | (0,2) |  1 | b
(1 row)

3.3 Session A

mytest=# commit;
COMMIT
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
 xmin | xmax | cmin | cmax | ctid | id | col1
------+------+------+------+------+----+------
(0 rows)

4. 事务内多操作

4.1 Session A

mytest=# begin;
BEGIN
mytest=# insert into test0520_01 values (11,'a');
INSERT 0 1
mytest=# insert into test0520_01 values (12,'b');
INSERT 0 1
mytest=# insert into test0520_01 values (13,'c');
INSERT 0 1
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076044 |    0 |    0 |    0 | (0,3) | 11 | a
 5076044 |    0 |    1 |    1 | (0,4) | 12 | b
 5076044 |    0 |    2 |    2 | (0,5) | 13 | c
(3 rows)

mytest=# update test0520_01 set col1 = 'x' where id = 11;
UPDATE 1
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076044 |    0 |    1 |    1 | (0,4) | 12 | b
 5076044 |    0 |    2 |    2 | (0,5) | 13 | c
 5076044 |    0 |    3 |    3 | (0,6) | 11 | x
(3 rows)

4.2 Session B

mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
 xmin | xmax | cmin | cmax | ctid | id | col1
------+------+------+------+------+----+------
(0 rows)

4.3 Session A

mytest=# commit;
COMMIT
mytest=# select xmin,xmax,cmin,cmax,ctid, * from test0520_01 ;
  xmin   | xmax | cmin | cmax | ctid  | id | col1
---------+------+------+------+-------+----+------
 5076044 |    0 |    1 |    1 | (0,4) | 12 | b
 5076044 |    0 |    2 |    2 | (0,5) | 13 | c
 5076044 |    0 |    3 |    3 | (0,6) | 11 | x
(3 rows)