MySQLのデッドロックを手元で起こして挙動を確認してみた

SHIFT Group 技術ブログ

2021年11月24日 11:35

はじめに

こんにちは、SHIFT の開発部門に所属しているmurasawaです。今期より中途で入社、バックエンド関連の開発を担当して行きます。

現在、研修でデータベースやRestAPIについて基本的な事から学んでいます。
学んだことをアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。

今回はデータベースの設計や使用していく中で気を付けるべきデッドロックについて調べました。
一度起こすことで回避の具体的なイメージもつくと考え、単一のテーブル、2つのテーブルで起こし、起こった際の挙動など確認してみました。

検証環境
CentOS7
MySQL5.7

デッドロックとは

デッドロックとは、和訳すると行き詰まり、手詰まり、膠着状態などの意味を持ちます。
データベースでは複数実行中のトランザクション内で互いにロック獲得待ちとなり、待機状態に入ったまま動かなくなる現象を指します。

デットロックを起こしてみる

言葉ではいまいちわかりずらいので、実際に起こして挙動を確認してみます。

単一テーブルでのデッドロック

単一のテーブルでデッドロックを起こします。
検証用に以下のテーブルとレコードを用意しました。

銀行口座でのお金のやり取りを想定しています

テーブル

mysql> show create table app.a_bank \G;
*************************** 1. row ***************************
       Table: a_bank
Create Table: CREATE TABLE `a_bank` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` int(100) unsigned NOT NULL,
  `account_number` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql>

レコード

mysql> SELECT * FROM a_bank;
+----+------+---------+----------------+---------------------+---------------------+
| id | name | amount  | account_number | updated_at          | created_at          |
+----+------+---------+----------------+---------------------+---------------------+
|  1 | sato |  120000 |         189995 | 2021-11-05 13:31:37 | 2021-11-05 13:31:37 |
|  2 | kato | 1200000 |         189996 | 2021-11-05 13:37:37 | 2021-11-05 13:31:55 |
|  3 | goto |  130000 |         189997 | 2021-11-05 13:33:41 | 2021-11-05 13:33:41 |
+----+------+---------+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

現在、借りているお金を示しあわずお互いに振り込もうとしているa_bank利用者のsatoさんとkatoさんがいます。

STEP1

a_bank使用者でid=1のsatoさんがid=2のkatoさんに3万円を振り込みました。
預金から3万円を引く変更を行います。

まず一つ目のトランザクションAでUPDATEを実行します。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE a_bank SET amount = amount - 30000 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

このUPDATEにより、 id=1のcolumnにロックがかかりました。

STEP2

satoさんが振り込むと同時に、a_bank利用者でid=2のkatoさんがid=1のsatoさんに1万円を振り込みました。
預金から1万円を引く変更を行います。

二つ目のトランザクションBで変更を行います。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE a_bank SET amount = amount - 10000 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

このUPDATEにより、 id=2のcolumnにロックがかかりました。

STEP3

satoさんが振り込んだお金がkatoさんの口座に振り込まれます。
トランザクションAで振り込まれた分の3万円がid=2のkatoさんの預金に合算されます。

mysql> UPDATE a_bank SET amount = amount + 30000 WHERE id = 2;

ロック獲得待ちになりました。

STEP4

katoさんが振り込んだお金がsatoさんの口座に振り込まれます。
トランザクションBで振り込まれた分の1万円がid=1のsatoさんの預金に合算されます。

mysql> UPDATE a_bank SET amount = amount + 10000 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

このUPDATEの実行により、

●トランザクションA : satoさんの口座から3万円が引かれる処理でロックがかかり、katoさんの口座に合算する処理でロック獲得待ちが発生
●トランザクションB : katoさんの口座から1万円が引かれる処理でロックがかかり、satoさんの口座に合算する処理でロック獲得待ちが発生

上記の図のようにお互いが待ちあって、どうしようもない状態になりました。
これがデッドロックです。

デッドロックが起きトランザクションBは自動でROLLBACKされました。
一方トランザクションAでは

mysql> UPDATE a_bank SET amount = amount + 30000 WHERE id = 2;
Query OK, 1 row affected (1.99 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.04 sec)

mysql>

トランザクションBがROLLBACKされたことで、ロック獲得待ちが解除されUPDATE,COMMITが通りました。

mysql> SELECT * FROM a_bank;
+----+------+---------+----------------+---------------------+---------------------+
| id | name | amount  | account_number | updated_at          | created_at          |
+----+------+---------+----------------+---------------------+---------------------+
|  1 | sato |   90000 |         189995 | 2021-11-05 14:38:02 | 2021-11-05 13:31:37 |
|  2 | kato | 1230000 |         189996 | 2021-11-05 14:55:03 | 2021-11-05 13:31:55 |
|  3 | goto |  130000 |         189997 | 2021-11-05 13:33:41 | 2021-11-05 13:33:41 |
+----+------+---------+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

変更は反映されています。

複数テーブルでのデッドロック

2つのテーブル間でデッドロックを起こします。
検証用に以下のテーブルとレコードを用意しました。

テーブル

mysql> show create table app.a_bank \G;
*************************** 1. row ***************************
       Table: a_bank
Create Table: CREATE TABLE `a_bank` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` int(100) unsigned NOT NULL,
  `account_number` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)



