Docker × MySQL で学ぶ、トランザクションとデッドロック体験

トランザクションやロックの仕組みは、データベースを扱う上で欠かせない重要な概念との事で学ぶ。
特に、複数の処理が同時に動く環境では、デッドロックという相互待ちの問題が発生することがあります。
本記事では、MySQL を Docker コンテナで手軽に立ち上げ、超シンプルな銀行システムを例にして「デッドロックをわざと起こしてみる → 対策してみる」流れを一通り体験します。
恩師にローカルは無闇に汚すなと教育されてきたので、今回は MySQL はインストールせず Docker Hub から取得する形で手軽に環境構築をします！

トランザクションとは

トランザクションとは、データベースにおける「一連の操作」をひとまとまりとして扱う仕組みです。

典型的には以下のようなACID特性を満たします。

Atomicity（原子性）
成功ならすべて反映、失敗ならすべて取り消す
Consistency（一貫性）
トランザクション実行前後でデータの整合性を保つ
Isolation（分離性）
複数のトランザクションが干渉し合わないようにする
Durability（永続性）
コミットが完了したら障害が起きてもデータが消えない

例えば、銀行システムで「口座Aからお金を引き出し(Aの残高をマイナス)、口座Bへ振り込む(Bの残高をプラス)」という複数の処理で成り立つ一連の操作を途中で止めずに確実に行うために、トランザクションが使われます。

ロックとは

データベースでは、複数のトランザクションが同時に同じデータを操作するとき、データの不整合や競合を防ぐために「ロック」を使います。ロックを取得することで、あるトランザクションがデータを操作している最中、他のトランザクションに操作を制限する仕組みを実現します。

1. 共有ロック（Shared Lock）

今回は共有ロックは体験しませんが、紹介です。

目的

読み取り専用ロックとも呼ばれます。
データを“読んでいる”間、他のトランザクションも同じデータを読み取ることはできますが、書き込み（更新・削除）は行えません。
複数の「読み取り専用トランザクション」が、同時に同じデータに対して共有ロックを取得できます。

どんなタイミングで使われるか

データを読み取りつつ、他からの書き込みをブロックしたい場合に使われます。
たとえば、MySQL での SELECT ... LOCK IN SHARE MODE（古い構文）や SELECT ... FOR SHARE（MySQL 8.0 / PostgreSQL）を利用すると、共有ロックがかかります。

2. 排他ロック（Exclusive Lock）

今回体験するのがこちらです。

目的

書き込み（更新）操作を独占するためのロックです。
該当データを操作する間、他のトランザクションが同じデータに対して読み書きを行えないように制限します。
同じデータに対して同時に排他ロックを取得できるのは1つのトランザクションだけです。

どんなタイミングで使われるか

トランザクション内で更新系クエリ (UPDATE/DELETE/INSERT) を実行するとき

ロック解除のタイミング

共有ロック、排他ロックどちらもCOMMIT;やROLLBACK;でトランザクションが終了した時にロックが解除されます。
トランザクションが続く限りロックは保持されるため、長時間ロックしていると他の処理をブロックしてしまう可能性があります。

デッドロックとは

デッドロックは、複数のトランザクションが互いに相手のロックを待ち合う状態で、どちらも先に進めなくなる問題です。
例えば、口座1 → 口座2 の順で更新しているトランザクションAと、口座2 → 口座1 の順で更新しているトランザクションBが同時に走ると、お互いが相手のロック解放を待ち合って永遠に進めなくなります。

ちなみに
共有ロックと排他ロックで起きるデッドロックを"変換デッドロック"、
排他ロックと排他ロックで起きるデッドロックを"サイクルデッドロック"
というそうです。

多くのデータベースでは、デッドロックを検知すると、片方のトランザクションを強制的にロールバックして解消します。MySQL(InnoDB)の場合は、以下のようなエラーが返ります。
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

文章で説明してもいまいち良くわからないので、実際にMySQLでいじってみます！
今回はサイクルデッドロックを体験しようと思います！

Docker × MySQL環境を作る

MySQL イメージを取得

