手を動かしながらロックを学ぶ 1

2021-06-07#mysql

いつまで経ってもロックについて理解したと言えなかったので、ロックについて手を動かしながら学んでみることにした。

MySQLが公式にサンプルデータとして提供しているworldデータベースと、補完が使えて便利なMySQLクライアントであるmycliをインストールしたDockerイメージを作ったので、それを使って試してみる。

今回は以下のようなcityテーブルに対して特定の一行へのロックをとったときについて試してみる。

MySQL (none)@(none):world> show create table city \G
***************************[ 1. row ]***************************
Table        | city
Create Table | CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
 
1 row in set
Time: 0.003s

共有ロック

トランザクションT1でselect ... lock in share modeを実行して共有ロックをとる。

MySQL (none)@(none):world> begin
Query OK, 0 rows affected
Time: 0.001s
MySQL (none)@(none):world> select * from city where ID = 1 lock in share mode
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1  | Kabul | AFG         | Kabol    | 1780000    |
+----+-------+-------------+----------+------------+
 
1 row in set
Time: 0.010s

別のトランザクションT2で同じ行をselectすることはできたが、updateすることはできなかった。

MySQL (none)@(none):world> begin
Query OK, 0 rows affected
Time: 0.001s
MySQL (none)@(none):world> select * from city where ID = 1;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1  | Kabul | AFG         | Kabol    | 1780000    |
+----+-------+-------------+----------+------------+
 
1 row in set
Time: 0.009s
MySQL (none)@(none):world> select * from city where ID = 1 lock in share mode;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1  | Kabul | AFG         | Kabol    | 1780000    |
+----+-------+-------------+----------+------------+
 
1 row in set
Time: 0.009s
MySQL (none)@(none):world> select * from city where ID = 1 for update;
(1205, 'Lock wait timeout exceeded; try restarting transaction')
MySQL (none)@(none):world> update city set `Population` = 100 where `ID` = 1;
(1205, 'Lock wait timeout exceeded; try restarting transaction')

排他ロック

トランザクションT1でselect ... for updateを実行して排他ロックをとる。

MySQL (none)@(none):world> begin
Query OK, 0 rows affected
Time: 0.000s
MySQL (none)@(none):world> select * from city where `ID` = 1 for update
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1  | Kabul | AFG         | Kabol    | 1780000    |
+----+-------+-------------+----------+------------+
 
1 row in set
Time: 0.011s

別のトランザクションT2で同じ行をselectすることはできたが、select ... lock in share modeやselect ... for updateやupdateはロック取得待ちになった。

MySQL (none)@(none):world> begin
Query OK, 0 rows affected
Time: 0.001s
MySQL (none)@(none):world> select * from city where ID = 1;
+----+-------+-------------+----------+------------+
| ID | Name  | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1  | Kabul | AFG         | Kabol    | 1780000    |
+----+-------+-------------+----------+------------+
 
1 row in set
Time: 0.012s
MySQL (none)@(none):world> select * from city where ID = 1 lock in share mode;
(1205, 'Lock wait timeout exceeded; try restarting transaction')
MySQL (none)@(none):world> select * from city where ID = 1 for update;
(1205, 'Lock wait timeout exceeded; try restarting transaction')
MySQL (none)@(none):world> update city set `Population` = 100 where `ID` = 1;
(1205, 'Lock wait timeout exceeded; try restarting transaction')

まとめ

特定の行に対するロックをとったとき、別のトランザクションからの操作がどうなるかまとめるとこうなった。

T1\T2selectselect ... lock in share modeselect ... for updateupdate
select ... lock in share mode可可ロック待ちロック待ち
select ... for update可ロック待ちロック待ちロック待ち