PostgreSQL DBA(177) - Serializability Isolation（Index vs NonIndex）

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 transactionsDETAIL:  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=#

操作过程如下：

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