PostgreSQL 9.1或以上版本，提供了真正意義的Serializability Isolation，本節主要介紹了在此隔離級別下禁止的異象。

[pg12@localhost pg122db]$ grep 'isolation' postgresql.conf
default_transaction_isolation = 'SERIALIZABLE'
```

### Write觸發
操作序列如下：

|時間點|T1|T2|T3|
| ------ | ------ | ------ | ------ |
|t1|begin;| | |
|t2|select * from idx where id = 1;| | |
|t3| |begin; | | 
|t4| |select * from idx where id = 10000;| | 
|t5| | |begin;| 
|t6| | |update idx set c1 = 'x' where id = 10000;| 
|t7| | |commit;| 
|t8| |update idx set c1 = 'x' where id = 1;| |

session 2（T2）在執行update操作時會報錯
```
-- session 2
15:46:52 [local:/data/run/pg12]:5120 pg12@testdb=#* update idx set c1 = 'x' where id = 1;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during write.
HINT:  The transaction might succeed if retried.
15:47:10 [local:/data/run/pg12]:5120 pg12@testdb=#!

```

### Read觸發
操作序列如下：

|時間點|T1|T2|T3|
| ------ | ------ | ------ | ------ |
|t1|begin;| | |
|t2|select * from idx where id = 1;| | |
|t3| |begin; | | 
|t4| |update idx set c1 = 'x1' where id = 1;| | 
|t5| | |begin;| 
|t6| | |update idx set c1 = 'x' where id = 10000;| 
|t7| | |commit;| 
|t8| |select * from idx where id = 10000;| |

session 2（T2）在執行select操作時會報錯
```
15:54:41 [local:/data/run/pg12]:5120 pg12@testdb=#* select * from idx where id = 10000;
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on conflict out to pivot 423284, during read.
HINT:  The transaction might succeed if retried.
15:55:16 [local:/data/run/pg12]:5120 pg12@testdb=#!
```

### commit觸發
操作序列如下：

|時間點|T1|T2|
| ------ | ------ | ------ |
|t1|begin;| |
|t2|select * from idx where id = 1;| |
|t3|update tbl set c1 = 'x' where id = 10000;| |
|t4| |begin;|
|t5| |select * from idx where id = 10000;|
|t6| |update idx set c1 = 'x' where id = 1;|
|t7| |commit;|
|t8|commit;| |

T1執行commit的時候會報錯：
```
16:07:50 [local:/data/run/pg12]:5120 pg12@testdb=#* 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.

```

**為何會觸發報錯，下節再介紹**

PostgreSQL DBA(178) - Serializability Isolation（禁止的異象）

相關文章