PostgreSQL 9.1或以上版本，提供了真正意义的Serializability Isolation，本节主要介绍了Serializability Isolation下有索引与没有索引的区别。 #### NonIndex 在没有索引的情况下，对relation进行w（写）操作，PG会对整个relation加SIReadLock，因为加锁粒度是Relation级别，因此如果其他session也对这个表进行w操作，那么两个session之间会出现rw依赖循环，其中一个session会被终止。 ``` -- Session 1 [local:/data/run/pg12]:5120 pg12@testdb=# show default_transaction_isolation; default_transaction_isolation ------------------------------- serializable (1 row) [local:/data/run/pg12]:5120 pg12@testdb=# begin; BEGIN [local:/data/run/pg12]:5120 pg12@testdb=#* select * from tbl where id = 1; id | c1 ----+---------------------- 1 | x (1 row) ``` 查询锁信息，在relation上加SIReadLock ``` [local:/data/run/pg12]:5120 pg12@testdb=# select pid,locktype,relation::regclass,page,tuple,transactionid,mode,granted,fastpath from pg_locks where pid = 22365; pid | locktype | relation | page | tuple | transactionid | mode | granted | fastpath -------+------------+----------+------+-------+---------------+-----------------+---------+---------- 22365 | relation | tbl | | | | AccessShareLock | t | t 22365 | virtualxid | | | | | ExclusiveLock | t | t 22365 | relation | tbl | | | | SIReadLock | t | f (3 rows) ``` ``` -- Session 1 [local:/data/run/pg12]:5120 pg12@testdb=#* update tbl set c1='x' where id = 1; UPDATE 1 [local:/data/run/pg12]:5120 pg12@testdb=#* commit; COMMIT [local:/data/run/pg12]:5120 pg12@testdb=# -- Session 2 [local:/data/run/pg12]:5120 pg12@testdb=# begin; BEGIN [local:/data/run/pg12]:5120 pg12@testdb=#* update tbl set c1 = 'x' where id = 2; UPDATE 1 [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. [local:/data/run/pg12]:5120 pg12@testdb=# ``` 操作过程如下： |时间点|T1|T2| | ------ | ------ | ------ | |t1|begin;| |t2| |begin;| |t3|update tbl set c1 = 'x' where id = 1;| |t4| |begin;| |t5| |update tbl set c1 = 'x' where id = 2;| |t6|commit;| |t7| |commit;| #### Index 在存在索引的情况下，对relation进行w（写）操作，PG会对page加SIReadLock，只会影响到tuple所在的page。 ``` [local:/data/run/pg12]:5120 pg12@testdb=# create table tbl_index(id int ,c1 varchar); CREATE TABLE [local:/data/run/pg12]:5120 pg12@testdb=# insert into tbl_index select x,x from generate_series(1,100000) x; INSERT 0 100000 [local:/data/run/pg12]:5120 pg12@testdb=# create index idx_tbl_index_id on tbl_index(id); CREATE INDEX [local:/data/run/pg12]:5120 pg12@testdb=# select id,ctid from tbl_index where id in(1,20000); id | ctid -------+---------- 1 | (0,1) 20000 | (107,24) (2 rows) ``` id为1和20000的tuple位于不同的page中，下面对这两条记录进行更新 ``` -- session 1 [local:/data/run/pg12]:5120 pg12@testdb=# begin; BEGIN [local:/data/run/pg12]:5120 pg12@testdb=#* update tbl_index set c1='x' where id = 1; UPDATE 1 [local:/data/run/pg12]:5120 pg12@testdb=#* -- session 2 [local:/data/run/pg12]:5120 pg12@testdb=# begin; BEGIN [local:/data/run/pg12]:5120 pg12@testdb=#* update tbl_index set c1='x' where id = 20000; UPDATE 1 [local:/data/run/pg12]:5120 pg12@testdb=#* select pg_backend_pid(); pg_backend_pid ---------------- 22425 (1 row) [local:/data/run/pg12]:5120 pg12@testdb=#* ``` 锁信息，注意：锁定的page是index的page而不是heap page ``` [local:/data/run/pg12]:5120 pg12@testdb=# select pid,locktype,relation::regclass,page,tuple,transactionid,mode,granted,fastpath from pg_locks where pid = 22365; pid | locktype | relation | page | tuple | transactionid | mode | granted | fastpath -------+---------------+------------------+------+-------+---------------+------------------+---------+---------- 22365 | relation | idx_tbl_index_id | | | | RowExclusiveLock | t | t 22365 | relation | tbl_index | | | | RowExclusiveLock | t | t 22365 | virtualxid | | | | | ExclusiveLock | t | t 22365 | transactionid | | | | 423265 | ExclusiveLock | t | f 22365 | page | idx_tbl_index_id | 1 | | | SIReadLock | t | f (5 rows) [local:/data/run/pg12]:5120 pg12@testdb=# select pid,locktype,relation::regclass,page,tuple,transactionid,mode,granted,fastpath from pg_locks where pid = 22425; pid | locktype | relation | page | tuple | transactionid | mode | granted | fastpath -------+---------------+------------------+------+-------+---------------+------------------+---------+---------- 22425 | relation | idx_tbl_index_id | | | | RowExclusiveLock | t | t 22425 | relation | tbl_index | | | | RowExclusiveLock | t | t 22425 | virtualxid | | | | | ExclusiveLock | t | t 22425 | transactionid | | | | 423266 | ExclusiveLock | t | f 22425 | page | idx_tbl_index_id | 56 | | | SIReadLock | t | f (5 rows) ``` 提交事务，两个session均成功 ``` -- session 1 [local:/data/run/pg12]:5120 pg12@testdb=#* commit; COMMIT -- session 2 [local:/data/run/pg12]:5120 pg12@testdb=#* commit; COMMIT ```