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)

```

```
-- 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=#*

```
提交事務，兩個session均成功
```
-- session 1
[local:/data/run/pg12]:5120 pg12@testdb=#* commit;
COMMIT

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

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

相關文章