

MySQLでUPDATEを行っている箇所でデッドロックが発生した原因の調査を行っています。  
排他処理は楽観ロックで行い、トランザクションは使用せずUPDATEの実行直後にCOMMITを行い即座にロックを解放しているため、何故デッドロックとなったのか、原因が見当がつかず困り果てています。  
ご助力お願いします。

  * MYSQLバージョン：5.7.12
  * 分離レベル：REPEATABLE READ
  * オートコミット：OFF

**追記:**

user_idごとに並列に回す日次バッチ内で発生しており、頻度は週に1度程度です。

* * *

### テーブルレイアウト

[code]

    CREATE TABLE `testtable` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) unsigned NOT NULL ,
      `image_id` bigint(20) unsigned NOT NULL ,
      `status` tinyint(4) DEFAULT '0',
     
      `created_at` datetime NOT NULL,
      `updated_at` datetime NOT NULL,
      `deleted_at` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' ,
     
      PRIMARY KEY (`id`),
      KEY `index_image_id` (`image_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=217159 DEFAULT CHARSET=utf8mb4;
    
[/code]

### SHOW ENGINE INNODB STATUS出力のLATEST DETECTED DEADLOCKの抜粋

[code]

    *** (1) TRANSACTION:
    TRANSACTION 349587391, ACTIVE 29 sec starting index read
    mysql tables in use 1, locked 1
    LOCK WAIT 9 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 2
    
    MySQL thread id 4004274, OS thread handle 47407848781568, query id 5622588628 **.**.**.** prod_buildee updating
    UPDATE `testtable` SET `status` = '1',  WHERE `user_id` = '24914' AND `deleted_at` = '1970-01-01 00:00:00' AND
     `image_id` in ('23345','23567','23987','23456','23123','23654','23783')
    
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 27692 page no 638 n bits 0 index PRIMARY of table `testdb`.`testtable` trx 
    id 349587391 lock_mode X locks rec but not gap waiting
    Record lock, heap no 30 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
    
    
[/code]

[code]

    *** (2) TRANSACTION:
    
    TRANSACTION 349587390, ACTIVE 0 sec fetching rows
    mysql tables in use 1, locked 1
    LOCK WAIT 751 lock struct(s), heap size 1136, 83407 row lock(s)
    MySQL thread id 4004316, OS thread handle 47407861106432, query id 5622588590 **.**.**.** prod_buildee updating
    
    UPDATE `testtable` SET `status` = '2', `updated_at` = '2021/01/14 03:03:32', `integration_service_id` = '2'
     WHERE `user_id` = '23500' AND `status` = '1'  AND `deleted_at` = '1970-01-01 00:00:00' LIMIT 50
    
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 27692 page no 638 n bits 0 index PRIMARY of table `testdb`.`testtable` trx id 349587390 lock_mode X
    Record lock, heap no 2 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
    
[/code]


CC BY-SA 4.0
2021-03-18T12:13:48.680
74744
2021-04-10T12:42:14.073
2021-03-18T16:29:15.367
3060
44395
question
mysql
MySQLでのトランザクション未使用でUPDATE文でのデッドロックの発生


複数のレコードを更新する UPDATE を同時に実行した場合はデッドロックが発生し得ます。

たとえばクライアントAが、1,2,3,4... の順に更新し、クライアントBが 4,3,2,1... の順に更新すればデッドロックになります。

<https://zenn.dev/tmtms/articles/202103-mysql-deadlock>


CC BY-SA 4.0
2021-03-20T14:18:17.467
74790
2021-03-21T10:45:17.643
2021-03-21T10:45:17.643
3249
3249
74744
answer


`user_id`にインデックスをはるか、`user_id`と`image_id`に複合indexをはることで解決されるはずです。transaction1と2で同じ`image_id`のレコードを取り合っている可能性が高いです。


CC BY-SA 4.0
2021-03-23T15:08:25.637
74843
2021-03-23T15:08:25.637
39410
74744
answer


2つめの transaction が悪さをしているように思います。

[code]

    WHERE `user_id` = '23500' AND `status` = '1'  AND `deleted_at` = '1970-01-01 00:00:00'
    
[/code]

で更新対象の絞り込みをかけていますが、この条件は、 `testtable` のどのインデックス(`image_id` しかないですが)にも hit
しません。結果、 MySQL
はこのクエリによる更新対象のテーブルを見つけるためにはすべてのレコードを読み込む必要があり、また、データ不整合を防ぐため、それら読み込んだレコードは、
transaction 2 が完了するまで lock されていきます。

user_id に対して index
を張れば、どちらの更新クエリに対しても、そのインデックスを基準にロックをかけられるようになるので、おそらく、この問題は解消すると思います。


CC BY-SA 4.0
2021-04-10T12:42:14.073
75198
2021-04-10T12:42:14.073
754
74744
answer