MySQLでわざとデッドロックを発生させる方法!解消方法も解説!

  • 2019年8月11日
  • 2022年10月22日
  • SQL
  • 4887view
SQL

Mysqlを使っていてこんな疑問を持ったことはありませんか?

 

・「デッドロック起こすな!」と言うけど、そもそもどうやってデッドロックを引き起こすの?

・デッドロックが起きたらどうなるの(MySQLの場合)?

・デッドロックをどうやって起こさないようにするの?

 

今回は、上記のような3つの疑問に答えていきます。

 

プログラミング・実務経験がある方も必見!
空いた時間をスキルアップ・収入増に使ってみませんか?

副業で仕事を取る方法を教えてくれる!
プログラミングスクール4選の詳細

 

デッドロックとは?

 

MySQLの公式サイトにデッドロックについてこのように書かれています。

(デットロックとは)さまざまなトランザクションが進行できない状況 (それぞれが、他方が必要とするロックを保持しているため)。リソースが利用可能になるまで両方のトランザクションが待機しているため、どちらもそれが保持しているロックを解放しません。

 

 

(UPDATE や SELECT … FOR UPDATE などのステートメントを通じて) トランザクションが複数のテーブル内の行を反対の順にロックすると、デッドロックが発生することがあります。

 

出典

14.2.11 デッドロックの対処方法

 

つまり、並列に複数の処理が動いていて、ロックの順番を考慮せずに同じテーブルのデータ(今回の解説では行単位)をロックしたりするとデッドロック状態になることがあります。

 

 

 

MySQLではデッドロックが起きるとどんな挙動をするの?

 

結論から言うと、MySQLは、デッドロックになってしまったトランザクションは、トランザクションは停止してロールバックします。

よって、デッドロックのまま処理がずっと進まないという、最悪の自体を防ぐことは可能です。

 

実際にデットロックが出ると、以下のようなエラーが出力されます。

「Deadlock found when trying to get lock; try restarting transaction」

 

 

デッドロックを引き起こす方法

実際にSQLを書いてデッドロックを引き起こしてみます

 

 

デッドロックを引き起こすための環境とデータ準備

 

テーブルを作成する

 

■本の在庫管理用のテーブル作成

