PostgreSQL DBA (177C)-Serializability Isolation (Index vs NonIndex)

PostgreSQL version 9.1 or above provides a true Serializability Isolation. This section mainly introduces the difference between indexed and non-indexed Serializability Isolation.

NonIndex

If you do a w (write) operation on relation without an index, PG will add SIReadLock to the entire relation, because the locking granularity is at the Relation level, so if other session also do w operations on this table, there will be a rw dependency loop between the two session, and one of the session will be terminated.

-- 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)

Query lock information and add SIReadLock to relation

[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 | 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 c1x '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 where id 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=#

The operation process is as follows:

Time point T1T2t1beginX T2beginShield T3update tbl set C1 ='x' where id = 1polit4beginShield T5update tbl set C1 ='x' where id = 2politieT6beginShield T7begin Index

If there is an index, w (write) to relation, and PG will add SIReadLock to page, which will only affect the page where tuple is located.

[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 journal x from generate_series (1m 100000) x Ting 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 (1Magne 20000); id | ctid-+- 1 | (0meme 1) 20000 | (107 Magi 24) (2 rows)

Tuple with id of 1 and 20000 are in different page. Update these two records below

-- session 1 [local:/data/run/pg12]: 5120 pg12@testdb=# begin;BEGIN [local:/data/run/pg12]: 5120 pg12@testdb=#* update tbl_index set c1 updated 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 updated 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=#*

Lock information, note: the locked page is the page of index, not 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 | 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 | 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) |

The transaction was committed and both session succeeded

-- session 1 [local:/data/run/pg12]: 5120 pg12@testdb=#* commit;COMMIT-- session 2 [local:/data/run/pg12]: 5120 pg12@testdb=#* commit;COMMIT