Docker Desktop(もしくはOrbStack)のインストールはされている前提で進ませてください。

ターミナルにて

docker pull mysql:8.0

でMySQL 8.0 系のイメージを Docker Hub から取得します。
バージョンを固定したい場合は mysql:5.7 のように指定可能です。

コンテナを起動

docker run --name my-mysql \
  -e MYSQL_ROOT_PASSWORD=my-secret-pw \
  -p 3306:3306 \
  -d mysql:8.0

これでホストの localhost:3306 にアクセスすると、コンテナ内の MySQL に繋げます。

コンテナ内で MySQL を使う

docker exec -it my-mysql bash
mysql -u root -p

パスワード入力を求められたら、先ほど MYSQL_ROOT_PASSWORD に指定した my-secret-pw を入力してください。
これで MySQL クライアントを使えるようになります。

デッドロックを体験する（銀行システム例）

ここでは、超シンプルな銀行口座テーブルを作って、2つのトランザクションで同時に口座残高を更新し合い、デッドロックを起こしてみます。以下の流れで行きます。

2つのターミナルで MySQL コンテナに入る
片方のターミナルで簡単な銀行テーブルを作成し、初期データを用意
2つのセッションから同時にトランザクションを開始して更新を試みる
デッドロックを発生させる

テーブル準備

-- ターミナル1
CREATE DATABASE IF NOT EXISTS bank_demo;
USE bank_demo;

-- 銀行口座テーブル
CREATE TABLE bank_accounts (
  account_id INT PRIMARY KEY,
  account_name VARCHAR(50),
  balance INT
) ENGINE=InnoDB;

-- 初期データを投入
INSERT INTO bank_accounts (account_id, account_name, balance) VALUES
(1, 'OHTANI', 1000),
(2, 'MIZUHARA', 1000);

-- テーブルの確認
SELECT * FROM bank_accounts;
+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
|          1 | OHTANI       |    1000 |
|          2 | MIZUHARA     |    1000 |
+------------+--------------+---------+
2 rows in set (0.00 sec)

ちなみにbalanceは口座残高という意味。

デッドロックを引き起こす状況を用意

シナリオ概要
・大谷から水原へ100を振り込むトランザクション1 (ターミナル1)
・水原から大谷へ100を振り込むトランザクション2 (ターミナル2)

-- ターミナル1
USE bank_demo;

START TRANSACTION;

/* Aliceの口座から100引き落とす */
UPDATE bank_accounts
   SET balance = balance - 100
 WHERE account_id = 1;  -- 大谷
 
 -- ここでまだ COMMIT や ROLLBACK をせず、待機（トランザクションをそのまま）

これにより、bank_accounts テーブルの id=1 (大谷) の行に対して排他ロックが取得されている状態です。なぜならトランザクション内でUPDATE文を書くことが、排他ロックの発動条件だったからです。

次に同じMySQLコンテナに入った別タブのターミナルで

-- ターミナル2
USE bank_demo;

START TRANSACTION;

/* Bobの口座から100引き落とす */
UPDATE bank_accounts
   SET balance = balance - 100
 WHERE account_id = 2;  -- 水原

-- ここでまだ COMMIT や ROLLBACK はしない

これで水原の行にも排他ロックがかかりました。

デッドロック発生

まずはトランザクション1の続きで、大谷から水原の振込を完了したいので、水原のレコードを更新しようとします。

-- ターミナル1 (大谷 -> 水原 へ入金する処理を続行)
UPDATE bank_accounts
   SET balance = balance + 100
 WHERE account_id = 2;

ここで、トランザクション2 がすでに id=2(水原の行) をロックしているため、このクエリは待ち状態（ロック解除待ち）になるので何も出力されないはずです。水原の行をロックしている他のトランザクションが終了すれば待ち状態は解放されて、処理を進むことができます。

そして次は、トランザクション2で水原から大谷の振り込みを完了したいので、大谷のレコードを更新しようとします。

-- ターミナル2 (水原 -> 大谷 へ入金処理を続行)
UPDATE bank_accounts
   SET balance = balance + 100
 WHERE account_id = 1;

