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

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

ただいまの
回答率

88.92%

mysql5.6でキー指定のUPDATE文を連続で実行した時にデッドロックが発生

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 196

ryau

score 2

前提・実現したいこと

  • 事象
    jmeterを使ってとあるAPIの負荷試験を行いました。
    APIの中で以下のSQLを実行しています。
    処理として、すでに存在している主キーが指定されるため、実際には1レコードに対して、連続でupdateがかかる挙動になります。
    この状態で、jmeterでは分間で数千、数万と負荷をかけた時にデッドロックが発生してしまいます。

  • 知りたいこと
    この原因とデッドロックの解消方法

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

CDbCommand はSQL文を実行できません。:SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction. The SQL statement executed was: ...略

実行SQL

INSERT INTO table_name (id,date_name,product_id,num,created_at,updated_at) VALUES(99999,'2020-07-12',11111,1000,'2020-07-12 08:45:08','2020-07-12 13:45:23') ON DUPLICATE KEY UPDATE num = VALUES(num),updated_at = VALUES(updated_at)

UPDATE対象のテーブル定義

CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_name` date NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `num` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`,`date_name`),
  UNIQUE KEY `table_name_IX1` (`date_name`,`product_id`),
  KEY `table_name_IX2` (`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10248 DEFAULT CHARSET=utf8

補足情報(FW/ツールのバージョンなど)

mysqlバージョン

mysql  Ver 14.14 Distrib 5.6.14, for Linux (x86_64) using  EditLine wrapper
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

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

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yambejp

    2020/07/10 18:37

    トランザクションはどうしていますか?

    キャンセル

  • ryau

    2020/07/10 18:44

    APIのリクエストごとにトランザクションを使用しています。

    キャンセル

  • sazi

    2020/07/10 20:48

    検証後の当該のテーブルにnsertで追加を行って、IDを確認してみて下さい。
    欠番は生じていますか?

    キャンセル

  • KOZ6.0

    2020/07/11 05:27

    ORACLE の場合、ITL というロックのための領域が不足してデッドロックが発生する場合があります。
    https://www.magata.net/memo/index.php?Oracle%A5%C7%A5%C3%A5%C9%A5%ED%A5%C3%A5%AF%A4%CB%A4%C4%A4%A4%A4%C6
    MySQL に、似た機能はないでしょうか?

    キャンセル

回答 2

checkベストアンサー

0

数万回APIを叩いているのであればその数セッションが開かれるので
負荷が高いかもしれません
おなじセッション内の同じトランザクションで処理すれば数万回の作業でも
たかが知れてます

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

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

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

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/07/10 19:55 編集

    たとえばこんなテスト
    create table tbl (id int primary key,val int);
    insert into tbl values
    (1,100),(2,200),(3,300);

    これにプロシージャを設定
    drop procedure if exists proc;
    delimiter //
    create procedure proc(IN num int,IN max int,IN done tinyint)
    begin
    start transaction;
    set @count=1,@id=num,@max=max,@done=done;
    while @count<=@max do
    update tbl set val=val+1 where id=@id;
    set @count=@count + 1;
    end while;
    if @done=1 then
    commit;
    else
    rollback;
    end if;
    end
    //
    delimiter ;

    仕様:procは引数を3つもち
    第1引数はtblのidを指定
    第2引数は繰り返し回数を指定
    第3引数は1ならcommit、そうでなければrollback

    キャンセル

  • 2020/07/10 19:58 編集

    テスト1
    call proc(1,100000,1);
    マシンにもよりますが5-10秒でid=1のvalが100100になります

    テスト2
    call proc(2,100000,0);
    マシンにもよりますが5-10秒待ってロールバックされるので元のままです

    キャンセル

  • 2020/07/10 20:39

    命題のテーブル見ましたがprimaryの取り方も変ですし
    複合ユニークしたキーの片方にインデックスを更にはったり
    いまいち効率的な運用がされていないようにみえます

    キャンセル

0

質問の前に、
date, datetime の値はシングルクォーティングしないとエラーになるのでは?

INSERT ...
VALUES(99999,2020-07-12,11111,1000,2020-07-12 08:45:08,2020-07-12 13:45:23)
              ↓
VALUES(99999,'2020-07-12',11111,1000,'2020-07-12 08:45:08','2020-07-12 13:45:23')
 ...

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

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

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

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2020/07/10 18:33

    SQL修正しました。記載ミスで実際のクエリに問題はありません。

    キャンセル

  • 2020/07/10 19:58

    もっとINSERT文がどんな使われているのか周りの部分も載せた方が適切なコメントが付きそうです。
    INSERT毎にCOMMIT できる案件でしたら、パフォーマンスは落ちるけど、INSERT毎にCOMMITしてみては?

    キャンセル

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

  • ただいまの回答率 88.92%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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