更新のロストの検出について、MySQLとPostgresSQLの挙動差異

# 概要

[データ指向アプリケーションデザイン](https://amazon.jp/dp/4873118700)という本の`7.2.3.3　更新のロストの自動検出`という章に以下の記述があります。

```
PostgreSQLのリピータブルリード、Oracleのserializable、SQL Serverのスナップショット分離レベルは
更新のロストが発生したことを自動的に検出し、問題のトランザクションを中断させます。
しかし、MySQL/InnoDBのリピータブルリードは更新のロストを検出しません[23]。

抜粋:: Martin Kleppmann  “データ指向アプリケーションデザイン”。   
```

保証するトランザクションのレベルをリピータブルリードにした際に、PostgresSQLであれば更新のロストを自動検出できるが、MySQLだと自動検出できないという内容です。

PostgresSQL、MySQLでそのような状況となった場合に、具体的にどのような挙動となるのか確かめてみました。

# 検証環境

macOS: Big Sur 11.5.2（20G95）
チップ: Apple M1
Docker: Docker version 20.10.5, build 55c4c88
PostgresSQL: 14.1
MySQL: 8.0.27

## PostgresSQLの場合

### 環境構築

1. `docker network create postgres-network` で検証用のネットワークを作る※
1. `docker run --network postgres-network --name postgres -e POSTGRES_PASSWORD=password -d postgres:14.1-alpine3.14` でPostgresSQLのコンテナを`postgres-network`ネットワーク内に立ち上げる。パスワードは`password`
1. `docker run -it --rm --network postgres-network --name psql postgres:14.1-alpine3.14 psql -h postgres -U postgres` もう一つコンテナを立ち上げて、その中から`psql`コマンドを叩いて、立ち上げたDBコンテナに接続する
    - もう片方のクライアントを立ち上げる際は`docker exec -it psql psql -h postgres -U postgres` 
1. `postgres=# create database my_postgre;` でDB作る
1. 以下のコマンドでトランザクション分離レベルの変更とデータ準備をする

```
postgres=# create database my_postgres;
CREATE DATABASE

postgres=# ALTER DATABASE my_postgres SET default_transaction_isolation = 'repeatable read';
ALTER DATABASE

postgres=# \c my_postgres;
You are now connected to database "my_postgres" as user "postgres".

my_postgres=# create table my_table (name varchar(10));
CREATE TABLE

my_postgres=# insert into my_table values ('nannany');
INSERT 0 1

my_postgres=# show transaction isolation level;
 transaction_isolation
-----------------------
 repeatable read
(1 row)
```

※デフォルトのネットワークだとコンテナ名での名前解決ができないようなので作った(https://knowledge.sakura.ad.jp/16082/)

### 検証コマンド

下記gifの通りです。

- 一方のクライアント（A）でトランザクションを開始しておき、その中でupdateを行う
    - `begin;`
    - `update my_table set name = 'nannany';`
- もう片方のクライアント(B)でもupdateを行う
    - `update my_table set name = 'numnumy';`
- Aの方でcommitする
    - `commit;`

![ezgif.com-gif-maker (1).gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/441085/0954180c-162b-7c74-9182-434ec54a0d25.gif)

### 結果

Bのupdateは成功せず、

```
ERROR:  could not serialize access due to concurrent update
```

というエラーが返却されました。
[こちらのドキュメント](https://www.postgresql.org/docs/14/transaction-iso.html#XACT-REPEATABLE-READ)の`13.2.2. Repeatable Read Isolation Level`に記載されているエラーに等しいようです。

## MySQLの場合

### 環境構築

[このページ](https://hub.docker.com/r/mysql/mysql-server)の手順に従って環境を作ります。

1. `docker run --name=mysql-server -d mysql/mysql-server:8.0.27-1.2.6-server` でコンテナ起動
1. `docker logs mysql-server 2>&1 | grep GENERATED` でログインパスワードを確認
    - `[Entrypoint] GENERATED ROOT PASSWORD: ~~~~~~~~~~~~`こんな感じでパスワードが見える
1. `docker exec -it mysql-server mysql -uroot -p` でコンテナに入る
    - パスワードを聞かれるので、上記のパスワード(`~~~~~~~~~~~~`部分)を入力
1. `ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';` でパスワードを変える
1. `SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;`でトランザクション分離レベルが`REPEATABLE-READ`になっていることを確認する
    - デフォルトで`REPEATABLE-READ`なはず
1. 以下のコマンドでデータを準備する

```
mysql> create database my_mysql;
Query OK, 1 row affected (0.01 sec)

mysql> use my_mysql
Database changed

mysql> create table my_table (name varchar(10));
Query OK, 0 rows affected (0.04 sec)

mysql> insert my_table values ('nannany');
Query OK, 1 row affected (0.01 sec)
```

### 検証コマンド

下記gifの通りです。

- 一方のクライアント（A）でトランザクションを開始しておき、その中でupdateを行う
    - `begin;`
    - `update my_table set name = 'aaa';`
- もう片方のクライアント(B)でもupdateを行う
    - `update my_table set name = 'bbb';`
- Aの方でcommitする
    - `commit;`

![ezgif.com-gif-maker (2).gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/441085/26dba228-7113-9f5a-eff7-0cb7ae300b18.gif)

### 結果

A、Bのコマンド双方とも正常に終了します。Aの更新がなかったことにされました。

Bのupdateを実行した際、すぐには実行結果は返ってこず待機状態になり、Aのcommitが終わったら、Bのコマンドが走り始めた模様です。  
実行後の結果としても、Bのupdateの結果がテーブルには反映されています。

つまり、Aの更新はAに知らされることなく、かき消されたことになります。

# まとめ

トランザクション分離レベルがリピータブルリードである際の更新のロストの自動検出についてみてきました。

[データ指向アプリケーションデザイン](https://amazon.jp/dp/4873118700)で述べられていたことは正しく、ロストした際に、PostgreSQLでは

```
ERROR:  could not serialize access due to concurrent update
```

として検知できることがわかりました。