> For the complete documentation index, see [llms.txt](https://wtifs.gitbook.io/diva-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://wtifs.gitbook.io/diva-notes/db/postgres/ge-li-ji-bie.md).

# 隔离级别

### 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` 级别。