Isolation Level: Serializable V.S. Repeatable Read

前置作業：在資料庫建立資料表和資料

docker exec -it pgacid psql -U postgres

postgres=# create table alphabets(id serial primary key, a text);
CREATE TABLE

// 一次新增一筆
postgres=# insert into alphabets(a) values('a');
INSERT 0 1

// 一次新增多筆
postgres=# insert into alphabets(a) values('a'),('a'),('b'),('b');
INSERT 0 4

postgres=# select * from alphabets;
 id | a 
----+---
  1 | a
  2 | a
  3 | a
  4 | b
  5 | b
(5 rows)

＊＊預期目標：tx1先執行，a -> b，tx2 後執行，b -> a

Isolation Level: Repeatable Read

打開兩個terminal，開始兩個tx，設定isolation level repeatable read

tx1: a -> b

postgres=# begin transaction isolation level repeatable read;
BEGIN

postgres=*# update alphabets set a = 'b' where a = 'a';
UPDATE 3
postgres=*# select * from alphabets;
 id | a 
----+---
  4 | b
  5 | b
  1 | b
  2 | b
  3 | b
(5 rows)

postgres=*# commit;
COMMIT

tx2: b -> a

postgres=# begin transaction isolation level repeatable read;
BEGIN

postgres=*# update alphabets set a = 'a' where a = 'b';
UPDATE 2

postgres=*# select * from alphabets;
 id | a 
----+---
  1 | a
  2 | a
  3 | a
  4 | a
  5 | a
(5 rows)

postgres=*# commit;
COMMIT

兩個tx都完成，分別在兩個terminal看看最後結果如何？

tx1:

postgres=# select * from alphabets;
 id | a 
----+---
  1 | b
  2 | b
  3 | b
  4 | a
  5 | a
(5 rows)

tx2:

postgres=# select * from alphabets;
 id | a 
----+---
  1 | b
  2 | b
  3 | b
  4 | a
  5 | a
(5 rows)

結果不如預期，alphabets table的a欄位資料，沒有全部都變成a！

要如何達到預期的結果？

Isolation Level: Serializable

同時開始tx1, tx2，並設定isolation level serializable

tx1:

postgres=# begin transaction isolation level serializable;
BEGIN

tx2:

postgres=# begin transaction isolation level serializable;
BEGIN

tx1, tx2 同時執行各自的任務

tx1: a -> b

postgres=*# update alphabets set a = 'b' where a = 'a';
UPDATE 2

tx2: b -> a

postgres=*# update alphabets set a = 'a' where a = 'b';
UPDATE 2

tx1 先 commit，並看結果如何？確實順利把 a -> b

postgres=*# commit;
COMMIT

postgres=# select * from alphabets;
 id | a 
----+---
  3 | b
  4 | b
  1 | b
  2 | b
(4 rows)

tx2 commit並看結果如何？出現 ERROR！

postgres=*# 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.

WHY?

根據PostgreSQL官方文件，從PostgreSQL version 9.1開始有Documentation of Serializable Snapshot Isolation (簡稱SSI) ，當兩個tx同時進行寫入資料，其中一個先commit，另一個就會被迫rollback，再重新執行，這是SSI的保護機制

如何讓tx2正確執行，達到預期的結果(b -> a)？

rollback tx2，再次執行，就能得到預期的結果

postgres=# rollback;
WARNING:  there is no transaction in progress
ROLLBACK

postgres=# begin transaction isolation level serializable;
BEGIN

postgres=*# update alphabets set a = 'a' where a = 'b';
UPDATE 4

postgres=*# commit;
COMMIT

postgres=# select * from alphabets;
 id | a 
----+---
  3 | a
  4 | a
  1 | a
  2 | a
(4 rows)

Reference:

Udemy Course: Fundamentals of Database Engineering 15

SSI - PostgreSQL wiki