Q&A
単に、「トランザクションを40秒も止めてしまったがために、デッドロックと誤認された」という可能性はないのでしょうか？
前提
こちらの３つのテーブルがあります。
my_fruits : フルーツテーブル
my_tags : タグテーブル
my_tag_holders : リレーションテーブル
そして以下５行目にコメントアウトしてあるように fruits_name の重複を避けるのが本質問の目的です。
SQL
1-- フルーツテーブル 2CREATE TABLE my_fruits ( 3 `ID` int AUTO_INCREMENT, 4 `count_likes` int(100) DEFAULT 0, 5 `fruits_name` varchar(1000) not null, # この重複を避けることが目的（ただし1000文字なのでユニーク制限はしません） 6 PRIMARY KEY(`ID`), 7 INDEX idx_fruits_name_01 (fruits_name)); 8 9INSERT INTO my_fruits (`count_likes`, `fruits_name`) 10VALUES 11(10, 'フルーツ１'), 12(56, 'フルーツ２'), 13(36, 'フルーツ３'); 14 15-- タグテーブル 16CREATE TABLE my_tags ( 17 `ID` int AUTO_INCREMENT, 18 `tag_kind_id` int default 1, 19 `tag_name` varchar(100), 20 PRIMARY KEY(`ID`), 21 UNIQUE u_my_tags_01 (`tag_kind_id`, `tag_name`)); 22 23INSERT INTO my_tags 24(`tag_kind_id`, `tag_name`) 25VALUES 26(1, '赤'), 27(1, '青'), 28(1, '緑'), 29(1, '黄'), 30(1, '白'); 31 32-- リレーションテーブル 33CREATE TABLE my_tag_holders ( 34 `fruits_ID` int, 35 `tags_ID` int, 36 PRIMARY KEY (`fruits_ID`, `tags_ID`), #「フルーツとタグの同じ組み合わせはない」という制限 37 INDEX idx_my_tag_holders_01 (`tags_ID`, `fruits_ID`), 38 CONSTRAINT fk_my_tag_holders_01 FOREIGN KEY (`fruits_ID`) REFERENCES my_fruits(`ID`), 39 CONSTRAINT fk_my_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES my_tags(`ID`) ); 40 41INSERT INTO my_tag_holders 42(`fruits_ID`, `tags_ID`) 43VALUES 44(1, 1),(1, 2), 45(2, 2),(2, 4),(2, 5), 46(3, 3);
実現したいこと
該当のソースコードの実行に際してfruits_nameの重複を避けたいです。
そしてデッドロックの原因と解決策を知りたいです。
（特に、なぜインデックスを貼ることでデッドロックが起こるのか？が大いに疑問です。）
発生している問題・エラーメッセージ
１回目に該当のソースコードを実行すると、var_dump($result); は次の結果を出力します。
php
1array(2) { 2 ["status"]=> 3 string(8) "pdoerror" 4 ["message"]=> 5 string(111) "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction" 6}
２回目の実行は次のように問題ありません。
php
1array(2) { 2 ["status"]=> 3 string(8) "inserted" 4 ["fruits_id"]=> 5 int(4) 6}
該当のソースコード
以下を２回実行します。
１回目と２回目で$with_sleepを切り替えて、「１人目の処理中に２人目の処理が介在した」という状況を確認します。
つまり「既存確認したAさんとBさんがいずれも同じfruits_nameを投稿したとき」に、fruits_nameが重複しないことを確認する狙いです。
php
1$with_sleep = false; // １回目だけ sleep させて、その最中に２回目を実行する 2$fruits_name = 'フルーツ4'; 3$result = test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ); 4var_dump($result); 5 6function test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ){ 7 8 $result = ['status'=>'error']; 9 try { 10 11 // トランザクション 12 $dbh = db_open(); 13 $dbh->beginTransaction(); 14 15 // 既存確認 16 $sql = " 17 SELECT * 18 FROM my_fruits 19 WHERE fruits_name = :fruits_name 20 FOR UPDATE 21 ;"; 22 $stmt = $dbh->prepare( $sql ); 23 $stmt->bindValue(':fruits_name', $fruits_name); 24 $flag = $stmt->execute(); 25 if( $flag ) { 26 27 // あれば既存IDを取得 28 $data = $stmt->fetch(); 29 $result = [ 30 'status' => 'exists', 31 'fruits_id' => (int)$data['ID'] 32 ]; 33 34 } else { 35 throw new Exception("SELECTで失敗しました"); 36 } 37 38 // １回目だけ sleep させて、その最中に２回目を実行する 39 if ( $with_sleep ) sleep(40); 40 41 // なければINSERT 42 if ( $data === false ) { 43 44 $sql = " 45 INSERT INTO my_fruits ( fruits_name ) 46 VALUES( :fruits_name ) 47 ;"; 48 $stmt = $dbh->prepare( $sql ); 49 $stmt->bindValue(':fruits_name', $fruits_name); 50 $flag = $stmt->execute(); 51 if( $flag ) { 52 $result = [ 53 'status' => 'inserted', 54 'fruits_id' => (int)$dbh->lastInsertId() 55 ]; 56 } else { 57 throw new Exception("INSERTで失敗しました"); 58 } 59 60 } 61 62 $dbh->commit(); 63 64 } catch (PDOException $e) { 65 $result = [ 66 'status' => 'pdoerror', 67 'message' => $e->getMessage() 68 ]; 69 } catch (Exception $e) { 70 $result = [ 71 'status' => 'error', 72 'message' => $e->getMessage() 73 ]; 74 $dbh->rollback(); 75 } finally { 76 $dbh = null; 77 } 78 return $result; 79} 80 81
試したこと（テーブルロック）
「beginTransactionとSELECT...FOR UPDATE」でなく「LOCK TABLESとSELECT」を利用すると１回目も２回目もデッドロックは起こらず、次のように２回目をexistsにできました。
しかしテーブルロックしてしまうと閲覧もできませんし他の待ちも生じてしまうので、これを解決策として採用したくはありません。
１回目
php
1array(2) { 2 ["status"]=> 3 string(8) "inserted" 4 ["fruits_id"]=> 5 int(4) 6}
２回目
php
1array(2) { 2 ["status"]=> 3 string(6) "exists" 4 ["fruits_id"]=> 5 int(4) 6}
試したこと（インデックス削除）
なぜかfruits_nameのインデックスを削除したところ、上記テーブルロックした場合と同様に解決が見られました。
しかし削除してしまうと検索に不向きなので、これを解決策として採用したくはありません。
そしてフルテキストインデックスですと検索に数秒かかるので、通常のインデックスを使いたいです。
試したこと（ロック状況の確認）
コードの過程でロック状況の確認をしようと思いましたが、レンタルサーバーのためその権限がありませんでした。（ユーザーに権限を付与することもできませんでした。）
試したこと（sleep時間の短縮）
「質問への追記・修正の依頼」にてmaisumakun様よりご提案頂きsleep(3)で試してみたのですが、これも同様のデッドロックでした。
試したこと（READ COMMITTED）
似た状況 を見つけました。いわく、
・SELECTが空振りするとギャップロックになる
・同時に複数のギャップロックが発生するとお互いをブロックしデッドロック状態となる
そこで ギャップロックの回避方法 を見つけ、次のように // READ COMMITTED に変更 する１行を加えてみました。
結果、デッドロックはなくなりましたが、普通に 'フルーツ4' は重複してしまいました。
php
1function test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ){ 2 3 $result = ['status'=>'error']; 4 try { 5 6 // DB接続 7 $dbh = db_open(); 8 9 // READ COMMITTED に変更 10 $dbh->query( "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;" ); 11 12 // トランザクション 13 $dbh->beginTransaction(); 14 15 /* 以降は同じ */
ツールのバージョンなど
それぞれ次のバージョンです。
PHP 8.0
MySQL 5.7.2
宜しくお願い致します。
補足
該当のソースコードはフルーツのみの内容になっていますが、実際にはフルーツとタグの投稿も想定されます。そのため一方が失敗した場合に備えてロールバックを可能にしたく、その意味でもbeginTransactionは必須と考えています。
そのようなことがあるとは思いもしませんでした。
ところで「処理時間が長いとデッドロックと誤認される」というご主張は何に由来しますか？MySQLのマニュアルを洗っているのですが見つけることができません。
また仮にそうだとしても「インデックスを貼るとデッドロックになること」（逆に言えばインデックスを削除すればデッドロックにならないこと）の説明にはならないと思うのですが、この点はいかがでしょうか？
いつもご指導ありがとうございます。誠に僭越ながら反論する形になってしまいましたが、maisumakun様のご見識はほぼ正解なので、できれば引き続きよろしくお願いいたします。
例に出されるような単純なケースならともかく関係が複雑になるとデッドロックを厳密に検出することはできないしできたとしてもその検出に無駄な時間がかかるので、
長時間(数秒)ロックしっぱなしの場合は時間を区切ってエラーとみなすタイムアウトはMySqlでもOracleでもPostrasqlでもSQL Serverでもやってますよ。
インデックスの影響でselectに無駄な時間かかっているんじゃねと思われてるのでは。
> 「インデックスを貼るとデッドロックになること」（逆に言えばインデックスを削除すればデッドロックにならないこと）の説明にはならないと思うのですが、この点はいかがでしょうか？
テーブルロックと行ロックは別系統の処理が行われます。インデックスがないことでテーブル単位のロックになって、行ロックのタイムアウトによりデッドロックとみなすルーチンに引っかからなくなる、という流れと思われます。
やはりsleep時間に関わらないようで、3秒にしてもデッドロックでした…
回答1件
0
テーブルロックしてしまうと閲覧もできませんし他の待ちも生じてしまうので、これを解決策として採用したくはありません。
LOCK IN SHARE MODEを検討してみては？
投稿2022/08/08 09:27
総合スコア25195
いつも新たなワードをありがとうございます。
FOR UPDATE の部分を LOCK IN SHARE MODE にし、ついでに sleep(3) と短めで試しましたが、やはり同じデッドロックのエラーでした。
非ユニークカラムの重複を回避するなんて大してレアな処理とは思えないのですが、解決策がネットに落ちていないのも不思議です…
> 非ユニークカラムの重複を回避するなんて大してレアな処理とは思えないのですが
すでに過去にもコメントしていますが、インデックスも貼れないぐらいに長いものをユニークにしなければならない、という条件自体がレアかと思います。
> 非ユニークカラムの重複を回避するなんて大してレアな処理とは思えないのですが
この件に関して、私もmaisumakunさんに同意です。
謂わば、SNSでの投稿内容について1語1句同じかどうかをチェックするような現実的でないものです。
仮に重複したとして、一文字だけ変えればOKになる事に意味があるとは思えませんし。
すみません、変なコメントしたせいで盛り上がってしまいましたね。
それはそれとして、質問のデッドロックも気になるので何かあればまたよろしくお願い致します。
あなたの回答
tips
プレビュー
