开发者学堂课程【PostgreSQL 快速入门：PostgreSQL 事务隔离级别的实现和多版本并发控制】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/77
PostgreSQL 事务隔离级别的实现和多版本并发控制
内容介绍：
一、PostgreSQL 多版本并发控制
二、事务隔离级别
三、事务隔离级别测试
接下来要讲事务处理与并发控制，主要分为：
事务隔离级别,并发控制,锁的介绍,死锁的发现和处理,实际应用中如何避免死锁.
一、PostgreSQL 多版本并发控制
1.PostgreSQL 的多版本并发控制
版本识别演示.(INSERT,UPDATE, DELETE,使用 ctid 定位,并查看该 TUPLE xmin,xmax 的变化)
多版本并发控制，PostgreSQL 它相当于是一条记录，可能存在多个不同的版本来实现这个并行的对一条记录有操作的这种情况，比如说读写是没有冲突的，我们在对一条记录去读取它这条记录的时候，其他的事物在更新这条记录是不会有影响的。
然后写和写之间，是有一些是有冲突，有一些是没冲突，打比方说更新的这一条记录，更新它的时候，相当于是会生成一个新的版本，然后老的那条记录，相当于是它的头部会有两个标记行的的头部，一个是 xmin 一个是 xmax，这是最重要的。
2.关键词
XID --数据库的事务 ID
Tuple head: xmin, xmax,行头部的 XID 信息, xmin 表示插入这条记录的事务 XID, xmax 表示删除这条记录的事务 XID
Xid_snapshot:当前集群中的未结束事务.
Clog :事务提交状态日志.
我们来看一下这些关键词，XID 是数据库的事物的 ID，它是一个八个字节的一个数据类型，也就64位的一个类型，然后它在数据库里面是唯一的，就是一个持续增长的 ID，也就是说每生成一个新的事物，我会去申请一个 XID，然后这个 XID 是用来分辨这个事物当中做了哪些操作，就像这里的 xmin，xmax。
Xmin 值指的是这一条记录第一次插入的时候，就是第一次生成的时候，生成这条记录的它的事物 ID 是这个 XID，比如说当前这个数号是一千万，然后插一条记录，那么这条记录 xmin 就是一千万，然后 xmax 指的是当这条记录被后面其他的事物，就是这条记录已经提交了，后面其它的事物，比如说现在是一千万零一这样一个事务 ID 去更新这条记录，去更新它的时候，这条老的记录 xmax 本来是零的，它会把它改成一千万零一。
就是这条记录，是被另外一条记录标记了，已经有其他的事物对这条记录做了一次更新，然后其他事务ID是一千万零一，然后更新的时候，它还会把这一条记录除了被更新的部分，其他部分都从原来的字段里面取值，把它写入到新的这个表的另外一个当前数据块或者其他数据块里面去，就相当于生成一个新的版本。然后这个新版本的xmin，就又变成了一千万零一。后面可以演示来看一下。
以上是讲的行的版本，行上面其实还有个 no ID，就类似于 Oracle 里面的 no ID，在 Postgre 里面叫 ctid，ctid 是两个部分的信息，一个部分是就当前这个数据对象，比如说一个表，第一个部分是这个表的 pageID，就是这个表比如说这条记录是存在零号 page还是一号 page 还是1000号 page，每个配置是我们在编译数据库软件的时候指定的。
比如说默认是8K一个page，那么8K里面是零号页，比如说是零，然后存在里面的第几条记录就第几个 itmID，比如说存在十号 itmID，那么 ctid 就是零到20，我们后面也可以来看一下。
xid_snapshot 指的是当前集行当中未结束的事物，比如说当前集群里面并行的正在处理的还有几个事物没有结束，那可以通过这个 xid_snapshot 函数可以去看到。
clog 指的是一个 postgre 的事务的提交状态，这个在数据文件里面可以看到，比如说：pg_clog 指的就是提交日志。
3.数据可见性条件:
①记录的头部 XID 信息比当前事务更早. (repeatable read或ssi有这个要求, read committed 没有这个要求)
②记录的头部 XID 信息不在当前的 XID_snapshot 中．(即记录上的事务状态不是未提交的状态.)
③记录头部的 XID 信息在 CLOG 中应该显示为已提交.
记录头部的 XID 信息比当前事要更早，因为 XID 是按顺序分配的，也就是说比如我请求一个事务，那现在有个事务ID，那么后面再请求的事务它的事务 ID肯定比这个大。
那后面插入的记录，这个是可能看不到，比如说 repeatable read 这样一个隔离级别，或者是 ssi，就是串行的隔离级别，这两个隔离级别肯定是看不到 XID 比我更大的记录，也就是说未来的事物它所产生的数据对于 repeatable read 隔离级别以及 ssi 这种隔离级别，是看不到未来插入的记录。
但对于 read committed 的，它是可以看到未来，比如说未来插入的那条记录并且提交了，那么即使当前这个事务没有结束，也能看到未来的那条事务对数据库发生的一些变更处理。
记录的头部 XID 信息不在当前的 XID_snapshot 中，也就是说对这条记录所操作的这个事物，它已经提交了。
它不在这个 XID_snapshot 中就表示已经提交了，如果在这个 snapshot 中表示这个事务还没有提交，也就是说postgre 是没有 read uncommitted 这样一个事务隔离级别，就是说未提交的事物对数据库产生的一些变更所带来的影响是看不到的。
记录头部的 XID 信息，在 clog 当中应该显示已提交的状态。XID，比如说去检索一条记录，看到它的 xmin 或者 xmax。这个在 clog 当中显示未提交，那这条记录没提交是看不到的，即使检索到了这条记录，然后在 output 给客户端时，也是不会输出的。这个可以通过后面的例子进行验证。
4.更新和删除数据时,并不是直接删除行的数据,而是更新行的头部信息中的 xmax 和 infomask 掩码.
在源代码文件中可以看到关于上一行文字的一些详细解释，源代码如下：src/include/access/htup_details.h
Infomask 指的是一条记录的头部信息，HeapTupleHeaderData 是这样一个数据结构，里面包含两个部分，infomask和 infomask2，这两部分实际存储的是 bits 的 flag
存储在 t_infomask 里的内容如下图：
比如 HEAP_HASNULL，这条记录只是一条突破记录，它是不是有空值？如果有空值，那这个比特位就是等于一，这个比特位就有。然后是不是有那个变长的字段类型，有就加二，就是这个 Frank 再加二。还有比如说事物有没有提交，也在这里面可以看到，或者说它有没有锁，是不是被锁住了这种都可以看到，然后还有就是后面我们讲到的heap_update 这种的话，也是要去根据行的标记去最终的查看。
5.事务提交后更新当前数据库集群的事务状态和 pg_clog 中的事务提交状态.
例子:
会话1:
digoal=# truncate iso_test ;
TRUNCATE TABLE
digoal=# insert into iso_test values ( 1,'test');
INSERT 0 1
digoal-# begin;
BEGIN
digoal=t# update iso_test set info='new' where id=1;
UPDATE 1
会话2∶
digoal-# select ctid,xmin,xmax,* from iso_test where id=1;
ctid | xmin | xmax | id | info
----------+----+------
(0,1)|36732572|316732573 | 1 | test
(1 row)
创建上述例子中的表，插入数据集
digoal- d iso_test
Did not f ind any relation naned "iso_test"-
digoal-f create table iso_test <id int , info text> ;
CREATE TABLE
digoal-insert into iso_test values <1 , 'test’ > ;
INSERT 0 1
digoal-t select ctid，*from iso_test ;
ctid : id : info
----+--一+------
＜0,1＞:1 : test
<1 rows>
＜0,1＞表示这条数据在零号数据库的第一条记录，如果再往里面插入数据：
ligoal-ltinsertinto iso_test ua lues＜1,'test'>;
NSERT 0 1
ligoal-#t se lect ctid ,* from iso _test ;
ctid:id : info
----+--一+------
＜0,1＞:1: test
＜0,2＞:1:test
<2 rows>
两条数据都是在零号数据块中，但是用1和2区分，1和2就是 itmID，我们可以通过 pageinspect 插件来查看一些配置信息
后面可以绘制插件 create extension pageinspect，会出现 pageinspect 的一些函数，可以看到零号数据块中有哪些信息。
执行以下代码：
digoal-# begin;
BEGIN
digoal=t# update iso_test set info='new' where id=1;
UPDATE 1
这样相当于更改了两条记录,之后执行会话2内容查看 xmin 和 xmax
digoal-# select ctid ,xnin , xnax, * fron iso_test where id=1;
ctid:xrkn!xnax: id: info
<0,1>:324246813: 324246816:1: test
<0,2> :324246814 :324246816:1: test
<2 rows>
我们可以看到：两条记录的 xmin 是两次事务插入的两条记录，当前正在更新的事务 id 是324246816，相当于上述两条老的记录已经变为324246816。
因为已经发生变更，进行查看：
digoal- select ctid,xnin , xnax,* fron iso_test where id-i;
ctid : xnin : xnax:id:info
<0,1>:324246816: 0:1: new
<0,2> :324246816:0:1: new
<2 rows>
可以看到，以上是新插入的两条记录，xmax变为0，xmin 就是当前的事务 ID，但是在其他会话查看时还是原来内容，因为还未提交。
所以说，PostgreSQL 多版本并发控制不需要 UNDO 表空间，Oracle 会将老的记录放入回本段里，在 postgre 没有这个概念，是直接把老记录放入 xmax，当然还有一些其他的头部信息变更，这里没有列举出来，就是简单来说 xmax已经变了。
对于其他会话可以通过一个函数 snapshot 就可以看到当前还在活跃的事务，它查到的都是老记录，因为还未提交；只要已提交，查看到的就是已经变更的数据，之前的数据就查不到了，查看，当前事务状态就变成324246817.
接下来讲解下列例子：
RR1 tuple-v1 IDLE IN TRANSACTION;
RC1 tuple-v1 IDLE IN TRANSACTION;
RC2 tuple-v1 UPDATE-> tuple-v2 COMMIT;
RR1 tuple-v1 IDLE IN TRANSACTION;
RC1 tuple-v2 IDLE IN TRANSACTION;
RR2 tuple-v2 IDLE IN TRANSACTION;
RC3 tuple-v2 UPDATE-> tuple-v3 COMMIT;
RR1 tuple-v1 IDLE IN TRANSACTION;
RR2 tuple-v2IDLE IN TRANSACTION;
RC1 tuple-v3 IDLE IN TRANSACTION;
RR——repeatable read 可重复读，也可以消除幻像读以及不可重复读事务的影响
RC——read committed
假如将第一个设置成 RR，当前是 idle 状态，就是事务不关闭，在第二个会话中设一个 RC1事务，在第三个会话中设一个 RC2事务并且更新一条记录且提交。
对于 RR，它读到的还是老版本的信息，但是对于 RC1，它读到的是第二个版本的信息，就是更改后的信息。这就与事务隔离级别有关系，对于这种可重复读的事务隔离级别，即使变更成新的信息，读到的还是旧记录，但是对于read committed 这种事务，后面的变更是可以看到的。
接下来再设置一个 RR2，这时看到的是 v2版本，因为是后设置的，故对前面的变更都能看到。再设一个 RC3并且将v2更新到v3，对于 RR1，看到的还是最初的版本，但是对于 RR2就是刚刚发起的这样一个事务，看到的其实还是第二个版本的记录，也就是说对于这种不断的对一条记录去做处理、更新，其实是会产生很多个版本。
比如在这个例子当中，第一条记录其实已经有三个版本，它就是通过这种多版本来实现各种事物隔离级别的这样一个状态。
二、事务隔离级别
1.脏读
在一个事务中可以读到其他未提交的事务产生或变更的数据。
就说如果现在有个a事务，另外有一个B事务，B事务正在做更新并且没有提交，那么对于那种在B事务已经更新的数据，这个a事务能看到就叫脏读，就说即使没有提交，也能看到。
PostgreSQL 不支持 read uncommitted 事务隔离级别,无法测试.
Postgre 是不会产生脏读，不管用什么事物隔离级别都不会产生脏读，因为 postgre 根本就不支持 read uncommitted 的这种事物隔离级别。
2.不可重复读
在一个事务中,再次读取前面 SQL 读过的数据时，可能出现读取到的数据和前面读取到的不一致的现象.(例如其他事务在此期间已提交的数据)
使用 read committed 事务隔离级别测试
对于 repeatable read 这种隔离级别是不会产生不可重复读，不管如何更新，它读到的都是最初版本的记录。
3.幻像读
在一个事务中,再次执行同样的SQL,得到的结果可能不一致.
比如说，select * from t where id=1，如果后面又插入一条相同的数据或者删掉 id=1的记录可能读出的结果不一样。
标准 SQL 事务隔离级别,(PostgreSQL的repeatable read隔离级别不会产生幻像读)
PostgreSQL 不支持 read uncommitted 隔离级别.
Table 13-1.Standard sQL Transaction Isolation Levels
Isolation Level |
Dirty Read |
Nonrepeatable Read |
Phantom Read |
Read uncommitted |
possible |
possible |
possible |
Read committed |
Not possible |
possible |
possible |
Repeatable read |
Not possible |
Not possible |
possible |
serializable |
Not possible |
Not possible |
Not possible |
注意：
在 postgre 里，repeated red 不会产生幻像读，它跟 SQL 标准里的串行隔离级别是一样的，但在 oracle 里面，repeated read 是会产生幻像读。
三、事务隔离级别测试
1.不可重复读测试
digoal=# crcate table iso_test(id int, info text);
digoal=# insert into iso_test valucs (1, 'test');
digoal-# begin isolation level rehd committed;
BEGIN
digoal-tt select * from iso_test where id=1;
id | info
----十------
1 | test
(1 row)
--其他会话更新这份数据,并提交.
digoal-# update iso_test set info='new' where id=1;
--不可重复读出现.
digoal-# sclect * from iso_test where id=1;
id | info
----十------
1 | new
(1 row)
2.幻像读测试
digoal=# begin isolation level read committed;
digoal-# select * from iso_test;
id | info
----十------
1 | new
(1 row)
--其他会话新增数据
digoal=# insert into isq_test values (2, 'test');
--幻象读出现
digoal=# select * from iso_test;
id | info
----+------
1 | new
2 | test
(2 rows)
3.使用 repeatable read 可避免不可重复读和幻像读.
在 postgre 里面可以避免不可重复读和幻像读，但是在 Oracle 里面只能避免不可重复读，不能避免幻像读。这跟实现机制有关。
digoal=# delete from iso_test;
digoal-# insert into iso_test values (1 , 'test');
digoal=# begin isolation level repeatable read;
digoal=# select * from iso_test where id=1;
id | info
----+------
1 | test
(1 row)
--其他会话修改数据,并提交
digoal=#t update iso_test set info='new' where id=1;
--未出现不可重复读现象.
digoal=# select * from iso_test where id=1;
id | info
----+------
1 | test
(1 row)
--其他会话新增数据.
digoal-# inscrt into iso_test values (2, 'test');
INSERT0 1
--未出现幻像读
digoal=# select * from iso_test ;
id | info
----十------
1 | test
(1 row)
即使将整个表删除还是能查到记录，repeatable read 可以实现这种情况，就是无论怎样操作，都能读到启动时的版本。
4. PostgreSQL repeatable read 情景案例
当 repeatable read 的事务去更新或删除在事务过程中被其他事务已经变更过的数据时，将报错等待回滚.
digoal-# truncate iso_ _test;
digoal-# insert into iso_ test values (I,test);
digoal=# begin isolation level repecatable read;
digoal=# select * from iso_ test ;
id | info
----十------
1|test
(1 row)
-其他事务更新或者删除这条记录,并提交.
digoal=# update iso_ _test set info *'new' where id=1;
UPDATE 1
-在repeatable read的事务中更新或者删除这条记录.会报错回滚
digoal =# update iso_ test set info- 'tt where id=1;
ERROR: could not serialize access due to concurrent update
digoal=# rollback;
ROLLBACK
repeatable read 起到一种保护作用，虽然可以读到老版本，但是不能更新它，这个老版本只有在其他会话没有更新时才能去更新。但是对于 read committed 隔离级别是可以去更新的，因为它已经看到更新完成的新版本，所以新版本再被更新是允许的。
接下来继续进行测试：
先获取锁,再处理行上的数据(例如做条件判断.)
所以会有这种现象.
--会话1
digoal=-# truncate iso_test ;
TRUNCATE TABLE
digoal=# insert into iso_test values (1,'test');
INSERT 0 1
digoal=# begin;
注：begin默认启动的是read committed
BEGIN
digoal=# update iso_test set id=id+1 returning id;
id
----
2
(1 row)
UPDATE 1
--会话2
digoal=# select * from iso_test ;
id | info
----+-----
1 | test
(1 row)
digoal=# delete from iso_test where id=1;
--等待ctid=(0,1)的行exclusive锁
--会话1,提交事务
digoal-# cnd;
COMMIT
--会话2,此时会话2等待的这条 ctid(0,1)已经被会话1删除了(如果会话2是 repeatable read 模式的话这里会报错).
注：因为这里要更新的记录已经被其他会话给更改了，对于 repeatable read 隔壁级别来说是不允许再进行更新的，那就会报错。
DELETE 0
digoal=# select * from iso_test;
id | info
----十------
2 | test
(1 row)
5. Serializable 隔离级别
目标是模拟 serializable 的隔离级别事务的提交顺序转换为串行的执行顺序.
例如:
Start session a serializable
Start session b scrializable
Session a SQL ...
Session b SQL ...
Session a|b sQL .......
Session ab sQL ...
Commit b
Commit a
注：提交顺序先后没有意义，只是模拟时成先提交的在前面。
这个场景模拟成︰
Start session b
Sql ...
Commit b
Start session a
sql ...如果会话a读过的数据在B中被变更,那么a会话将提交失败.
Commit a
如果a里面的数据跟b没有一点关系也可以提交成功。
PostgreSQL 串行事务隔离级别的实现,通过对扫描过的数据加载预锁来实现(内存中的一种弱冲突锁,只在事务结束时判断是否有数据依赖性的冲突)
就是说在串行的数据结构中，如果去执行 SQL 语句，是会对扫描过的数据块预加一个锁，这个锁是在内存当中的一种弱锁，是放在内存中的，只在事务结束时判断是否有数据依赖性的冲突。
因为涉及到扫描的数据,所以这种锁和执行计划有关.
为什么和执行计划有关：比如说一个 SQL 语句是走索引扫描的，就相当于索引的 passage 要加预锁，同时索引最终会扫描到 heap passage，对应的 heap passage 也会加锁。如果是全表扫描，就相当于整个表要加预锁，在判断时，只要有一个数据发生变更整个表都不能提交。
例如：Select* from tbl where a=1;
如果没有索引,那么是全表扫描,需要扫描所有的数据块.
加载的预锁是表级别的预锁.(那么期间如果其他串行事务对这个表有任何变更,包括插入.删除,更新等.并且先提交的话.)
这个会话结束的时候会发现预加锁的数据被其他串行事务变更了,所以会提交失败.
如果a上有索引的话,执行计划走索引的情况下,扫描的数据包括行和索引页.
那么加载的预锁包含行和索引页.
这种情况仅当其他串行事务在此期间变更了相对应的行或者是索引页才会在结束时发生冲突.
下面举个例子：
会话A：
digoal-# sclect pg_backend_pido;
-[ RECORD 1 ]--+------
pg_backend_pid | 12186
会话B:
digoal=# select pg_backend_pid();
-[ RECORD 1 ]--+------
Pg_backend pid | 12222
会话A:
digoal=-# truncate iso_test ;
TRUNCATE TABLE
digoal-# inscrt into iso_test select
gencrate_series(1,100000);
INSERT 0 100000
digoal-# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal-# sclect sum(id) from iso_test whcre id=100;
-[ RECORD 1 ]
sum | 100
会话C:
digoal-# select relation:regclass,* from pg_locks where pid
in (12186,12222):
relation / locktype | database | relation / page / tuple |
virtualxid | transactionid | classid | objid | objsubid |
virtualtrans
action / pid |model granted | fastpath
……..+.….…+...…+......+…...+...….+……...
……..+.….…+...…+......+…...+...….+……...
iso_test | relation |16384| 92992| | | | | 1/157993
12186 | AccessShareLock | tl t
| virtualxid | | 1/157993 | | | |1/157993|
|12186 | ExclusiveLock |tlt
iso_test | relation | 16384| 92992| | | | 1/157993
|12186 | SIReadLock |t |f
(3 rows)
由 pid 的值可知当前只有会话1对 有锁, SIReadLock 直接就把所有 relation 锁住，因为它是全面扫描，对于上面两行是普通的锁。根据执行计划，执行计划是全面扫描，所以就锁住整个 relation，如果是索引扫描，这里是锁索引以及对应的行。
会话B:
digoal-# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=10;
-[ RECORD 1 ]
digoal=# select sum(id) from iso_test where id=10;-
[ RECORD 1 ]
会话C：
digoal=# select relation:regclass,* from pg_locks where pid
in (12186,12222);
relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid | objid | objsubid |l
virtualtransaction |pid |mode l granted | fastpath
……..+.….…+...…+......+…...+...….+……...
……..+.….…+...…+......+…...+...….+……...
iso_test | relation | 16384| 92992| | 1/157993
|12186 | AccessShareLock | tlt
|virtualxid | | 1/157993 | | 1/157993
|12186 | ExclusiveLock |tlt
iso_test | relation | 16384| 92992| |2/6433312
|12222 | AccessShareLock | t l t
|virtualxid | |2/6433312| |2/6433312
|12222 | ExclusiveLock |tlt
iso_test | relation | 16384|92992| | 1/157993
|12186 | SIReadLock ltl f
iso_test | relation | 16384| 92992| | |2/6433312
|12222 | SIRcadLockltl f
(6 rows)
这时会发现，pid两个值都加上了三个锁，就相当于两个事务发生冲突，在提交时会检测。
会话A:
digoal=-# ilnsert into iso_test values ( 1,'test');
INSERT 0 1
会话B:
digoal-# insert into iso_test values (2,'test');
INSERT 0 1
会话C:
digoal-# select relation::regclass,* from pg_locks where
pid in (12186,12222);
relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid | objid | objsubid |l
virtualtransaction |pid |mode l granted | fastpath
……..+.….…+...…+......+…...+...….+……...
……..+.….…+...…+......+…...+...….+……...
iso_test | relation | 16384| 92992| | 1/157993
|12186 | AccessShareLock | tlt
iso_test | relationl 16384| 92992I | 1/157993
| 12186 | RowExclusiveLock |tlt
|virtualxid | | 1/157993 | | 1/157993
|12186 | ExclusiLaLock |tlt
iso_test | relation| 16384| 92992| |2/6433312
|12222 | AccessShareLock |tlt
iso_test | relation| 16384| 92992| |2/6433312
| 12222 |RowExclusiveLock | tlt
| virtualxid |2/6433312 | |2/6433312
|12222 | ExclusiveLock |tlt
|transactionid |316732564| | 1/157993
| 12186 | ExclusiveIock |tl f
| transactionid | |316732565| |2/6433312
|12222 | ExclusiveLock |tl f
iso_test | relation | 16384| 92992| | 1/157993
|12186 | SIRcadLock ltl f
iso_test | relation | 16384| 929921| |2/6433312
|12222 | SIReadLock ltl f
(10 rows)
除了 SIReadLock 外，还多了 ExclusiveLock 以及
RowExclusiveLock。
之后提交会话A
会话A:
digoal=# commit;
COMMIT
会话C:
digoal-# select relation::regclass,* from pg_locks where
pid in (12186,12222);
relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid | objid | objsubid |l
virtualtransaction |pid |mode l granted | fastpath
……..+.….…+...…+......+…...+...….+……...
……..+.….…+...…+......+…...+...….+……...
iso_test | relation| 16384| 92992| |2/6433312
|12222 | AccessShareLock |tlt
iso_test | relation| 16384| 92992| |2/6433312
| 12222 |RowExclusiveLock | tlt
| virtualxid |2/6433312 | |2/6433312
|12222 | ExclusiveLock |tlt
| transactionid | |316732565| |2/6433312
|12222 | ExclusiveLock |tl f
iso_test | relation | 16384| 92992| | 1/157993
|12186 | SIRcadLock ltl f
iso_test | relation | 16384| 929921| |2/6433312
|12222 | SIReadLock ltl f
(6 rows)
会话A已经提交，但是还保留了 SIReadLock，这个锁是对整个 relation 的。
会话B:
digoal=# commit;
ERROR: could not serialize access due to read/write
dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot,
during commit attcmpt.
HINT: The transaction might succeed if retried.
提交会话B会出现报错，因为已经提交了会话A且整个 relation 被锁，数据已经发生变更，后续如果再提交就会产生冲突报错，只能进行回滚。
会话C:
digoal-# select relation::regclass,* from pg_locks where
pid in (12186,12222);
relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid | objid | objsubid |
virtualtransaction | pid | mode | granted | fastpath
……..+.….…+...…+......+…...+...….+……...+ ……..+.….…+...…
+......+…...+...….+……...
(0 rows)
锁已经不存在，因为是落锁，就是在提交时才会去检测的 SIReadLock。
6.同样的场景,加索引测试:
会话A：
digoal-# create index idx_iso_test_1 on iso_test (id);
CREATE INDEX
digoal=#f begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=100;
-[ RECORD 1 ]
sum | 100
会话C:
digoal=# select relation::regclass,* from pg_locks where
pid in (12186,12222);
relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid objid | objsubid |
virtualtransaction | pid |model granted | fastpath
……..+.….…+...…+......+…...+...….+……...+ ……..+.….…+...…
+......+…...+...….+……...
idx_iso_test_1 | relation |16384| 93017| | 1/157996
|12186 | AccessShereLock | tlt
iso_test | relation | 16384| 92992| | |1/157996
|12186 [ AccessShareLock | tlt
|virtualxid | | 1/157996 | | | | | 1/157996
|12186 [ ExclusiveLock |tlt
iso_test|tuple| 16384| 92992| 0| 100| | |1/157996
|12186 | SIRcadLock ltl
idx_iso_test_1 |page| 16384| 93017[ 1| | |1/157996
|12186 | SIRcadLock|tl f
(5 rows)
发现：
SIRcadLock 变了，一个是行锁，一个是 page 锁，page 锁锁的是索引，锁的 page 的 id 是一号页面。对于 tuple，锁的是零号数据块的第一百条记录，我们可以看到这要记录如下图。也就是说现在不是直接锁整个 relation，而是锁索引的 page 以及 iso_test 的其中一条记录。
在会话B中查询另一条记录
digoal-# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal-# sclect sum(id) from iso_test where id=10;
-[ RECORD 1 ]
sum | 10
会话C:
digoal=# select relation:regclass,* from pg_locks where pid
in (12186,12222);relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid objid | objsubid |
virtualtransaction | pid |model granted | fastpath
……..+.….…+...…+......+…...+...….+……...+ ……..+.….…+...…
+......+…...+...….+……...
idx_iso_test_1 | relation |16384| 93017| | 1/157996
|12186 | AccessShereLock | tlt
iso_test | relation | 16384| 92992| | |1/157996
|12186 [ AccessShareLock | tlt
|virtualxid | | 1/157996 | | | | | 1/157996
|12186 [ ExclusiveLock |tlt
idx_iso_test_1 | relation |16384| 93017| |2/6433314
|12222 | AccessShareLock | t | t
iso_test | relation|16384| 92992| | |2/6433314
|12222 | AccessShareLock | tl t
|virtualxid |2/6433314 | | | |2/6433314
|12222 | ExclusiveLock | tl t
iso_test | tuple| 16384| 92992| 0| 100| | | | |1/157996
|12186 | SIRcadLock | t l f
iso_test| tuple| 16384| 92992| 0| 10 | | | |2/6433314
|12222 | SIReadLock | tl f
idx_iso_test_1 / pagel 16384| 93017| 1 | | | |1/157996
|12186 | SIReadLock l t| f
idx_iso_test_1 | page| 16384| 93017| 1| | | |2/6433314
|12222 | SIReadLock l t l f
(10 rows)
对于 pid 为12222的会话B中的 SIreadLock 也是一个锁索引，一个锁 tuple，索引上锁的是同一个块，对于会话A锁的也是一号数据块，会话B锁的也是一号数据块。虽然 tuple1不一样，但是锁了索引页面，所以后面还会发生冲突。
比如说对会话A再插入一条信息：
digoal-# insert into iso_test values (1,'test');
INSERT 0 1
会话C：
digoal=-# sclect relation:regclass,* from pg_locks whcre
pid in (12186,12222);relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid objid | objsubid |
virtualtransaction | pid |model granted | fastpath
……..+.….…+...…+......+…...+...….+……...+ ……..+.….…+...…
+......+…...+...….+……...
idx_iso_test_1 | relation |16384| 93017| | 1/157996
|12186 | AccessShereLock | tlt
iso_test | relation | 16384| 92992| | |1/157996
|12186 [ AccessShareLock | tlt
iso_test | relation | 16384| 92992| | | |1/157996
|12186 | RowExclusiveLock | tl t
|virtualxid | | 1/157996 | | | | | 1/157996
|12186 [ ExclusiveLock |tlt
idx_iso_test_1 | relation |16384| 93017| |2/6433314
|12222 | AccessShareLock | t | t
iso_test | relation|16384| 92992| | |2/6433314
|12222 | AccessShareLock | tl t
|virtualxid |2/6433314 | | | |2/6433314
|12222 | ExclusiveLock | tl t
iso_test | tuple| 16384| 92992| 0| 100| | | | |1/157996
|12186 | SIRcadLock | t l f
iso_test| tuple| 16384| 92992| 0| 10 | | | |2/6433314
|12222 | SIReadLock | tl f
idx_iso_test_1 / pagel 16384| 93017| 1 | | | |1/157996
|12186 | SIReadLock l t| f
idx_iso_test_1 | page| 16384| 93017| 1| | | |2/6433314
|12222 | SIReadLock l t l f
(12 rows)
会话B插入一条记录：
digoal-# insert into iso_test values (2,'test');
INSERT 0 1
digoal=# select relation:regclass,* from pg_locks where
pid in (12186,12222);relation | locktype | database | relation | page | tuple |
virtualxid | transactionid | classid objid | objsubid |
virtualtransaction | pid |model granted | fastpath
……..+.….…+...…+......+…...+...….+……...+ ……..+.….…+...…
+......+…...+...….+……...
idx_iso_test_1 | relation |16384| 93017| | 1/157996
|12186 | AccessShereLock | tlt
iso_test | relation | 16384| 92992| | |1/157996
|12186 [ AccessShareLock | tlt
iso_test | relation | 16384| 92992| | | |1/157996
|12186 | RowExclusiveLock | tl t
|virtualxid | | 1/157996 | | | | | 1/157996
|12186 [ ExclusiveLock |tlt
idx_iso_test_1 | relation |16384| 93017| |2/6433314
|12222 | AccessShareLock | t | t
iso_test | relation|16384| 92992| | |2/6433314
|12222 | AccessShareLock | tl t
iso_test | relation | 16384| 92992| | | |2/6433314
|12222 | RowExclusiveLock | tlt
|virtualxid |2/6433314 | | | |2/6433314
|12222 | ExclusiveLock | tl t
|transactionid| | | |316732567| | |1/157996
|12186 | ExclusiveLock |tl f
|transactionid| | | |316732567| | |2/6433314
|12222 | ExclusiveLock |tl f
iso_test | tuple| 16384| 92992| 0| 100| | | | |1/157996
|12186 | SIRcadLock | t l f
iso_test| tuple| 16384| 92992| 0| 10 | | | |2/6433314
|12222 | SIReadLock | tl f
idx_iso_test_1 / pagel 16384| 93017| 1 | | | |1/157996
|12186 | SIReadLock l t| f
idx_iso_test_1 | page| 16384| 93017| 1| | | |2/6433314
|12222 | SIReadLock l t l f
(14 rows)
此时 idx_iso_test_1锁了一号页面，刚刚插进去的记录也是在一号页面，所以后面会发生一些冲突。
我们可以看到刚刚插入的两条记录都是在 idx 里，都是在一号数据块里，所以会产生问题。
在进行提交时，还是会发生冲突。
会话A:
digoal=# cdmmit;
COMMIT
会话B：
digoal=-t commit;
ERROR: could not serialize access due to read/write
dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot,
during commit attempt.
HINT:The transaction might succeed if retried.
索引页用了同一个,并且被插入语句更新了.预加锁的数据已经被另一事务变更新过了，所以发生了冲突。
如果其中一个插入的值不在1号索引页则没有问题,例如
会话A:
digoal-# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=t select sum(id) from iso_test where id=100;
-[ RECORD 1 ]
sum | 100
digoal=# insert into iso_test values (1,'test');
INSERT 0 1
digoal-# commit;
COMMIT
会话B：
digoal-# begin ISOLATION LEVEL SERIALIZABLE;
BEGIN
digoal=# select sum(id) from iso_test where id=10;
-[ RECORD 1 ]
sum | 10
digoal-# insert into iso_test values (200000,'test');
INSERT 0 1
digoal-# commit;
COMMIT
插入成功的原因是插入的记录不在一号 page 上，没有影响到所加载数据的预锁，就不会发生冲突，没有冲突就可以成功提交。
(200000,'test')这个索引页不在1号数据块中
idx_iso_test_1 | page | 16384| 93017| 275| |2/6433316
|12222 I SIReadLockl t | f
(200000,'test')这个页在什么地方可以通过 SQL 语句查询，会话B会对这个索引页再加一个锁，可以看到这个页在275页（如下图），就是在索引上面锁的是275页，也就是这里变更的数据是275页的数据。
然后 select select sum(id) from iso_test where id=1;
它没有新的 page 索引，所以还是在一号索引页上，也就是说一个变更了一号索引页，另一个是变更了275索引页。
之后进行提交，无论先提交谁，事务都能提交。因为未发生冲突，没有相互依赖性的数据变更的冲突，所以是可以提交的。
注意事项：
PostgreSQL 的 hot_standby 节点不支持串行事务隔离级别,只能支持 read committed 和 repeatable read 隔离级别.
PostgreSQL 只在可读写的节点上支持串行事务隔离级别。
在 SQL 标准里面，hot_standby 节点可以实现串行事务隔离级别，在可读写的节点真正实现了串行隔离级别，它是依赖于执行计划的，就是执行计划带来的数据扫描，比如对整个表加了预锁，在表上发生一些变更，一些依赖发生冲突就会在事务结束时检测到 SIreadLock。对于走索引扫描的，一个是锁索引页，另一个是锁 relation tuple，这种相当于是它为了避免内存溢出采用这种方式，因为这种锁是放在内存中的。
如果锁的行比较多，可能会升级成快锁，升级成快锁，锁的冲突概率会变高，但是可以减少内存溢出的可能。也就是说 SIreadLock 是一种放在内存中的锁，它的锁跟执行计划有关。
