理解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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- PostgreSQLOracle相容性之-系統列(ctid,oid,cmin,cmax,xmin,xmax)SQLOracle
- Linux實驗的幾個基礎命令Linux
- 幾個行列轉換的實用小例子
- 我理解的IPTV盒子和OTT盒子的幾個不同點
- String和StringBuffer的幾個小程式
- Oracle Shared Cursor問題的幾個實驗Oracle
- 分享幾個 SpringBoot 實用的小技巧Spring Boot
- TSM 實施中一定要理解的幾個概念
- 一個關於JAVA GC的小實驗JavaGC
- 通過實驗理解PG邏輯結構：1 使用者（角色）
- Android幾個實戰經驗Android
- 角色許可權（Role）和系統許可權（System）的幾個澄清實驗
- 關於外來鍵的理解和實驗步驟
- 圖解JVM實驗-觸發FullGC的幾個條件圖解JVMGC
- Swift開發的幾個小技巧Swift
- 幾個專案管理的小故事專案管理
- 幾個常用的Ajax庫小節
- 幾個小 trick
- Failover過程涉及standby redo log的實驗和理解AI
- oracle實驗記錄 關於記憶體的幾個viewOracle記憶體View
- 一個小碼農這半年的經驗和教訓
- 深入 TypeScript – 2( 幾個常用的小技巧)TypeScript
- Redis學習的幾個小問題Redis
- js前端除錯的幾個小技巧JS前端除錯
- 圖靈社群的幾個小問題圖靈
- 搭建dataguard碰到的幾個小問題
- 在微軟學到的幾個小技能微軟
- 外貿郵件推送理解的幾個點
- 實驗 詳解Docker的各種操作小實驗Docker
- 強烈推介的幾個微信小程式開發小技巧，簡單又實用微信小程式
- PG獲取檔案大小的幾種方式
- PG最典型和實用的熱備指令碼實戰指令碼
- 幾個小實踐帶你快速上手MindSpore
- 幾個 Haskell 小程式Haskell
- 微信小程式開發中遇到的幾個小問題微信小程式
- PHP 簡單的幾個設計模式（個人理解）PHP設計模式
- 再次理解：關閉資料庫的幾個模式資料庫模式
- 轉：學習TSM必須理解的幾個概念