MySQLで「Deadlock found when trying to get lock」デッドロック

2023年8月5日システムMySQL,PHP

MySQLで「Deadlock found when trying to get lock」デッドロック

MySQLで SELECT ・・・ FOR UPDATE で複数行をロックしてから UPDATE すると「Deadlock found when trying to get lock」とエラーが出てデッドロックになった時の話

詳しくは以下の記事にわかりやすくまとめられていました
参考記事：SELECT … FOR UPDATEとUPDATEでデッドロックが出る人へ

MySQLで「Deadlock found when trying to get lock」デッドロック

発生する条件

  • MySQL8.0.17以前
  • Where句で取得するレコードが複数行（※フルテーブルスキャンに近くなると発生）
  • 処理が TRANSACTION 開始 → SELECT ・・・ FOR UPDATE → UPDATE の順で行っている

原因

詳しい発生原因は リリースノート に書かれている

InnoDB: A deadlock was possible when a transaction tries to upgrade a record lock to a next key lock. (Bug #23755664, Bug #82127)

InnoDB: トランザクションがレコード ロックを次のキー ロックにアップグレードしようとすると、デッドロックが発生する可能性がありました。 (バグ #23755664、バグ #82127)

解決方法

解決方法は３つ

MySQLのバージョンを 8.0.18 以降にする

これが一番確実な方法
でも運用中のシステムだと安易にMySQLのバージョンアップできないので断念…

transaction_isolation を REPEATABLE-READ にする

これもシステムに影響するので断念…

SELECT ・・・ FOR UPDATE するレコードを絞る

結局 SELECT ・・・ FOR UPDATE でロックするレコードを可能な限り絞るという方法で対応

対応した解決方法

発生したのは在庫管理システムで起こりました

テーブルとデータ

参考するテーブル構成

MySQL > show create table products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '在庫商品ID',
  `amount` int(11) NOT NULL COMMENT '金額',
  `saled` datetime DEFAULT NULL COMMENT '売却日時',
  PRIMARY KEY (`id`),
  KEY `saled` (`saled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='在庫商品'
1 row in set (0.01 sec)

サンプルデータ

insert into products 
(amount, saled) 
values 
(100, NULL),(110, NULL),(200, NULL),(250, NULL),(500, NULL),
(510, NULL),(535, NULL),(580, NULL),(600, NULL),(650, NULL);

処理

  1. TRANSACTION を開始
  2. 在庫商品の中から売れ残っているレコードを SELECT ・・・ FOR UPDATE
  3. ロックした行から指定個数を UPDATE
  4. COMMI を実行

対応

上記処理の２の「在庫商品の中から売れ残っているレコードを SELECT ・・・ FOR UPDATE」でロックするレコードを絞りこむ（ただ一意にはできなかった）

在庫で売れ残っているレコードをすべて SELECT していたので、とりあえず 100レコード だけロックするようにしました

対応後の処理
  1. 在庫で売れ残っているレコードを取得(とりあえず100レコード)
  2. TRANSACTION を開始
  3. 在庫から売り出す商品数分の在庫IDを配列へ退避
  4. 在庫商品の中から売れ残っているレコードを SELECT ・・・ FOR UPDATE
    • 退避していた在庫IDを Where句 で指定
    • 他のリクエストで先取されていたら もう一度 SELECT ・・・ FRO UPDATE をやり直す
  5. ロックした行から指定個数を UPDATE
  6. COMMI を実行
ユート
ユート

Webプログラマ兼ブロガー | プログラミング基礎知識を発信｜Webプログラマ歴は10年以上｜JavaScript,PHP.HTML | 「人生の時間は有限。悪いことをしている時間はない」
→プロフィール

2023年8月5日システムMySQL,PHP

Posted by ユート