CREATE TABLE `m_books` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL COMMENT '名前',
  `count` int(10) DEFAULT '0' COMMENT '在庫',
  `created` datetime NOT NULL,
  `modified` datetime DEFAULT NULL,
  `del` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`created`),
  UNIQUE KEY `uniqe` (`id`,`created`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 

■(本の)ジャンル用のテーブル

CREATE TABLE `m_genre` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL COMMENT 'ジャンル名',
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `del` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

データを入れるinsert文

データは、テーブルごとに1レコード用意する。

INSERT INTO `m_books` (`id`, `name`, `count`, `created`, `modified`, `del`)
VALUES
	(1, '本1', 10, '0000-00-00 00:00:00', NULL, 0);

INSERT INTO `m_genre` (`id`, `name`, `created`, `modified`, `del`)
VALUES
	(1, '始まり', '0000-00-00 00:00:00', NULL, 0);

 

以上でデータの準備は終わりです。

 

2つのスクリプトを用意する

タスクA
begin;
update m_books set count = 2 where id = 1;
update m_genre set `name` = "test2" where id = 1;
commit;

タスクB
begin;
update m_genre set `name` = "test" where id = 1;
update m_books set count = 1 where id = 1; #デッドロック
commit;

 

 

実行順番に注意してスクリプトを事項する

 

以下の画像を参考に①から順番に処理を実行します。

 

⑥のところでデッドロックを検出します。

 

デッドロックを検出すると、MySQLから以下のエラーがでて。タスクA,Bのupdateは反映されませんでした。

「Deadlock found when trying to get lock; try restarting transaction」

 

 

デッドロックを起こさないためには?

今回の例では、タスクA,Bでテーブルのデータの更新順番が逆になっていました。

その結果、デッドロックになりました。

 

この解決方法は、updateの順番を決めることです。

そうすれば、デッドロックにならずにすみます。

 

具体的に言うと、タスクA,B共に以下のような処理順番にすればよいです。

以下の例では、タスクA,B共にm_books、m_genreの順番にテーブルの行を更新しています。

 

begin;

update m_books set count = [代入する値] where id = 1;

update m_genre set `name` = [代入する文字列] where id = 1;

commit;

 

デッドロックが起きたときの調査方法

コマンド「SHOW ENGINE INNODB STATUS」を実行します。

最近のデッドロックの原因を特定に役立ちします。

 

実際に「SHOW ENGINE INNODB STATUS」を実行すると、以下のようなログが出てきます。

「LATEST DETECTED DEADLOCK」の部分を確認すると、どのsqlでデッドロックが発生したのか分かるので、調査する際に大変役立ちます。

------------------------
LATEST DETECTED DEADLOCK
------------------------

(2) TRANSACTION:
TRANSACTION 4340031, ACTIVE 65 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 28, OS thread handle 0x700001c0f000, query id 874 localhost 127.0.0.1 root updating
update m_genre set `name` = "test2" where id = 1

 

プログラミング・実務経験がある方も必見!
空いた時間をスキルアップ・収入増に使ってみませんか?

副業で仕事を取る方法を教えてくれる!
プログラミングスクール4選の詳細

 

プログラミングを短期間で学ぶ方法

プログラミングを短期間で効率的に学ぶ方法を紹介します。

それは、以下のようなサービス、プログラミングスクールを利用することです。

プログラミングスキルを習得している人でも機械学習の習得は、難しいため、独学で迷うよりは、プロに教えてもらったほうが遥かに最短経路で身につけることができます!

 

スクール名 受講形式 サポート体制 値段の安さ こんな人にオススメ!
.Pro(ドットプロ) 教室 ★★★★★ ★★ ・AIだけでなくWEB開発技術も対面授業を通して直接講師に教えてもらいたい
・教室で仲間と一緒に学びたい!
Aidemy Premium Plan オンライン ★★★★ ★★ ・画像認識・自然言語処理・AIアプリ開発・AIとWEBを組み合わせたシステムなど広範囲の知識を身に着けたい!
・教育訓練給付制度を活用して最大70%引きで学びたい!
AIジョブカレ 教室
オンライン
★★★★ ★★★★★

・授業形式でAI実務家から直接AIの実務について教えてもらいたい!

・SQLからデータを取り出して機械学習をする。
・10万円台と低価格で教えてもらいたい!
・ゆっくりペースで学びたい!

テックアカデミー オンライン ★★★★ ★★★★ ・最短4週間!短期間で学び終えるほど料金が安い!
・講座数が多いためAI以外のスキルも学びたい!
ヒューマンアカデミー オンライン ★★★ ★★★★ ・比較的リーズナブルな値段でPython基礎から学びたい
・就職内定率90.7% 提携企業115社以上という就職実績があるスクールで学びたい
Udemy オンライン ★★ ★★★★★ ・一度購入した講座は、Udemyがつぶれない限りずっと見ることができる
・1講座あたり2000円台(セール期間中)で購入して学びたい!

 

まとめ

デッドロックは、いつも同じタイミングで発生するわけでないので、原因を発見しづらい問題と言えます。

設計段階から、デッドロックが起きないように、テーブルの読み込み順番を考慮しておく必要があると言えます。

 

プログラミング・実務経験がある方も必見!
空いた時間をスキルアップ・収入増に使ってみませんか?

副業で仕事を取る方法を教えてくれる!
プログラミングスクール4選の詳細

最新情報をチェックしよう!
>プログラミングスクール検索・比較表サイト

プログラミングスクール検索・比較表サイト

ワンクリック、さらに詳細に条件を指定してプログラミングスクールの検索ができます。さらに比較表により特徴を細かく比較できる!

CTR IMG