質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
87.20%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

受付中

MySQLでインデックスを貼るとデッドロックが発生してしまう

nikuatsu
nikuatsu

総合スコア172

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

1回答

3リアクション

0クリップ

462閲覧

投稿2022/08/07 18:48

編集2022/08/08 17:11

前提

こちらの3つのテーブルがあります。

my_fruits : フルーツテーブル
my_tags : タグテーブル
my_tag_holders : リレーションテーブル

そして以下5行目にコメントアウトしてあるように fruits_name の重複を避けるのが本質問の目的です。

SQL

-- フルーツテーブル CREATE TABLE my_fruits ( `ID` int AUTO_INCREMENT, `count_likes` int(100) DEFAULT 0, `fruits_name` varchar(1000) not null, # この重複を避けることが目的(ただし1000文字なのでユニーク制限はしません) PRIMARY KEY(`ID`), INDEX idx_fruits_name_01 (fruits_name)); INSERT INTO my_fruits (`count_likes`, `fruits_name`) VALUES (10, 'フルーツ1'), (56, 'フルーツ2'), (36, 'フルーツ3'); -- タグテーブル CREATE TABLE my_tags ( `ID` int AUTO_INCREMENT, `tag_kind_id` int default 1, `tag_name` varchar(100), PRIMARY KEY(`ID`), UNIQUE u_my_tags_01 (`tag_kind_id`, `tag_name`)); INSERT INTO my_tags (`tag_kind_id`, `tag_name`) VALUES (1, '赤'), (1, '青'), (1, '緑'), (1, '黄'), (1, '白'); -- リレーションテーブル CREATE TABLE my_tag_holders ( `fruits_ID` int, `tags_ID` int, PRIMARY KEY (`fruits_ID`, `tags_ID`), #「フルーツとタグの同じ組み合わせはない」という制限 INDEX idx_my_tag_holders_01 (`tags_ID`, `fruits_ID`), CONSTRAINT fk_my_tag_holders_01 FOREIGN KEY (`fruits_ID`) REFERENCES my_fruits(`ID`), CONSTRAINT fk_my_tag_holders_02 FOREIGN KEY (`tags_ID`) REFERENCES my_tags(`ID`) ); INSERT INTO my_tag_holders (`fruits_ID`, `tags_ID`) VALUES (1, 1),(1, 2), (2, 2),(2, 4),(2, 5), (3, 3);

実現したいこと

該当のソースコードの実行に際してfruits_nameの重複を避けたいです。

そしてデッドロックの原因と解決策を知りたいです。
(特に、なぜインデックスを貼ることでデッドロックが起こるのか?が大いに疑問です。)

発生している問題・エラーメッセージ

1回目に該当のソースコードを実行すると、var_dump($result); は次の結果を出力します。

php

array(2) { ["status"]=> string(8) "pdoerror" ["message"]=> string(111) "SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction" }

2回目の実行は次のように問題ありません。

php

array(2) { ["status"]=> string(8) "inserted" ["fruits_id"]=> int(4) }

該当のソースコード

以下を2回実行します。

1回目と2回目で$with_sleepを切り替えて、「1人目の処理中に2人目の処理が介在した」という状況を確認します。

つまり「既存確認したAさんとBさんがいずれも同じfruits_nameを投稿したとき」に、fruits_nameが重複しないことを確認する狙いです。

php

$with_sleep = false; // 1回目だけ sleep させて、その最中に2回目を実行する $fruits_name = 'フルーツ4'; $result = test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ); var_dump($result); function test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ){ $result = ['status'=>'error']; try { // トランザクション $dbh = db_open(); $dbh->beginTransaction(); // 既存確認 $sql = " SELECT * FROM my_fruits WHERE fruits_name = :fruits_name FOR UPDATE ;"; $stmt = $dbh->prepare( $sql ); $stmt->bindValue(':fruits_name', $fruits_name); $flag = $stmt->execute(); if( $flag ) { // あれば既存IDを取得 $data = $stmt->fetch(); $result = [ 'status' => 'exists', 'fruits_id' => (int)$data['ID'] ]; } else { throw new Exception("SELECTで失敗しました"); } // 1回目だけ sleep させて、その最中に2回目を実行する if ( $with_sleep ) sleep(40); // なければINSERT if ( $data === false ) { $sql = " INSERT INTO my_fruits ( fruits_name ) VALUES( :fruits_name ) ;"; $stmt = $dbh->prepare( $sql ); $stmt->bindValue(':fruits_name', $fruits_name); $flag = $stmt->execute(); if( $flag ) { $result = [ 'status' => 'inserted', 'fruits_id' => (int)$dbh->lastInsertId() ]; } else { throw new Exception("INSERTで失敗しました"); } } $dbh->commit(); } catch (PDOException $e) { $result = [ 'status' => 'pdoerror', 'message' => $e->getMessage() ]; } catch (Exception $e) { $result = [ 'status' => 'error', 'message' => $e->getMessage() ]; $dbh->rollback(); } finally { $dbh = null; } return $result; }

試したこと(テーブルロック)

beginTransactionSELECT...FOR UPDATE」でなく「LOCK TABLESSELECT」を利用すると1回目も2回目もデッドロックは起こらず、次のように2回目をexistsにできました。

しかしテーブルロックしてしまうと閲覧もできませんし他の待ちも生じてしまうので、これを解決策として採用したくはありません。

1回目

php

array(2) { ["status"]=> string(8) "inserted" ["fruits_id"]=> int(4) }

2回目

php

array(2) { ["status"]=> string(6) "exists" ["fruits_id"]=> int(4) }

試したこと(インデックス削除)

なぜかfruits_nameのインデックスを削除したところ、上記テーブルロックした場合と同様に解決が見られました。

しかし削除してしまうと検索に不向きなので、これを解決策として採用したくはありません。
そしてフルテキストインデックスですと検索に数秒かかるので、通常のインデックスを使いたいです。

試したこと(ロック状況の確認)

コードの過程でロック状況の確認をしようと思いましたが、レンタルサーバーのためその権限がありませんでした。(ユーザーに権限を付与することもできませんでした。)

試したこと(sleep時間の短縮)

「質問への追記・修正の依頼」にてmaisumakun様よりご提案頂きsleep(3)で試してみたのですが、これも同様のデッドロックでした。

試したこと(READ COMMITTED)

似た状況 を見つけました。いわく、

・SELECTが空振りするとギャップロックになる
・同時に複数のギャップロックが発生するとお互いをブロックしデッドロック状態となる

そこで ギャップロックの回避方法 を見つけ、次のように // READ COMMITTED に変更 する1行を加えてみました。

結果、デッドロックはなくなりましたが、普通に 'フルーツ4' は重複してしまいました。

php

function test_insert_on_select_for_update_ver_pdo( $fruits_name, $with_sleep ){ $result = ['status'=>'error']; try { // DB接続 $dbh = db_open(); // READ COMMITTED に変更 $dbh->query( "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;" ); // トランザクション $dbh->beginTransaction(); /* 以降は同じ */

ツールのバージョンなど

それぞれ次のバージョンです。

PHP 8.0
MySQL 5.7.2

宜しくお願い致します。

補足

該当のソースコードはフルーツのみの内容になっていますが、実際にはフルーツとタグの投稿も想定されます。そのため一方が失敗した場合に備えてロールバックを可能にしたく、その意味でもbeginTransactionは必須と考えています。

DiningKitchen, kemusi, Maximillion👍を押しています

以下のような質問にはリアクションをつけましょう

  • 質問内容が明確
  • 自分も答えを知りたい
  • 質問者以外のユーザにも役立つ

リアクションが多い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

気になる質問をクリップする

クリップした質問は、後からいつでもマイページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

下記のような質問は推奨されていません。

  • 間違っている
  • 質問になっていない投稿
  • スパムや攻撃的な表現を用いた投稿

適切な質問に修正を依頼しましょう。

2022/08/07 19:40依頼された後にこの質問は修正されています

こちらの質問が他のユーザーから「問題・課題が含まれていない質問」という指摘を受けました。

maisumakun

2022/08/07 22:11

単に、「トランザクションを40秒も止めてしまったがために、デッドロックと誤認された」という可能性はないのでしょうか?
nikuatsu

2022/08/07 22:32

そのようなことがあるとは思いもしませんでした。 ところで「処理時間が長いとデッドロックと誤認される」というご主張は何に由来しますか?MySQLのマニュアルを洗っているのですが見つけることができません。 また仮にそうだとしても「インデックスを貼るとデッドロックになること」(逆に言えばインデックスを削除すればデッドロックにならないこと)の説明にはならないと思うのですが、この点はいかがでしょうか? いつもご指導ありがとうございます。誠に僭越ながら反論する形になってしまいましたが、maisumakun様のご見識はほぼ正解なので、できれば引き続きよろしくお願いいたします。
RiaFeed

2022/08/08 00:03 編集

例に出されるような単純なケースならともかく関係が複雑になるとデッドロックを厳密に検出することはできないしできたとしてもその検出に無駄な時間がかかるので、 長時間(数秒)ロックしっぱなしの場合は時間を区切ってエラーとみなすタイムアウトはMySqlでもOracleでもPostrasqlでもSQL Serverでもやってますよ。 インデックスの影響でselectに無駄な時間かかっているんじゃねと思われてるのでは。
maisumakun

2022/08/08 06:24 編集

> 「インデックスを貼るとデッドロックになること」(逆に言えばインデックスを削除すればデッドロックにならないこと)の説明にはならないと思うのですが、この点はいかがでしょうか? テーブルロックと行ロックは別系統の処理が行われます。インデックスがないことでテーブル単位のロックになって、行ロックのタイムアウトによりデッドロックとみなすルーチンに引っかからなくなる、という流れと思われます。
nikuatsu

2022/08/08 12:46

やはりsleep時間に関わらないようで、3秒にしてもデッドロックでした…

まだ回答がついていません

会員登録して回答してみよう

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
87.20%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。