MySQL のロック範囲は実行計画で変わるという話

最近、ANDPADでデータベース周りの技術顧問をさせて頂いています。ANDPADのエンジニアの皆さんから「データベースのロックまわりを詳しく知りたい！」というお話を受けて、先日、ロック周りの社内勉強会を開催しました。

SQLでは一般的なプログラミング言語と違って、ロックの制御を明示的に記述しません。ロックは暗黙的に（自動的に）データベースが必要なロックを獲得します。データベースのロックが　わかりにくい・むずかしい　と言われることが多いのはこういった背景があると思います。

MySQL のロック範囲は実行計画で変わる

更新対象の行がロックされるのは予測が付く方が多いと思います。しかし、MySQL（InnoDB）では更新対象でなくても行がロックされることがあります。

このようなサンプルデータを使って説明します。

mysql> CREATE TABLE `lockt` (
    ->  `pk` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    ->  `name` varchar(255) DEFAULT NULL,
    ->  `favorite` varchar(255) DEFAULT NULL,
    ->  `addr` varchar(255) DEFAULT NULL,
    -> PRIMARY KEY (`pk`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM lockt;
+----+-------+----------+---------+
| pk | name  | favorite | addr    |
+----+-------+----------+---------+
|  1 | Taro  | Apple    | Tokyo   |
|  2 | Jiro  | Orange   | Tokyo   |
|  3 | Alice | Orange   | London  |
|  4 | John  | Pine     | NewYork |
+----+-------+----------+---------+
4 rows in set (0.00 sec)

MySQL 8.0 で登場した、FOR UPDATE SKIP LOCKED を使って、どの行がロックしたのかを確かめます。FOR UPDATE SKIP LOCKED はロックされている行を除外して、SELECTすることが出来る機能です。

-- 何もロックがない状態ではすべてのレコードが取れる
mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
+----+-------+----------+---------+
| pk | name  | favorite | addr    |
+----+-------+----------+---------+
|  1 | Taro  | Apple    | Tokyo   |
|  2 | Jiro  | Orange   | Tokyo   |
|  3 | Alice | Orange   | London  |
|  4 | John  | Pine     | NewYork |
+----+-------+----------+---------+
4 rows in set (0.00 sec)

favorite = 'Orange' and addr = 'Tokyo'の pk=2 の1行のみを更新してみます。

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

上記のUPDATEがロックを取っている状態で、FOR UPDATE SKIP LOCKED で確認します。

mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
Empty set (0.00 sec)

関係のない pk=1,3,4 の行も含めて、全部の行がロックされてしまいました！

インデックスを追加します

次に、favorite カラムにインデックスを追加して、ロック範囲がどう変化するか確認してみます。

mysql> CREATE INDEX idx_favorite ON lockt(favorite);
Query OK, 0 rows affected (0.02 sec)

今度は、favorite = 'Orange'の行がロックされました。まだ、関係のない pk = 3 の行がロックされてしまっています。

mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
+----+------+----------+---------+
| pk | name | favorite | addr    |
+----+------+----------+---------+
|  1 | Taro | Apple    | Tokyo   |
|  4 | John | Pine     | NewYork |
+----+------+----------+---------+
2 rows in set (0.00 sec)

インデックスを favorite と addr の複合キーにします。

mysql> CREATE INDEX idx_favorite_addr ON lockt(favorite, addr);
Query OK, 0 rows affected (0.03 sec)

今度は、無事、pk = 2の行だけがロックされました！

mysql> SELECT * FROM lockt FOR UPDATE SKIP LOCKED;
+----+-------+----------+---------+
| pk | name  | favorite | addr    |
+----+-------+----------+---------+
|  1 | Taro  | Apple    | Tokyo   |
|  3 | Alice | Orange   | London  |
|  4 | John  | Pine     | NewYork |
+----+-------+----------+---------+
3 rows in set (0.00 sec)

実行計画を比べる

すべてロックされてしまったケースでは、インデックスでの絞り込みが行われてません。更新対象の行を特定するためにテーブル全体をスキャン（type=index）してしまっています。

mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo' \G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: lockt
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using where

インデックスを追加した場合は、rangeスキャンになり、該当の行をインデックスを使って絞り込めていることがわかります。

mysql> EXPLAIN UPDATE lockt SET favorite = 'Banana' WHERE favorite = 'Orange' AND addr = 'Tokyo' \G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: lockt
   partitions: NULL
         type: range
possible_keys: idx_favorite,idx_favorite_addr
          key: idx_favorite_addr
      key_len: 2046
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using temporary
1 row in set, 1 warning (0.00 sec)