# Isolation Level: Serializable V.S. Repeatable Read

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

```bash
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

```bash
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

```bash
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:

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

tx2:

```bash
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:

```bash
postgres=# begin transaction isolation level serializable;
BEGIN
```

tx2:

```bash
postgres=# begin transaction isolation level serializable;
BEGIN
```

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

tx1: a -> b

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

tx2: b -> a

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

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

```bash
postgres=*# commit;
COMMIT

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

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

```bash
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官方文件](https://wiki.postgresql.org/wiki/SSI)，從PostgreSQL version 9.1開始有Documentation of Serializable Snapshot Isolation (簡稱SSI) ，當兩個tx同時進行寫入資料，其中一個先commit，另一個就會被迫rollback，再重新執行，這是SSI的保護機制

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

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

```bash
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](https://wiki.postgresql.org/wiki/SSI)