理解PG的xmin和xmax的幾個小實驗
看了一些關於PostgreSQL的xmin與xmax的解釋，感覺理解不到位，不妨做幾個小實驗來深入理解一下。
紙上得來終覺淺，絕知此事要躬行！
準備工作
CREATE TABLE parent( p_id integer PRIMARY KEY, p_val text ); CREATE TABLE child( c_id integer PRIMARY KEY, p_id integer REFERENCES parent(p_id), c_val text ); INSERT INTO parent (p_id, p_val) VALUES (42, 'parent');
第一個實驗
#### 剛剛開始，表裡面沒有值
dump=# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val ------+------+------+------+------- (0 rows)
#### 插入一行記錄到表parent
dump=# begin; BEGIN dump=*# dump=*# INSERT INTO parent (p_id, p_val) VALUES (42, 'parent'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2705 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2705 | 0 | 42 | parent (1 row)
ctid is the physical location of the tuple (Block 0, item 1)
xmin contains the ID(2705) of the inserting transaction
xmax is zero because the row is alive.
#### 做一個rollback操作，這個時候表裡面的資料就沒有了
dump=*# rollback; ROLLBACK dump=# dump=# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val ------+------+------+------+------- (0 rows)
#### 但是實際上這行的資料保留在page裡面，可以透過extension pageinspect中的方法來檢視
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2705 | 0 | f | f | t | f | \x2a000000 | \x0f706172656e74
(1 row)
因為我們做了insert的rollback操作,這裡 xmax_committed => f, xmax_rolled_back => t
沒有delete操作 xmax => 0
#### 做vacuum操作，資料才從page裡面被刪除掉
dump=# vacuum parent; VACUUM
第二個實驗
#### 插入一行
dump=# begin; BEGIN dump=*# INSERT INTO parent (p_id, p_val) VALUES (42, 'parent'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2707 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2707 | 0 | 42 | parent (1 row) dump=*# commit; COMMIT
#### 刪除此行
dump=# begin;
BEGIN
dump=*# DELETE FROM parent WHERE p_id = 42;
DELETE 1
dump=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
2708
(1 row)
dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent;
ctid | xmin | xmax | p_id | p_val
------+------+------+------+-------
(0 rows)
dump=*# commit;
COMMIT
dump=#
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2707 | 2708 | f | t | f | f | \x2a000000 | \x0f706172656e74
(1 row)
dump=#
因為我們做了刪除的commit操作,這裡 xmax_committed => f, xmax_rolled_back => t
有delete操作 xmax => 2708 (delete 操作的transaction id)
第三個實驗
#### 插入一行資料
dump=# begin; BEGIN dump=*# INSERT INTO parent (p_id, p_val) VALUES (42, 'parent'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2710 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2710 | 0 | 42 | parent (1 row) dump=*# commit; COMMIT
#### 刪除，然後做rollback操作
dump=# begin; BEGIN dump=*# DELETE FROM parent WHERE p_id = 42; DELETE 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2711 (1 row) dump=*# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val ------+------+------+------+------- (0 rows) dump=*# rollback; ROLLBACK dump=# dump=# SELECT ctid, xmin, xmax, p_id, p_val FROM parent; ctid | xmin | xmax | p_id | p_val -------+------+------+------+-------- (0,1) | 2710 | 2711 | 42 | parent (1 row)
注意這裡的 xmax: 2711
即使delete操作被rollback了，這裡的xmax仍然記錄了做delete操作的transaction id
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2710 | 2711 | f | f | t | f | \x2a000000 | \x0f706172656e74
(1 row)
xmax_committed: f 表示刪除操作被rollback了，資料仍然存在，可以被其他連線讀取
第四個實驗
#### Session 1: 做select ... for update 操作
dump=# begin; BEGIN dump=*# SELECT * FROM parent WHERE p_id = 42 FOR UPDATE; p_id | p_val ------+-------- 42 | parent (1 row) dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2712 (1 row)
#### Session 2: 可以看到 xmax_is_lock: t 表示在此行有lock了
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2710 | 2712 | t | f | f | f | \x2a000000 | \x0f706172656e74
(1 row)
dump=#
if xmax contains a row lock(xmax_is_lock: t), the row is active, no matter what the state of the locking transaction is.
第五個實驗
#### Session 1
dump=# begin; BEGIN dump=*# INSERT INTO child (c_id, p_id, c_val) VALUES (1, 42, 'first session'); INSERT 0 1 dump=*# select txid_current_if_assigned(); txid_current_if_assigned -------------------------- 2713 (1 row) dump=*# commit; COMMIT
#### Session 2
dump=# begin;
BEGIN
dump=*# INSERT INTO child (c_id, p_id, c_val) VALUES (2, 42, 'second session');
INSERT 0 1
dump=*# select txid_current_if_assigned();
txid_current_if_assigned
--------------------------
2714
(1 row)
dump=*# commit;
COMMIT
#### Session 3
dump=# SELECT lp,
t_ctid AS ctid,
t_xmin AS xmin,
t_xmax AS xmax,
(t_infomask & 128)::boolean AS xmax_is_lock,
(t_infomask & 1024)::boolean AS xmax_committed,
(t_infomask & 2048)::boolean AS xmax_rolled_back,
(t_infomask & 4096)::boolean AS xmax_multixact,
t_attrs[1] AS p_id,
t_attrs[2] AS p_val
FROM heap_page_item_attrs(
get_raw_page('parent', 0),
'parent'
);
lp | ctid | xmin | xmax | xmax_is_lock | xmax_committed | xmax_rolled_back | xmax_multixact | p_id | p_val
----+-------+------+------+--------------+----------------+------------------+----------------+------------+------------------
1 | (0,1) | 2710 | 1 | t | f | f | t | \x2a000000 | \x0f706172656e74
(1 row)
這個地方的xmax的值變成了1, xmax_multixact變成了 t
這是因為有兩個transaction（2713，2714）對parent表的這一行施加了lock，那麼這裡就不能在xmax只記錄其中一個transaction了，因為單獨記錄 2713或者2714
它記錄了1，那麼透過下面的sql可以找到1對應的2個transaction id
dump=# SELECT * FROM pg_get_multixact_members('1');
xid | mode
------+-------
2713 | keysh
2714 | keysh
(2 rows)
dump=#
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/725820/viewspace-2935284/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- String和StringBuffer的幾個小程式
- Linux實驗的幾個基礎命令Linux
- 分享幾個 SpringBoot 實用的小技巧Spring Boot
- 我理解的IPTV盒子和OTT盒子的幾個不同點
- Array的幾個小技巧
- Debug和幾個小例項
- 一個關於JAVA GC的小實驗JavaGC
- 通過實驗理解PG邏輯結構：1 使用者（角色）
- Python 日誌庫 logging 的理解和實踐經驗Python
- 圖解JVM實驗-觸發FullGC的幾個條件圖解JVMGC
- ES6 的幾個小技巧
- 構建映象的幾個小技巧
- PHP 簡單的幾個設計模式（個人理解）PHP設計模式
- 外貿郵件推送理解的幾個點
- openGauss/MogDB的uncommitted xmin問題解決MIT
- Crunchy PG手動備份實驗
- 深入 TypeScript – 2( 幾個常用的小技巧)TypeScript
- Redis學習的幾個小問題Redis
- 幾個小 trick
- PG最典型和實用的熱備指令碼實戰指令碼
- 實驗 詳解Docker的各種操作小實驗Docker
- PG獲取檔案大小的幾種方式
- 分享幾個實用的方法
- WebGPU 的幾個最佳實踐WebGPU
- 幾個小實踐帶你快速上手MindSpore
- 強烈推介的幾個微信小程式開發小技巧，簡單又實用微信小程式
- 小程式測試的幾個小Tips（趕快收藏啦！）
- Python中使用字典的幾個小技巧Python
- 折騰ChatGLM的幾個避坑小技巧
- PostgreSQL DBA(24) - MVCC#4(快照中的xmax)SQLMVCC#
- 從Spring的幾個階段理解其工作過程Spring
- Nginx的幾個常用配置和技巧Nginx
- 相見恨晚的幾個Excel小技巧，簡單實用又高效！Excel
- 理解new和實現一個new
- Excel裡面最實用的7個小技巧，不知道你會幾個呢?Excel
- 幾大排序演算法的理解和程式碼實現（超級詳細的過程）排序演算法
- 微信小程式開發中遇到的幾個小問題微信小程式
- if、else if、else判斷語句的幾個小例子