Zenn
Open4

mysqlのロックについて

MySQL
hoshitocathoshitocat

背景

  • 業務で、ULIDを使うことが多い
  • mysql8.0.18
  • Intで連番のIDを使用し、そのカラムに対してロックした場合の件についてはよくあるが、ULIDでの記事がなかったので試してみることに

気になり

  • ギャップロックのかかり方について
  • ネクストキーロックについて
  • 複合ユニークキーの場合について
hoshitocathoshitocat

環境

  • mysql8.0.18

以下のようにDockerImageで起動

docker run --rm --platform linux/amd64 -p'3306:3306' -eMYSQL_ALLOW_EMPTY_PASSWORD=YES mysql:8.0.18
hoshitocathoshitocat

手順

  1. まずは初期設定
mysql> create database playground
    -> ;
Query OK, 1 row affected (0.03 sec)
  1. テーブル設計
-- 一つのPKの場合
create table single_ids(
  `id` varchar(26) NOT NULL COMMENT 'ULIDのPrimary Key',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 2つのPKの場合
create table multiple_ids(
  `id1` varchar(26) NOT NULL COMMENT 'ULIDのPrimary Key',
  `id2` varchar(26) NOT NULL COMMENT 'ULIDのPrimary Key',
  PRIMARY KEY (`id1`, `id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  1. 結果
mysql> show tables;
+----------------------+
| Tables_in_playground |
+----------------------+
| multiple_ids         |
| single_ids           |
+----------------------+
2 rows in set (0.00 sec)
hoshitocathoshitocat

Int型のギャップロックについて

  • 前提の確認
tx1> select * from int_ids;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  7 |
+----+
4 rows in set (0.01 sec)

期待する挙動

  • 5に対してロックを取ったら、ギャップである4,6にもロックがかかってしまう

実際にやってみた

  1. tx1にてギャップロックとなるように id = 5 に対してロックをかける
tx1> begin;
Query OK, 0 rows affected (0.01 sec)

tx1> select * from int_ids where id = 5 for update;
Empty set (0.00 sec)

tx1> insert into int_ids value (5);
Query OK, 1 row affected (0.00 sec)
  1. tx2にて、id = 4 または id = 6 に対してinsertしようとすると待機になる
tx2> begin;
Query OK, 0 rows affected (0.01 sec)

tx2> select * from int_ids;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  7 |
+----+
4 rows in set (0.01 sec)

tx2> insert into int_ids value(4);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
tx2> insert into int_ids value(6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
tx2>

余談だが、複数IDを指定している場合も同じくギャップロックはかかるのか？

結果: 普通にかかる

tx1> begin;
Query OK, 0 rows affected (0.00 sec)

tx1> select * from int_ids where id in (1,2,3,5,10) for update;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.01 sec)

tx1>

tx2> begin;
Query OK, 0 rows affected (0.00 sec)

tx2> select * from int_ids;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  7 |
+----+
4 rows in set (0.00 sec)

tx2> insert into int_ids value(4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
tx2> insert into int_ids value(6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
tx2> insert into int_ids value(9);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
tx2> insert into int_ids value(11);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
tx2> insert into int_ids value(20);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

特に 10 以上の場合は最大までロックがかかってしまうので、困る