PostgreSQL 9.1或以上版本，提供了真正意义的Serializability Isolation，本节主要介绍了在此隔离级别下禁止的异象。 ### 测试数据 数据表idx，在id列上有索引，数据库默认的隔离级别为可串行化 ``` 15:44:16 [local:/data/run/pg12]:5120 pg12@testdb=# \d+ idx Table "public.idx" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-------------------+-----------+----------+---------+----------+------- -------+------------- id | integer | | | | plain | | c1 | character varying | | | | extended | | Indexes: "idx_id" btree (id) Access method: heap [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. ``` **为何会触发报错，下节再介绍**