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

概要

データ指向アプリケーションデザインという本の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 で検証用のネットワークを作る※
2. docker run --network postgres-network --name postgres -e POSTGRES_PASSWORD=password -d postgres:14.1-alpine3.14 でPostgresSQLのコンテナをpostgres-networkネットワーク内に立ち上げる。パスワードはpassword
3. 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
4. postgres=# create database my_postgre; でDB作る
5. 以下のコマンドでトランザクション分離レベルの変更とデータ準備をする

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;

結果

Bのupdateは成功せず、

ERROR:  could not serialize access due to concurrent update

というエラーが返却されました。
こちらのドキュメントの13.2.2. Repeatable Read Isolation Levelに記載されているエラーに等しいようです。

MySQLの場合

環境構築

このページの手順に従って環境を作ります。

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

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;

結果

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

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

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

まとめ

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

データ指向アプリケーションデザインで述べられていたことは正しく、ロストした際に、PostgreSQLでは

ERROR:  could not serialize access due to concurrent update

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