mysql> show create table app.b_bank \G;
*************************** 1. row ***************************
       Table: b_bank
Create Table: CREATE TABLE `b_bank` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` int(100) unsigned NOT NULL,
  `account_number` int(10) unsigned NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql>

レコード

mysql> SELECT * FROM a_bank;
+----+------+---------+----------------+---------------------+---------------------+
| id | name | amount  | account_number | updated_at          | created_at          |
+----+------+---------+----------------+---------------------+---------------------+
|  1 | sato |   90000 |         189995 | 2021-11-05 14:38:02 | 2021-11-05 13:31:37 |
|  2 | kato | 1230000 |         189996 | 2021-11-05 14:55:03 | 2021-11-05 13:31:55 |
|  3 | goto |  130000 |         189997 | 2021-11-05 13:33:41 | 2021-11-05 13:33:41 |
+----+------+---------+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)


mysql> SELECT * FROM b_bank;
+----+----------+----------+----------------+---------------------+---------------------+
| id | name     | amount   | account_number | updated_at          | created_at          |
+----+----------+----------+----------------+---------------------+---------------------+
|  1 | suzuki   |   300000 |          14560 | 2021-11-05 13:36:03 | 2021-11-05 13:36:03 |
|  2 | hirakawa |  1100000 |          14561 | 2021-11-05 13:36:48 | 2021-11-05 13:36:48 |
|  3 | masuda   | 10000000 |          14562 | 2021-11-05 13:37:19 | 2021-11-05 13:37:19 |
+----+----------+----------+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

今度は、借りているお金を示しあわずお互いに振り込もうとしているa_bank利用者のgotoさんとb_bank利用者のhirakawaさんがいます。

STEP1

a_bank使用者でid=3のgotoさんがb_bank利用者のid=2のhirakawaさんにお金を10万円振り込みました。
預金から10万円を引く変更を行います。
まず一つ目のトランザクションAでUPDATEを実行します。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE a_bank SET amount = amount - 100000 WHERE id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

このUPDATEにより、 a_bankテーブルのid=3のcolumnにロックがかかりました。

STEP2

satoさんが振り込むと同時に、b_bank利用者でid=2のhirakawaさんがa_bank利用者でid=3のsatoさんにお金を20万円振り込みました。
預金から20万円を引く変更を行います。
二つ目のトランザクションBで変更を行います。

mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE b_bank SET amount = amount - 200000 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

このUPDATEにより、 b_bankテーブルのid=2のcolumnにロックがかかりました。

STEP3

gotoさんが振り込んだお金がhirakawaさんの口座に振り込まれます。
トランザクションAで振り込まれた分の10万円がhirakawaさんの預金に合算されます。

mysql> UPDATE b_bank SET amount = amount + 100000 WHERE id = 2;

ロック獲得待ちになりました。

STEP4

hirakawaさんが振り込んだお金がgotoさんの口座に振り込まれます。
トランザクションBで振り込まれた分の20万円がgotoさんの預金に合算されます。

mysql> UPDATE a_bank SET amount = amount + 200000 WHERE id = 3;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

デッドロックが起き自動でROLLBACKされました。

一方トランザクションAでは

mysql> UPDATE a_bank SET amount = amount + 30000 WHERE id = 2;
Query OK, 1 row affected (1.36 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#今回はROLLBACKしてみた
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql>

トランザクションBがROLLBACKされたことで、ロック獲得待ちが解除されUPDATE,ROLLBACKが通りました。

mysql> SELECT * FROM a_bank;
+----+------+---------+----------------+---------------------+---------------------+
| id | name | amount  | account_number | updated_at          | created_at          |
+----+------+---------+----------------+---------------------+---------------------+
|  1 | sato |   90000 |         189995 | 2021-11-05 14:38:02 | 2021-11-05 13:31:37 |
|  2 | kato | 1230000 |         189996 | 2021-11-05 14:55:03 | 2021-11-05 13:31:55 |
|  3 | goto |  130000 |         189997 | 2021-11-05 13:33:41 | 2021-11-05 13:33:41 |
+----+------+---------+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM b_bank;
+----+----------+----------+----------------+---------------------+---------------------+
| id | name     | amount   | account_number | updated_at          | created_at          |
+----+----------+----------+----------------+---------------------+---------------------+
|  1 | suzuki   |   300000 |          14560 | 2021-11-05 13:36:03 | 2021-11-05 13:36:03 |
|  2 | hirakawa |  1100000 |          14561 | 2021-11-05 13:36:48 | 2021-11-05 13:36:48 |
|  3 | masuda   | 10000000 |          14562 | 2021-11-05 13:37:19 | 2021-11-05 13:37:19 |
+----+----------+----------+----------------+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

両方ROLLBACKされたので変更は反映されていません。

行ロックとテーブルロック

今回の検証で知識では知っていたものの実際に遭遇した挙動として、
検索に主キーやユニークキーのカラムをWHERE句で指定すると行ロックになり、
制約のないカラムをWHERE句で指定するとテーブルロックになることが実感できました。

確かに主キーやユニークキーでないと行を一意に決められないのでこの挙動は納得です。
検索の際の使い分けや考慮すべき点ですね。

追加でテーブルロックを用いてデッドロックを起こしてみました。

単一のテーブル

前述の方法同様に主キーであるidではなく制約のないaccount_numberでUPDATEを行いデッドロックを起こそうとすると

STEP1

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE a_bank SET amount = amount - 30000 WHERE account_number = 189995;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

STEP2

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE a_bank SET amount = amount - 10000 WHERE account_number = 189996;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

テーブルロックがかかっているため、STEP2の時点でUPDATEが実行できません。

複数テーブル

同様に制約のないaccount_numberでデータを指定しデッドロック発生を試みます。

STEP1

mysql> BEGIN;
Query OK, 0 rows affected (0.03 sec)

mysql> UPDATE a_bank SET amount = amount -100000 WHERE account_number = 189997;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

STEP2

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE b_bank SET amount = amount - 200000 WHERE account_number = 14561;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

今回はSTEP2まで実行できました。
それぞれテーブルロックがかかっていますが、a_bankとb_bankという違うテーブルなのでテーブルロックの影響を受けていません。

続けてSTEP3、STEP4を行います

STEP3

mysql> UPDATE b_bank SET amount = amount + 100000 WHERE account_number = 14561;

ロック獲得待ちになり

STEP4

mysql> UPDATE a_bank SET amount = amount + 200000 WHERE account_number = 189997;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

デットロックが発生しました。
以降の挙動は前述のものと同様でした。

テーブルロックであっても複数テーブルであればデットロックは発生します。

結果まとめ

「行ロックとテーブルロック」×「単一テーブルと複数テーブル」で試してみましたが、デッドロックが発生した際の挙動としては変わりませんでした。

またテーブルロックがかかっている場合、単一テーブルではロックをかけているトランザクション以外テーブルを変更することができずにデッドロックは起こせませんでした。

MySQL5.7においてはデッドロックを起こすSQLを実行したトランザクションBがROLLBACKされ、ロック獲得待ちをしていたトランザクションAはトランザクションBのROLLBACKによりロックが解かれてUPDATE、COMMITが実行できるようになりました。

終わりに

今回、回避方法やイメージをつかむために、一度デッドロックを起こしてみました。思っていたよりも簡単に起こせたということから、DBの設計やプログラムでも意識してそもそも発生しないようにする運用を学んでいかなければならないと実感しました。実業務でも生かせたらと考えています。

■この公式ライターの他の記事はこちら

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。
DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。