どうも！デッドロック撲滅委員会会長の優です。
今回は１つのテーブルでも発生してしまうデッドロックのご紹介です！
こんなケース
『怪しいのはトランザクションしている箇所だ。でも2つのテーブルを操作しているわけでもないので、デッドロックは起こりえないはず。デッドロックってことはなさそうだけど？？さっぱり原因が不明だ…。サーバスペックあげたら解決するかな？？？』
そんな時、それはやはりデッドロックかもしれません。
バックナンバー
１テーブルでもデッドロックは発生する
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)
実は事前に設定あるので、こんな風にデッドロックの箇所を確認することが出来ます。
お疲れ様です。
