PG 中用tuple上的xmin,xmax 记录产生这一行的txid,与删除这一行的txid,并且用来实现锁机制， exclusive模式的锁只被一个事务持有，可以通过xmax中获取持有该锁的txid.很好理解. shared 模式的锁，是可以被多个事务共同持有的,怎么记录被哪些事务持有呢？
如何使用一个txid标识一组事务？Multixact就是做这个事的，用multixactid标识出一组事务。
引用《PostgreSQL数据库内核分析》中的一段描述
MULTIXACT日志是PostgreSQL系统用来记录组合事务ID的一种日志。由于PostgreSQL采用了多版本并发控制，因此同一个元组相关联的事务ID可能有多个，为了在加锁（行共享锁）的时侯统一操作，
PostgreSQL将与该元组相关联的多个事务ID组合起来用一个MultiXactID代替来管理。
实验验证
--创建测试表
postgres@test > create table t5(id int,name varchar(60));
CREATE TABLE
postgres@test > select * from t5;
id | name
----+--------
1 | test1
2 | test2
3 | test3
4 | test4
5 | test5
6 | test6
7 | test7
8 | test8
9 | test9
10 | test10
(10 rows)
test=#
--创建pageinspect扩展
create extension pageinspect;
test=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------------+---------+------------+-------------------------------------------------------
pageinspect | 1.6 | public | inspect the contents of database pages at a low level
pg_freespacemap | 1.2 | public | examine the free space map (FSM)
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 rows)
postgres@test > select * from heap_page_items(get_raw_page('T5',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------
1 | 8152 | 1 | 34 | 3700709 | 0 | 0 | (0,1) | 2 | 2306 | 24 | NULL | NULL | \x010000000d7465737431
2 | 8112 | 1 | 34 | 3700709 | 0 | 0 | (0,2) | 2 | 2306 | 24 | NULL | NULL | \x020000000d7465737432
3 | 8072 | 1 | 34 | 3700709 | 0 | 0 | (0,3) | 2 | 2306 | 24 | NULL | NULL | \x030000000d7465737433
4 | 8032 | 1 | 34 | 3700709 | 0 | 0 | (0,4) | 2 | 2306 | 24 | NULL | NULL | \x040000000d7465737434
5 | 7992 | 1 | 34 | 3700709 | 0 | 0 | (0,5) | 2 | 2306 | 24 | NULL | NULL | \x050000000d7465737435
6 | 7952 | 1 | 34 | 3700709 | 0 | 0 | (0,6) | 2 | 2306 | 24 | NULL | NULL | \x060000000d7465737436
7 | 7912 | 1 | 34 | 3700709 | 0 | 0 | (0,7) | 2 | 2306 | 24 | NULL | NULL | \x070000000d7465737437
8 | 7872 | 1 | 34 | 3700709 | 0 | 0 | (0,8) | 2 | 2306 | 24 | NULL | NULL | \x080000000d7465737438
9 | 7832 | 1 | 34 | 3700709 | 0 | 0 | (0,9) | 2 | 2306 | 24 | NULL | NULL | \x090000000d7465737439
10 | 7792 | 1 | 35 | 3700709 | 0 | 0 | (0,10) | 2 | 2306 | 24 | NULL | NULL | \x0a0000000f746573743130
(10 rows)
---session 1 获取ID=1记录的shared锁
postgres@test > begin;
BEGIN
postgres@test >
postgres@test > select pg_backend_pid(),txid_current();
pg_backend_pid | txid_current
----------------+--------------
20827 | 3700712
(1 row)
postgres@test >
postgres@test > select id,name from t5 where id=1 for share;
id | name
----+-------
1 | test1
(1 row)
postgres@test > select * from heap_page_items(get_raw_page('T5',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+---------+----------+--------+-------------+------------+--------+--------+-------+--------------------------
1 | 8152 | 1 | 34 | 3700709 | 3700712 | 0 | (0,1) | 2 | 466 | 24 | NULL | NULL | \x010000000d7465737431
2 | 8112 | 1 | 34 | 3700709 | 0 | 0 | (0,2) | 2 | 2306 | 24 | NULL | NULL | \x020000000d7465737432
3 | 8072 | 1 | 34 | 3700709 | 0 | 0 | (0,3) | 2 | 2306 | 24 | NULL | NULL | \x030000000d7465737433
4 | 8032 | 1 | 34 | 3700709 | 0 | 0 | (0,4) | 2 | 2306 | 24 | NULL | NULL | \x040000000d7465737434
5 | 7992 | 1 | 34 | 3700709 | 0 | 0 | (0,5) | 2 | 2306 | 24 | NULL | NULL | \x050000000d7465737435
6 | 7952 | 1 | 34 | 3700709 | 0 | 0 | (0,6) | 2 | 2306 | 24 | NULL | NULL | \x060000000d7465737436
7 | 7912 | 1 | 34 | 3700709 | 0 | 0 | (0,7) | 2 | 2306 | 24 | NULL | NULL | \x070000000d7465737437
8 | 7872 | 1 | 34 | 3700709 | 0 | 0 | (0,8) | 2 | 2306 | 24 | NULL | NULL | \x080000000d7465737438
9 | 7832 | 1 | 34 | 3700709 | 0 | 0 | (0,9) | 2 | 2306 | 24 | NULL | NULL | \x090000000d7465737439
10 | 7792 | 1 | 35 | 3700709 | 0 | 0 | (0,10) | 2 | 2306 | 24 | NULL | NULL | \x0a0000000f746573743130
(10 rows)
---session 2 获取ID=1记录的shared锁
postgres@test > begin;
BEGIN
postgres@test > select pg_backend_pid(),txid_current();
pg_backend_pid | txid_current
----------------+--------------
582 | 3700713
(1 row)
postgres@test > select id,name from t5 where id=1 for share;
id | name
----+-------
1 | test1
(1 row)
postgres@test > select * from heap_page_items(get_raw_page('T5',0));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+---------+--------+----------+--------+-------------+------------+--------+--------+-------+--------------------------
1 | 8152 | 1 | 34 | 3700709 | 1 | 0 | (0,1) | 2 | 4562 | 24 | NULL | NULL | \x010000000d7465737431
2 | 8112 | 1 | 34 | 3700709 | 0 | 0 | (0,2) | 2 | 2306 | 24 | NULL | NULL | \x020000000d7465737432
3 | 8072 | 1 | 34 | 3700709 | 0 | 0 | (0,3) | 2 | 2306 | 24 | NULL | NULL | \x030000000d7465737433
4 | 8032 | 1 | 34 | 3700709 | 0 | 0 | (0,4) | 2 | 2306 | 24 | NULL | NULL | \x040000000d7465737434
5 | 7992 | 1 | 34 | 3700709 | 0 | 0 | (0,5) | 2 | 2306 | 24 | NULL | NULL | \x050000000d7465737435
6 | 7952 | 1 | 34 | 3700709 | 0 | 0 | (0,6) | 2 | 2306 | 24 | NULL | NULL | \x060000000d7465737436
7 | 7912 | 1 | 34 | 3700709 | 0 | 0 | (0,7) | 2 | 2306 | 24 | NULL | NULL | \x070000000d7465737437
8 | 7872 | 1 | 34 | 3700709 | 0 | 0 | (0,8) | 2 | 2306 | 24 | NULL | NULL | \x080000000d7465737438
9 | 7832 | 1 | 34 | 3700709 | 0 | 0 | (0,9) | 2 | 2306 | 24 | NULL | NULL | \x090000000d7465737439
10 | 7792 | 1 | 35 | 3700709 | 0 | 0 | (0,10) | 2 | 2306 | 24 | NULL | NULL | \x0a0000000f746573743130
(10 rows)
multixact日志结构
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
