🍣

MySQL(InnoDB)の行ロックについてすぐ忘れるのでロック範囲を目視確認できるようにする。

2023/05/07に公開

MySQL

Database

tech

これはなに?

InnoDB REPEATABLE READの行ロックの細かい部分すぐ忘れる...
開発環境でクエリ毎にちゃんとロック範囲を確認して「ヨシ！」できるようにしよう
performance_schema.data_locksをみよう。というn番煎じの記事なので、既に知ってる方にとっては新しい情報はないと思います(ごめんなさい)
また、ロック範囲を確認したい!という旨の記事なのでInsert Intention Lock等の各用語については特に説明しません....!

はじめに

こんにちは。突然ですが問題です。

前提条件

次のようなテーブルとレコードがあるとします。

CREATE TABLE `elem` (
  `id` int unsigned NOT NULL,
  `a`  char(2) NOT NULL,
  `b`  char(2) NOT NULL,
  `c`  char(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB;

SELECT * FROM `elem`;
+----+-----+----+----+
| id | a   | b  | c  |
+----+-----+----+----+
|  2 | Au  | Be | Co |
|  5 | Ar  | Br | C  |
+----+-----+----+----+

各クエリを実行したときのロック範囲はどうなるでしょう?

InnoDB(分離レベル: REPEATABLE_READ)
MySQLのバージョンは5.7,8.0.21で結果に変化がなかったので、極端に古く(3.3とか)なければ何でも大丈夫だと思います。
画像のSupremumやInfimumが分かり辛ければ+∞, -∞ といった感じに読み替えてください。

問題1

BEGIN;
SELECT id FROM `elem` where id BETWEEN 2 AND 5 FOR UPDATE;

正解

したがって次のようなクエリはInsert Intention Lock獲得待ちとなります。

insert into elem values(6,'Ar','Br','C');

問題2

BEGIN;
UPDATE elem SET c='Dr' WHERE id IN (2, 5);

正解

レコードロックのみでロック範囲と被らないので、問題1と異なり次のクエリはロック獲得待ちとならず即実行されます。

insert into elem values(6,'Ar','Br','C');

問題3

column aにはセカンダリインデックスが貼られていることに注意してください。

BEGIN;
UPDATE elem SET a = 'Go' WHERE a = 'Au';

正解

セカンダリインデックスのAr以降に各種ロックがあるため、
例えば次のクエリはInsert Intention Lock獲得待ちとなります

insert into elem values(7,'Gw','Br','C');

むずない?

どや顔で問題を出した手前非常に申し訳ないのですが、私はこれをパッと出されて短時間で正確にロック範囲を特定できるかあんまり自信がありません。
デッドロックでタイムアウトした旨のアラートが本番環境で鳴り、一刻も早く原因特定をしなければならないといった追い詰められた状況下では尚更自信がないです。(アワアワしてしまう)

開発時点で逐一確認しよう

Lockを伴うコードを書いたはいいが自信がないときに実際にロック範囲を確認できるようにしたいですよね。

MySQL 8.0以上

performance_schemaにdata_locksというテーブルが追加され、そこで詳細なロック状況を一発で確認できます。

-- 問題3
BEGIN;
UPDATE elem SET a = 'Go' WHERE a = 'Au';


-- ここから確認クエリと結果
SELECT index_name, lock_type, lock_mode, lock_status, lock_data
FROM   performance_schema.data_locks
WHERE  object_name = 'elem';

index_name|lock_type|lock_mode    |lock_status|lock_data             |
----------+---------+-------------+-----------+----------------------+
          |TABLE    |IX           |GRANTED    |                      |
idx_a     |RECORD   |X            |GRANTED    |supremum pseudo-record|
idx_a     |RECORD   |X            |GRANTED    |'Au', 2               |
PRIMARY   |RECORD   |X,REC_NOT_GAP|GRANTED    |2                     |
idx_a     |RECORD   |X,GAP        |GRANTED    |'Go', 2               |

MySQL 5.x

残念ながら上のテーブルはないので、SUPER権限をもったユーザで下記のクエリを叩きます。

SET GLOBAL innodb_status_output_locks=ON

後は各クエリ後にSHOW ENGINE INNODB STATUSでロック状況がTRANSACTIONSの箇所に吐き出されるようになります。

-- 問題3
BEGIN;
UPDATE elem SET a = 'Go' WHERE a = 'Au';
SHOW ENGINE INNODB STATUS;

TRANSACTIONSのロック部分を抜き出してコメントを付記したものが下記となります。

------------
TRANSACTIONS
------------
...

 <!-- セカンダリインデックスの+∞(Supremum)レコードのNext Key Lock -->
RECORD LOCKS space id 24 page no 4 n bits 72 index idx_a of table `db`.`elem` trx id 2821 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;; 

<!-- セカンダリインデックスのAuレコードのNext Key Lock -->
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 2; hex 4175; asc Au;;
 1: len 4; hex 00000002; asc     ;;

<!-- プライマリインデックスのid:2のRecord Lock -->
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table `db`.`elem` trx id 2821 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 00000002; asc     ;;
 1: len 6; hex 000000000b05; asc       ;;
 2: len 7; hex 250000013906e2; asc %   9  ;;
 3: len 2; hex 476f; asc Go;;
 4: len 2; hex 4265; asc Be;;
 5: len 2; hex 436f; asc Co;;


<!-- セカンダリインデックスのGoレコードのGap Lock -->
RECORD LOCKS space id 24 page no 4 n bits 72 index idx_a of table `db`.`elem` trx id 2821 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 476f; asc Go;;
 1: len 4; hex 00000002; asc     ;;

--------
FILE I/O
--------

最後に

こういった便利機能やJOIN戦略の改善(Hash JOIN),オプティマイザの改善(ANTI JOINとか..)を見ると、MySQL8系にアップデートしたい欲が強まりますね....

MySQL5.7のEOLが今年10月に控えていますし、AWSといった各種クラウドプロバイダのスケジュールはまだわかりませんが早めに移行の準備をしたいですね....!

また、この記事にかかれたことは全てEffecient MySQL Performanceに書いてあります。
日本語版はまだ出でいませんが、EXPLAIN句について(Chapter 2. Indexes and Indexing)とロックについて(Chapter 8. Transactions)は業務中に傍らにリファレンスとしていつも置いときたい(~~少しは頭使って覚えろ~~)くらいとてもよくまとまっていました。
めちゃおすすめです。

全然関係ないけど作者っぽい人が自分でポジティブな書評を書いててちょっと笑いました。
この姿勢は見習っていきたい...!

Amazing! There's not another MySQL book like this, which is why I wrote it. (Who says an author can't review their own book?) This book is for busy engineers using MySQL--not DBAs--who need to understand and effect MySQL performance quickly. It's straight and to the point. No time is wasted on MySQL knowhow that should be handled by your DBA or cloud provider. Every chapter--every sentence, even--is focused on helping you achieve remarkable MySQL performance.