MySQL(innodb)のインデックスとDeadLock
-
2022年12月2日
こんにちは。ポケコロのサーバー開発の Kです。
今回は、人気のデータペースMySQL(innodb)のインデックスとDeadLockについてお話しできればと思います。
そもそもデータベースのロックとは、並列処理によってデータの不整合が発生しないよう、同じデータを同時に更新・参照することを防ぐ仕組みです。
しかしMySQLのロックはインデックスと直接関係していて、インデックスを利用した検索・更新によってDeadLockが発生してしまう場合があります。
今回は上記のようなことについて、1つの例を題材に簡単に解説します。
事前準備
以下のテーブルを作成します。
| id | username | status | |
|---|---|---|---|
| 1 | test4 | test4@yyyy.com | N |
| 2 | test3 | test3@yyyy.com | N |
| 3 | test2 | test2@yyyy.com | Y |
| 4 | test1 | test1@yyyy.com | Y |
以下のインデックスを作成します。
id：プライマリーキー
username：セカンダリインデックス
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | BTREE | |||
| user | 1 | username_index | 1 | username | A | 3 | NULL | NULL | YES | BTREE |
Transaction
例えば、以下のような業務ロジックがあるとします。
TransactionA : idが１番と２番のメールアドレスを更新
TransactionB : 名称がtest3,test4且つstatusがYのものを更新用で抽出（抽出の結果は0行）
TransactionA
update user set email = 'test1@xxx.com' where id =1; update user set email = 'test2@xxx.com' where id =2;
TransactionB
select * from user where username in ('test3','test4') and status ='Y' for update;
上記のようなTransactionAとTransactionBが同時に実行された場合、TransactionBの抽出結果が0行だったとしても、DeadLockが発生する可能性は高いです。
原因はお分かりでしょうか。
DeadLock解説
DeadLock発生を解説する前に、
まず、MySQL(innodb)のロックはどういう仕組みかを簡単に説明します。
MySQLのロック対象は抽出した行ではなく、該当のインデックスを含む行です。
(よく「抽出の行のみロックする」だと勘違いされる)
このことを頭に入れた上で、先ほどの例でどのようなことが起きていたのか整理してみましょう。
TransactionAの場合
idはプライマリーインデックスのため、プライマリーインデックスにて以下のように順番にロックをかけます。
① id=1のプライマリーインデックスにロックをかけます。
② id=2のプライマリーインデックスにロックをかけます。
TransactionBの場合
usernameはセカンダリインデックスのため、以下のような順番でロックをかけます。
① セカンダリインデックスusername_indexにて、test3のものにロックをかけます。
② test3がid=2のため、さらに、id=2のプライマリーインデックスにロックをかけます。
③ セカンダリインデックスusername_indexにて、test4のものにロックをかけます。
④ test4がid=1のため、さらに、id=1プライマリーインデックスにロックをかけます。
※statusにインデックスを張っていないため、YとNを無視して全部ロックをかけます。（今回のDeadLock原因はここです）
TransactionAとTransactionBが同時に実行されている場合、
- A①が完了すると
→ Aがid=1のプライマリーインデックスをロックしている - B①,②が完了すると
→ Bがusername=test3のセカンダリーインデックス、id=2のプライマリーインデックスをロックしている - A②を実行すると
→ id=2がBによってロックされているため、ロック待ち状態になる - B④を実行すると
→ id=1がAによってロックされているため、ロック待ち状態になる
となり、DeadLockが発生してしまいます。
終わりに
今回はMySQLのインデックスとDeadLockについて簡単にご紹介しました。
MySQLをシステム運用する上で、インデックス＋非インデックスを条件にレコードを更新することもあると思います。
DeadLockが発生しないように、
- 検索条件に使用する非インデックスのカラムにインデックスを張る
- 業務ロジックやTransaction中で実行するSQLを工夫する
などの対策・検討が必要かもしれません。
DeadLock発生時の原因分析にこの記事が参考となれば幸いです。
ココネでは一緒に働く仲間を募集中です。
ご興味のある方は、ぜひこちらの採用特設サイトをご覧ください。
