PostgreSQL 9.1或以上版本，提供了真正意義的Serializability Isolation，本節(jié)主要介紹了Serializability Isolation下有索引與沒有索引的區(qū)別。

公司主營業(yè)務(wù)：成都網(wǎng)站制作、網(wǎng)站建設(shè)、移動網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊有機(jī)會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)推出平順免費做網(wǎng)站回饋大家。

NonIndex

在沒有索引的情況下，對relation進(jìn)行w（寫）操作，PG會對整個relation加SIReadLock，因為加鎖粒度是Relation級別，因此如果其他session也對這個表進(jìn)行w操作，那么兩個session之間會出現(xiàn)rw依賴循環(huán)，其中一個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=#

操作過程如下：

Index

在存在索引的情況下，對relation進(jìn)行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中，下面對這兩條記錄進(jìn)行更新

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

提交事務(wù)，兩個session均成功

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