【MySQL】最低限の??デッドロック対策メモ

今まであまり気にしてこなかったけどちょっと問題になったのでメモ
今回は同じ様な内容のバッチ処理を多重でいくつも回す実装としたため、更新するテーブル・レコードも同じになってデッドロックが発生しました

今回の原因となったのは以下

複数テーブルへの更新処理は処理するテーブルの順番をそろえること

処理A

begin;
update tableA set xxx where id = 1;
update tableB set xxx where id = 2;
commit;

処理B

begin;
update tableB set xxx where id = 2;
update tableA set xxx where id = 1;
commit;

デッドロック

処理A	処理B
update tableA set xxx where id = 1;		tableA.id = 1のロック取得
	update tableB set xxx where id = 2;	tableB.id = 2のロック取得
update tableB set xxx where id = 2;		処理BがtableB.id = 2のロック取得中のため待ちに入る
	update tableA set xxx where id = 1;	処理AがtableA.id = 1のロック取得中のため待ちに入る
処理Aと処理Bがそれぞれを待ち状態となるためデッドロックとなる

解決策(処理Bを修正)

begin;
update tableA set xxx where id = 1;
update tableB set xxx where id = 2;
commit;

更新するテーブルの順番を処理Aと合わせる

ロックをかける(かかる可能性がある)場合は、検索結果が0件となるwhereは避けること

tableCは、tableA_idとtableB_idを主キーとするテーブルとする

処理

begin;

// テーブルCへの処理　
select * from tableC where tableA_id = xxx and tableB_id = xxx for update;
if (結果なし) {
  insert into tableC (tableA_id, tableB_id) values (xxx, xxx);
} else {
  update tableC set xxx where tableA_id = xxx and tableB_id = xxx;
}

commit;

データ(tableC)

tableA_id	tableB_id
1	1

デッドロック

処理A	処理B
select * from tableC where tableA_id = 1 and tableB_id = 2 for update;		結果なし・・・tableC.tableA_id = 1のロック取得
	select * from tableC where tableA_id = 1 and tableB_id = 3 for update;	結果なし・・・tableC.tableA_id = 1のロック取得??
	insert into tableC values (tableA_id, tableB_id) values(1, 3);	処理AがtableC.tableA_id = 1のロック取得中のため待ちに入る
insert into tableC values(tableA_id, tableB_id) values(1,2);		処理BがtableC.tableA_id = 1のロック取得中のため待ちに入る??
処理Bは成功 処理Bは「Deadlock found when trying to get lock; try restarting transaction」エラー

上記のような挙動となる
イマイチ何が起きているのか理解できていないのだが、上記ではギャップロックが発生するため意図しないレコードのロックを取得しようとしていることが原因みたい。
なので、すっきりしないけどギャップロックを回避することで対応

対応策

insert into tableC (tableA_id, tableB_id) values (xxx, xxx) on duplicate key update xxx;

空振りを避けるため、on duplicate key updateを使っておかしなロックを取得しないように修正することで対応しました

外部キーが貼ってあるレコードをinsertすると親レコードにもロックがかかることを意識すること

【MySQL】外部キー制約とロックとデッドロックについて - とりあえずphpとか
ここに今回書こうとしていたことを書いてた・・・

さいごに
ギャップロックについて調べていたら「ネクストキーロック」というのもキーワードのようだが
今回は問題になっていないのでまた今度調べてみようと思います

後者の2つを見るとinsertするときにダメみたい
もうちょっと理解しないとダメだな・・・

以上です