Zenn
Open13

MySQL 8: ロック

ほげさんほげさん

発生しているロックの確認方法

select
    threads.processlist_user,
    locks.object_name,
    locks.index_name,
    locks.lock_type,
    locks.lock_mode,
    locks.lock_status,
    locks.lock_data
from performance_schema.data_locks locks
join performance_schema.threads threads on locks.thread_id = threads.thread_id;
ほげさんほげさん

一意インデックス検索 行

  • テーブルに IX ロック
  • id = 10 の行に X ロック
    • この行が変更できなければ Phantom Read を防げる
Reader> begin;

Reader> select * from user where id = 10 for update;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
| 10 | Alice |  25 |
+----+-------+-----+
1 row in set (0.00 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| Reader           | user        | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 10        |
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
ほげさんほげさん

一意インデックス検索 行 ( からぶり )

  • テーブルに IX ロック
  • id = 10 のギャップに X ロック
    • id = 15 が生まれなければ Phantom Read を防げる
      • ロックするべき行がないのでギャップをロックする
Reader> begin;

Reader> select * from user where id = 15 for update;
Empty set (0.00 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| Reader           | user        | PRIMARY    | RECORD    | X,GAP     | GRANTED     | 10        |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
ほげさんほげさん

一意インデックス検索 範囲

  • テーブルに IX ロック
  • id = 10 の行に X ロック
    • この行が変更できなければ Phantom Read を防げる
  • id = 20 の行に X ロック
    • この行が変更できなければ Phantom Read を防げる
  • id = 30 のギャップに X ロック
    • id in 20 ~ 30 が生まれなければ Phantom Read を防げる
      • ロックするべき行がないのでギャップをロックする
Reader> begin;

Reader> select * from user where id between 5 and 25 for update;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
| 10 | Alice    |  25 |
| 20 | Isabella |  35 |
+----+----------+-----+
2 rows in set (0.01 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| Reader           | user        | PRIMARY    | RECORD    | X         | GRANTED     | 10        |
| Reader           | user        | PRIMARY    | RECORD    | X         | GRANTED     | 20        |
| Reader           | user        | PRIMARY    | RECORD    | X,GAP     | GRANTED     | 30        |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
ほげさんほげさん

一意インデックス検索 範囲 ( からぶり )

  • テーブルに IX ロック
  • id = 10 のギャップに X ロック
    • id in ~ 10 が生まれなければ Phantom Read を防げる
      • ロックするべき行がないのでギャップをロックする
Reader> begin;

Reader> select * from user where id between 3 and 7 for update;
Empty set (0.00 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| Reader           | user        | PRIMARY    | RECORD    | X,GAP     | GRANTED     | 10        |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
ほげさんほげさん

非一意インデックス検索 行

  • テーブルに IX ロック
  • age が 35 ~ 20 の行に X ロック
  • id = 20 の行に X ロック
  • age が 45 ~ 30 のギャップに X ロック
Reader> begin;

Reader> select * from user where age = 35 for update;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
| 20 | Isabella |  35 |
+----+----------+-----+
1 row in set (0.00 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| Reader           | user        | age_index  | RECORD    | X             | GRANTED     | 35, 20    |
| Reader           | user        | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 20        |
| Reader           | user        | age_index  | RECORD    | X,GAP         | GRANTED     | 45, 30    |
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
ほげさんほげさん

非一意インデックス検索 行 ( からぶり )

  • テーブルに IX ロック
  • age が 35 ~ 20 のギャップに X ロック
Reader> begin;

Reader> select * from user where age = 30 for update;
Empty set (0.01 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| Reader           | user        | age_index  | RECORD    | X,GAP     | GRANTED     | 35, 20    |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
ほげさんほげさん

非一意インデックス検索 範囲

  • テーブルに IX ロック
  • age が 35 ~ 20 のインデックス範囲に X ロック
Reader> begin;

Reader> select * from user where age between 28 and 33 for update;
Empty set (0.00 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode | lock_status | lock_data |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| Reader           | user        | age_index  | RECORD    | X         | GRANTED     | 35, 20    |
+------------------+-------------+------------+-----------+-----------+-------------+-----------+
ほげさんほげさん

非一意インデックス検索 範囲 ( からぶり )

Reader> begin;

Reader> select * from user where age between 30 and 50 for update;
+----+----------+-----+
| id | name     | age |
+----+----------+-----+
| 20 | Isabella |  35 |
| 30 | James    |  45 |
+----+----------+-----+
2 rows in set (0.01 sec)

Reader> call ls;
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
| processlist_user | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+------------------+-------------+------------+-----------+---------------+-------------+-----------+
| Reader           | user        | NULL       | TABLE     | IX            | GRANTED     | NULL      |
| Reader           | user        | age_index  | RECORD    | X             | GRANTED     | 35, 20    |
| Reader           | user        | age_index  | RECORD    | X             | GRANTED     | 45, 30    |
| Reader           | user        | age_index  | RECORD    | X             | GRANTED     | 55, 40    |
| Reader           | user        | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 20        |
| Reader           | user        | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 30        |
+------------------+-------------+------------+-----------+---------------+-------------+-----------+