隔离级别

ANSI SQL标准隔离级别

ANSI SQL标准通过现象（Phenomena）定义（除此之外还可以通过锁、数据流图定义），下面首先使用形式化语言来定义以下几种现象：

脏写 P0 – （Dirty Write）

脏读 P1 – Dirty Read

不可重复读 P2 – Fuzzy or Non-Repeatable Read

幻读P3 – Phantom

ANSI SQL标准通过禁止上述现象的形式定义了四个隔离级别，分别是读未提交、读已提交、可重复读和可串行化：

隔离级别	P0 Dirty Write	P1 Dirty Read	P2 Fuzzy Read	P3 Phantom
Read Uncommitted	Not Possible	Possible	Possible	Possible
Read Committed	Not Possible	Not Possible	Possible	Possible
Repeatable Read	Not Possible	Not Possible	Not Possible	Possible
Serializable	Not Possible	Not Possible	Not Possible	Not Possible

读未提交：禁止脏写

读已提交：禁止脏读和脏写

可重复读：除幻读外，禁止其他现象

可串行化：禁止所有并发现象，效果如同事务以串行的方式执行

PostgreSQL隔离级别

PostgreSQ L 与ANSI SQL标准相应，也定义了四种隔离级别。但由于使用了快照读 SI(Snapshot Isolation) ，RU 和 RC 实际上在 PostgreSQL 中没有任何区别。

并且得益于快照读， RR 级别下也没有幻读问题。RR 级别下连 \d 命令都看不到别的事务对表结构的修改。

但 RR 级别下可能产生阻塞、串行化失败等问题。比如

## course表, (name text, score int), name为唯一键
## 插入主键冲突的数据会导致阻塞

## 事务1
> BEGIN;
> INSERT INTO course(name, score) VALUES ('go',  0);

## 事务2
> INSERT INTO course(name, score) VALUES ('go',  1);
## 事务2会卡住，无法再输入COMMIT命令，等事务1 COMMIT时事务2会报错

## 事务1
> COMMIT;
INSERT 0 1

## 事务2会报错，只能ROLLBACK
ERROR:  duplicate key value violates unique constraint "course_name_idx"
DETAIL:  Key (name)=(math) already exists.
> COMMIT;
ROLLBACK

再比如对同一行数据进行修改，会报串行失败的错：

## 事务1
> BEGIN;
> UPDATE course SET score=score+1 WHERE id=1;

## 事务2
> BEGIN;
> UPDATE course SET score=score+1 WHERE id=1;
ERROR:  could not serialize access due to concurrent update

在 RC 级别下，这种操作则会导致阻塞。这种情况可以用 SELECT FOR UPDATE 语法解决。

# 事务1
BEGIN; 
UPDATE course SET score=1 WHERE id = (SELECT id FROM course WHERE id=1 FOR UPDATE);

# 事务2
BEGIN; 
UPDATE course SET score=1 WHERE id = (SELECT id FROM course WHERE id=1 FOR UPDATE SKIP LOCKED);

其中事务2的 SKIP LOCKED 会因事务1上锁而 SELECT 不出任何数据，直接跳过更新。

因此 PostgreSQL 仍然提供 Serialzable 级别。