トランザクション1 が id=1 をロック中のため、こちらも待ち状態になります。

トランザクション1はトランザクション2が終了しないと処理を進められない。
トランザクション2はトランザクション1が終了しないと処理を進められない。
どちらものトランザクションも永遠に進めない！これがデッドロック状態です。

しかしInnoDBはデッドロックを検出すると,どちらか一方のトランザクションを強制ロールバックします。そして、そのトランザクションには下記のようなエラーが返されると思います。

#ターミナル2
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

ここでターミナル１を確認すると、トランザクション1はエラーが出ていません。
試しにCOMMIT;でトランザクションを終了して、テーブルの中身を確認します。

-- ターミナル1
SELECT * FROM bank_accounts;

+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
|          1 | OHTANI       |     900 |
|          2 | MIZUHARA     |    1100 |
+------------+--------------+---------+
2 rows in set (0.00 sec)

トランザクション1の処理だけは問題なく完了していることがわかりますね。

デッドロックを対策してみる

対策方法1

step1. トランザクションを短く保つ
トランザクション時に画面入力待ちなどを挟まず、必要最小限の更新処理だけを行って即コミットする。
step2. デッドロック (エラー1213) が起きたら、ロールバック後に再実行
DBMSがデッドロックを感知してくれることを信じれば、片方は成功しているので失敗した側のトランザクションを再実行すれば良い。

この対策法は「デッドロックは起こり得る」という前提で、発生時に再度トランザクションをやり直す方法です。

今回はstep2についてしか体験できませんが、先ほどエラーが出たターミナル2の方で、再度同じSQL文を叩いてCOMMIT;してテーブルを確認すると、OHTANI 1000, MIZUHARA 1000という予定通りの結果となっているのが確認できます。結構当たり前です。

対策方法2

更新順序を統一する

デッドロックは、異なる順序で同じリソース(今回は行)をロックしようとすると発生しやすいです。
そこで、「常に同じ順序でロックを取得」するルールを設計段階で決めておくと、相互待ちを起こしにくくなります。

銀行口座の場合：
口座ID が小さい方 → 大きい方の順で必ず UPDATE など

テーブルが複数ある場合：
必ずテーブルA → テーブルB の順で UPDATE など

さっきの簡単な銀行システムの例で試してみます。

-- ターミナル1
START TRANSACTION;

UPDATE bank_accounts SET balance = balance - 100 WHERE account_id = 1;  -- 先に1を更新
UPDATE bank_accounts SET balance = balance + 100 WHERE account_id = 2;  -- 次に2を更新
-- ここでまだ COMMIT しないでおく

次にトランザクション2ではid=2 → id=1への送金だが、更新する行の順序を1→2とする。

-- ターミナル2
START TRANSACTION;

UPDATE bank_accounts SET balance = balance + 100 WHERE account_id = 1;   -- 先に1を更新
UPDATE bank_accounts SET balance = balance - 100 WHERE account_id = 2;   -- 次に2を更新

この時ターミナル2では一つ目の更新処理がロック解除待ちとなっていることが確認できます。なぜならトランザクション1ではまだCOMMIT;していないのでid=1の行はロック解除されていないからです。そこでターミナル1に戻りCOMMIT;をしてトランザクション終了をして、またターミナル2に戻ると処理が正常に進んでいることが確認できます！トランザクション2もCOMMIT;してテーブルを確認します。

SELECT * FROM bank_accounts;
+------------+--------------+---------+
| account_id | account_name | balance |
+------------+--------------+---------+
|          1 | OHTANI       |    1000 |
|          2 | MIZUHARA     |    1000 |
+------------+--------------+---------+
2 rows in set (0.00 sec)

上手くいってますね！
これでデッドロックせず、処理後のデータの整合性が取れている状態となりました！

まとめ

今回は超簡単な例としてデッドロックを体験してみましたが、実務レベルでどこまでトランザクションを意識しているのかも気になるし、デッドロックを対策するために実装の際はどんなロジックを書けば良いのかなども今後記事として出せたらなと思います。