PG事务详解

[TOC]

PG事务等级

国际SQL标准事务等级：

PostgreSQL事务等级

1. 可以看到PG中不允许出现脏读，其实最终就三个事务隔离级别：RC、RR、SR；
2. PG默认RC模式；
3. PG不允许在可重复读的情况下产生幻读。
4. 可以手动调整为可序列化SR，但因为SR是序列化的，性能较低一般没人用。

查询事务级别

-- 查询当前事务级别
SELECT name, setting FROM pg_settings WHERE name ='default_transaction_isolation';
-- 设置当前事务为
SELECT current_setting('default_transaction_isolation');
SELECT name, setting FROM pg_settings WHERE name ='transaction_isolation';
SELECT current_setting('transaction_isolation');

常见异常概念

脏读（数目不变、修改+回滚 数据值变化）

脏读主要针对RU事务级别的数据更新Update操作出现的问题

当事务等级是读未提交RU时：

T1: update、rollback

T2: select x 2

1. T1修改了没提交
1. T2读了T1的修改值
1. T1回滚修改
1. T2读到的值就是脏值

sequenceDiagram 
T1->>中间态: update t1 set name = 'lisi' where id = 1 
Note right of T1: 将id=1的name从zhangsan变更成lisi
T2->>中间态: select name from t1 where id = 1 
中间态-->>T1: rollback; 
T2->>中间态: id=1的name可能不等于zhangsan,这时候就是读到了脏数据

不可重复度（数目不变，修改+提交 数据值变化）

不可重复度主要针对RU、RC事务级别下进行数据更新与多次读取之间结果值不同的问题

T1: select x 2

T2: update

1. T1读取
2. T2修改
3. T2提交
4. T1又读了一次，发现两次结果竟然不一样；

sequenceDiagram 
T1->>中间态: select name from t1 where id = 1
Note right of T1: name = zhangsan
T2->>中间态: update t1 set name = 'lisi' where id = 1 
T1->>中间态: select name from t1 where id = 1 
Note right of T1: T1第一次select的name是zhangsan 第二次select的却变成了lisi 
Note right of T1: 明明T1只是做了两次查询 两次却读到了不一样的值

幻读（数目变化， 创建或删除，数据值可能变化）

T1: select x 2

T2: insert delete

T1读取了满足某一搜索条件的数据集合，T2创建或删除了满足该搜索条件的数据项并提交了。

T1再次使用同样的搜索条件查询的时候，发现和第一次查询的结果不同；

sequenceDiagram 
T1->>中间态: select name from t1 where id < 10;
T2->>中间态: delete from t1 where id < 10/insert into t1 values (xxx); 
T1->>中间态: select name from t1 where id < 10;
Note right of T1: T1发现查不到数据了
Note right of T1: 明明T1只是做了两次查询 两次却发生了数据量上的幻觉

ERROR: could not serialize access due to concurrent update

由于并发事务更新，无法序列化访问；它是一个现象并不是一个异常，因为采用了它才能保证数据的准确性和一致性，让人不会产生困扰，如果你做了一些违反它规定和原则的行为，它就会禁止你继续做下去并给你一个提示。

原因是因为系统采用了RR（Repeatable Read - 可重复读）的事务级别，我们知道PG每次查询的数据实际上是取得的一组数据快照。

如果当前有两个事务，同时对同一张表的同一条记录进行更新或者删除操作，我们举个例子：

RC(Read Committed)读已提交 事务模式

读已提交发现出现了不可重复度和幻读的现象

drop table t1;
create table t1(id int, amount int);
insert into t1 values(1,0);
-- Transation - 01
begin;
SELECT name, setting FROM pg_settings WHERE name ='transaction_isolation';
select * from t1;
update t1 set amount = amount+100 where id = 1;
select * from t1;

-- Transation - 02
begin;
SELECT name, setting FROM pg_settings WHERE name ='transaction_isolation';
select * from t1;
update t1 set amount = amount+200 where id = 1;
select * from t1;

-- Transation - 01
commit;
-- Transation - 02
commit;

1. 左侧开启一个事务，并将amount添加100，注意暂未commit;注意金额变成了100；

2. 右侧开启一个新事务，并将amount添加200，也没有commit；注意看右侧事务在等待左侧事务执行完成；

3. 左侧事务执行完成，左侧数据amount=100正确；左侧执行完成，右侧继续执行，我们发现右侧的amount初始值是0，加了200块，却变成了300块，这就会让人很疑惑，产生了”不可重复度”。

4. 右边事务结束；

RR(Repeatable Read )可重复读 事务模式

drop table t1;
create table t1(id int, amount int);
insert into t1 values(1,0);
-- Transation - 01
begin;
--注意修改事务级别时，修改语句前不能有任何SQL语句（起码保证整个事务用的是同一个级别）
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT name, setting FROM pg_settings WHERE name ='transaction_isolation';
select * from t1;
update t1 set amount = amount+100 where id = 1;
select * from t1;

-- Transation - 02
begin;
--注意修改事务级别时，修改语句前不能有任何SQL语句（起码保证整个事务用的是同一个级别）
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT name, setting FROM pg_settings WHERE name ='transaction_isolation';
select * from t1;
update t1 set amount = amount+200 where id = 1;
select * from t1;

-- Transation - 01
commit;
-- Transation - 02
commit;

1. 左边事务开始，事务级别设置为可重复读（其实RR或者RC都行）；amount开始是0，amount更新后变成100；

2. 右侧事务开始，实物级别设置为可重复读；amount开始是0，等待左侧事务结束；

3. 左侧事务结束，右侧事务继续执行，由于这个时候右侧拿到的amount应该是100，与初始拿到的0不符，故数据库认不允许更新；

4. 因为右边不允许更新，所以保证数据结果和左边的保持一致，右边报错。

修改事务级别前不允许有任何SQL过程

begin;
SELECT name, setting FROM pg_settings WHERE name ='default_transaction_isolation';
--注意修改事务级别时，修改语句前不能有任何SQL语句（大白话：起码保证整个事务用的是同一个级别，不能这一行是RR那一行是RC吧）
START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
end;

参考：

​ 1. 并发异常那些事