MySQL デッドロック対処

MySQLのデッドロック対処おまけでギャップロック

寄稿しました。

今回はトランザクションでよくありがちなデッドロックのご紹介。

たすきがけのデッドロック

よくデッドロックはなんぞや？といった時に提示されるパターンです。

CREATE TABLE a_table(
	a_id       INT UNSIGNED PRIMARY KEY,
	point      INT UNSIGNED,
    value      VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO a_table(a_id, point, value) VALUES
	(1, 100, "a");
	



CREATE TABLE b_table(
	b_id       INT UNSIGNED PRIMARY KEY,
	point      INT UNSIGNED,
    value      VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO b_table(b_id, point, value) VALUES
	(1, 100, "a");

解決には？

テーブルへの書き込みの順番を揃えてあげることで解決です！

簡単ですね！

外部キー制約によるデッドロック

外部キー制約があるテーブルはINSERT時に親テーブルに共有ロックがかかります。

CREATE TABLE parent_job(
	job_id       INT UNSIGNED PRIMARY KEY,
	name         VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO parent_job
	(job_id, name)
	VALUES
	(1, "勇者");
	


CREATE TABLE child_member(
	user_id  INT UNSIGNED PRIMARY KEY,
	name     VARCHAR(255) NOT NULL,
	job_id   INT UNSIGNED, 
	FOREIGN KEY(job_id)   REFERENCES parent_job(job_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO testdb.child_member 
	(user_id, name, job_id)
	VALUES
	(1, "You", 1);

たすきがけになっていないはずなのに…どうして？

何気に見逃しがちなパターンです。

解決

外部キー制約がされているテーブルを操作する前に、親のテーブルに対して先に排他ロックをかけておきます。

すぐに対応が出来ない！

設計の見直しや検証が終わるまで対症療法として、デッドロックになっても早くタイムアウトするようにしておく、という手もあります。

/etc/my.cnf

innodb_lock_wait_timeout=10;

プログラムでロールバックする

また、プログラム側でデッドロックを検地した場合はロールバックする仕組みを実装することも出来ます。

try{

(略)

    $dbh->beginTransaction();
 
    try {

(略)
 
      //コミット
      $dbh->commit();
 
    }catch(PDOException $e){
    
      //ロールバック
      $dbh->rollback();

      throw $e;
    }
 
  } catch(PDOException $e){
    echo $e->getMessage();
  }

ロールバックの仕組みの実装と同時にデッドロック時にログに出力するようにしたり、メールなどの通知でデッドロックを検知する仕組みを実装すると良いかもしれませんね！

ギャップロック

ギャップロック: これはインデックスレコード間にあるギャップのロック、または先頭のインデックスレコードの前や末尾のインデックスレコードのあとにあるギャップのロックです。

@see https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html

インデックスの空振りロックです。

CREATE TABLE a_table(
	a_id       INT UNSIGNED PRIMARY KEY,
	point      INT UNSIGNED,
    value      VARCHAR(255),
    INDEX      idx_a_id(a_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO a_table(a_id, point, value) VALUES
	(1, 100, "a"),
	(2, 100, "b"),
	(3, 100, "c"),
	(9, 100, "i"),
	(10, 100, "j");

パターン1 排他ロックで対象IDにレコードが存在しない場合

Aトランザクション

BEGIN;

SELECT * FROM a_table WHERE a_id > 10 FOR UPDATE;

Bトランザクション

BEGIN;

INSERT INTO a_table(a_id, point, value) VALUES(14, 100, "n");

Aトランザクション

BEGIN;

SELECT * FROM a_table WHERE a_id = 5 FOR UPDATE;

ギャップロックがかかる。

パターン2 指定IDの排他ロックを空振りした場合

Aトランザクション

BEGIN;

SELECT * FROM a_table WHERE a_id = 5 FOR UPDATE;

Bトランザクション

BEGIN;

INSERT INTO a_table(a_id, point, value) VALUES(14, 100, "n");

INSERT INTO a_table(a_id, point, value) VALUES(8, 100, "n");

ギャップロックがかかる。

１つのテーブルでデッドロックする場合

１テーブルでもデッドロックは発生する

CREATE TABLE product(
	id         INT UNSIGNED PRIMARY KEY,
    name       VARCHAR(255),
    INDEX      idx_a_id(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO product(id, name) VALUES
	(1, "ケーキ風ばなな"),
	(2, "高めのあんぱん");

上記のようにテーブルを作成します。

●A


BEGIN;

UPDATE product SET name = "まるごとバナナ" WHERE id = 1;


●B

BEGIN;

UPDATE product SET name = "高級あんぱん" WHERE id = 2;



●A

UPDATE product SET name = "高級あんぱん" WHERE id = 2;

固まる




●B

UPDATE product SET name = "まるごとバナナ" WHERE id = 1;

デッドロック！

レコードの更新順によっては、１つのテーブルでもたすきがけとなりデッドロックが発生します。

複数レコードを日時などの範囲指定でいっきに更新する時など発生しがちです。

解決には？

●A


BEGIN;

SELECT * FROM product WHERE id = 1 FOR UPDATE;
SELECT * FROM product WHERE id = 2 FOR UPDATE;
UPDATE product SET name = "まるごとバナナ" WHERE id = 1;


●B

BEGIN;

SELECT * FROM product WHERE id = 2 FOR UPDATE;

固まる。ロック解放待ち。





●A

UPDATE product SET name = "高級あんぱん" WHERE id = 2;

COMMIT;




●B

ロック解放される

SELECT * FROM product WHERE id = 1 FOR UPDATE;
UPDATE product SET name = "高級あんぱん" WHERE id = 2;
UPDATE product SET name = "まるごとバナナ" WHERE id = 1;

COMMIT;

FOR UPDATEで排他ロックをかけてあげればOKです。

デッドロックをログに出したい

innodb_print_all_deadlocksをONにすればエラーログとしてログに出力することが出来ます。

現在の設定を確認します。

mysql> show variables like 'innodb_print_all_deadlocks';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_print_all_deadlocks | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)

OFFになっていますね。

[mysqld]
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

innodb_print_all_deadlocks = ON ←追加

# systemctl restart mysqld

デッドロックのログを見てみよう！

# tail -n 50 /var/log/mysqld.log



*** (1) TRANSACTION:

TRANSACTION 3092, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 140717827069696, query id 23 localhost root updating
UPDATE product SET name = "高級あんぱん" WHERE id = 2
2017-12-05T06:12:58.818434Z 4 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 88 page no 3 n bits 72 index PRIMARY of table `testdb`.`product` trx id 3092 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 000000000c15; asc       ;;
 2: len 7; hex 300000013e16ff; asc 0   >  ;;
 3: len 18; hex e9ab98e7b49ae38182e38293e381b1e38293; asc                   ;;

2017-12-05T06:12:58.818522Z 4 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 3093, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140717826803456, query id 24 localhost root updating
UPDATE product SET name = "まるごとバナナ" WHERE id = 1
2017-12-05T06:12:58.818535Z 4 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 88 page no 3 n bits 72 index PRIMARY of table `testdb`.`product` trx id 3093 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 000000000c15; asc       ;;
 2: len 7; hex 300000013e16ff; asc 0   >  ;;
 3: len 18; hex e9ab98e7b49ae38182e38293e381b1e38293; asc                   ;;

2017-12-05T06:12:58.818603Z 4 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 88 page no 3 n bits 72 index PRIMARY of table `testdb`.`product` trx id 3093 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 00000001; asc     ;;
 1: len 6; hex 000000000c14; asc       ;;
 2: len 7; hex 2f0000013d0faa; asc /   =  ;;
 3: len 21; hex e381bee3828be38194e381a8e38390e3838ae3838a; asc                      ;;

2017-12-05T06:12:58.818675Z 4 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

実は事前に設定あるので、こんな風にデッドロックの箇所を確認することが出来ます。

お疲れ様です。

Amazonおすすめ

iPad 9世代 2021年最新作

iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜

優技録

MySQL デッドロック対処

たすきがけのデッドロック

解決には？

外部キー制約によるデッドロック

解決

すぐに対応が出来ない！

/etc/my.cnf

プログラムでロールバックする

ギャップロック

パターン1 排他ロックで対象IDにレコードが存在しない場合

パターン2 指定IDの排他ロックを空振りした場合

１つのテーブルでデッドロックする場合

１テーブルでもデッドロックは発生する

解決には？

デッドロックをログに出したい

デッドロックのログを見てみよう！

関連記事 - More from my site -

Amazonおすすめ

iPad 9世代 2021年最新作

コメントを残すコメントをキャンセル

たすきがけのデッドロック

解決には？

外部キー制約によるデッドロック

解決

すぐに対応が出来ない！

/etc/my.cnf

プログラムでロールバックする

ギャップロック

パターン1 排他ロックで対象IDにレコードが存在しない場合

パターン2 指定IDの排他ロックを空振りした場合

１つのテーブルでデッドロックする場合

１テーブルでもデッドロックは発生する

解決には？

デッドロックをログに出したい

デッドロックのログを見てみよう！

関連記事 - More from my site -

Amazonおすすめ

iPad 9世代 2021年最新作

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル