まず準備として、下記のテーブルとデータを使用して検証を行う。
CREATE TABLE `test_lock` (
`id` int(10) unsigned NOT NULL,
`index_ari` int(10) unsigned NOT NULL,
`index_nasi` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `i1` (`index_ari`)
) ENGINE=InnoDB;
insert test_lock (id,index_ari,index_nasi) values
(1,1,1),
(2,0,2),
(3,3,0),
(4,4,4),
(5,5,5);
まずはロックに関する基本的な内容のまとめ。
・ロックの種類
共有ロック
排他ロック
・ロックの粒度
行ロック
テーブルロック
ロックには排他ロックと共有ロックの2種類がある。
・排他ロック
データ更新の際に利用されるロック
該当レコードに対し、ひとつのプロセスのみ獲得できる
排他ロックがかかっているレコードに対しては、排他ロックを獲得することはできない
排他ロックがかかっているレコードに対して更新クエリを実行することはできない
排他ロックのクエリ例：
select * from test_lock where id=3 for update;
・共有ロック
読み込みの際に利用されるロック
該当レコードに対し、複数のプロセスが獲得できる
共有ロックがかかっているレコードに対して排他ロックを獲得することはできない
共有ロックがかかっているレコードに対して更新クエリを実行することはできない
共有ロックのクエリ例：
select * from test_lock where id=3 lock in share mode;
つまり、該当レコードの排他ロックを獲得することができれば、レコードの更新が可能だが、共有ロックを獲得できたとしてもレコードの更新ができるとは限らない（他のトランザクションもロックを獲得しているかもしれないから）
・共有ロックのデモ
terminal1
mysql> begin;
mysql> select * from test_lock where id=3 lock in share mode;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> begin;
共有ロックされたデータに対してアクセスは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
共有ロックされたデータに対して共有ロックを掛けることは可能
mysql> select * from test_lock where id=3 lock in share mode;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
共有ロックされたデータに対して排他ロックを掛けることはできない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
共有ロックされたデータに対して更新クエリを実行することはできない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
・排他ロックのデモ
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
terminal2
mysql> begin;
排他ロックされたデータに対してアクセスは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
1 row in set (0.00 sec)
排他ロックされたデータに対して共有ロックを掛けることはできない
mysql> select * from test_lock where id=3 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックされたデータに対して排他ロックを掛けることはできない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックされたデータに対して更新クエリを実行することはできない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ロックの粒度にはテーブルロックと行ロックの2種類がある。
・テーブルロック
オーバーヘッドが低い
書き込み中は他の読み取り、書き込み操作がすべて拒否される
MyISAMはテーブルロック
・行ロック
並行性が高いがオーバーヘッドも高い
行ロックはストレージエンジンで実装されている
・テーブルロックのデモ
terminal1
mysql> begin;
mysql> select * from test_lock for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 0 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
5 rows in set (0.00 sec)
terminal2
排他ロックがテーブル全体にかかっていてもselectはできる
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
排他ロックがテーブル全体にかかっていると、特定レコードへの排他ロックは取得できない
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックがテーブル全体にかかっていると、特定レコードの更新を実施することができない
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
排他ロックがテーブル全体にかかっていると、新たなレコードを挿入することができない
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
・行ロックのデモ
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
排他ロックがかかっているレコードへのselectは可能
mysql> select * from test_lock where id=3;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
排他ロックがかかっているレコードに対して排他ロックは獲得できないが、かかっていないものに対しての排他ロックは獲得できる
mysql> select * from test_lock where id=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test_lock where id=2 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
+----+-----------+------------+
排他ロックがかかっているレコードに対して更新はできないが、かかっていないものに対しての更新はできる
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
排他ロックが掛かっていても、新たなレコードの挿入はできる
mysql> rollback;
mysql> begin;
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
上記を踏まえた上で、ここからが本題。
検索クエリのインデックスが効いていないと、テーブルロックがかかってしまうことがわかった。今回の検証用テーブルで、インデックスのあるカラムとないカラムを用意したので、それぞれを検索条件に入れたクエリでどう挙動が変わるのかを確認しよう。
・インデックスがあるケース
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_ari=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
インデックスが効いているカラムを使ってのselect..for updateでは、
他のトランザクションは排他ロックが獲得されているレコード以外の更新や挿入は可能
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
・インデックスがないケース
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_nasi=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ここまでは想定通り。排他ロックが獲得されたレコードの更新ができないのは当然。ところが・・
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
なんと全レコードの更新ができないという状況が発生している。さらに・・
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
新たなレコードの挿入すらできない。つまりこれはテーブルロック
インデックスを使った検索ができないクエリによる排他ロックは、テーブルロックになる。
続いて、範囲検索によるロックの獲得に関しても、直感的な理解とは異なる挙動をする。
・範囲検索を行った場合のロックの挙動
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 4 and id <> 1 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ここまではわかる。今回のselect..for updateによる抽出レコードだから。問題はここから。
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test_lock set index_ari=10 where id=4;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
id:1,4はselect..for updateの抽出対象でないにもかかわらずロックが掛かっている
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
id:5の更新と、新規レコード挿入は問題なくできたことからテーブルロックではないことがわかる
explainを実行した限りではrowsは2行となっているが、それ以外のレコードにもロックが掛かっていることになる。
mysql> explain select * from test_lock where id < 4 and id <> 1;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | test_lock | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
・範囲検索を行った場合のロックの挙動2
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 3 and id <> 2 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
+----+-----------+------------+
terminal2
mysql> rollback;
mysql> begin;
mysql> update test_lock set index_ari=10 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
このロックは想定通り。
mysql> update test_lock set index_ari=10 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
クエリで除外条件に指定しているにもかかわらず、ロック対象になっている。
mysql> update test_lock set index_ari=10 where id=3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
これもクエリに取得条件には含まれないがロック対象になっている。
mysql> update test_lock set index_ari=10 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_lock set index_ari=10 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert test_lock (id,index_ari,index_nasi) values(6,6,6);
Query OK, 1 row affected (0.00 sec)
id:4,5の更新と新規レコード挿入は問題なく出来た。
ロックの取得は範囲検索では広めに（仮説としては第1検索条件の範囲+境界条件）行われる
・ロックの範囲を確認する方法
http://blog.kamipo.net/entry/2013/12/03/235900
記載の説明では一体どうログを読めばよいのかわからなかったが、とりあえず
mysql> CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB;
して、ロックを獲得するクエリを発行後
mysql> SHOW ENGINE INNODB STATUS\G;
することで手がかりとなるログが現れるようだ。ではいろいろ試してみよう。
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 0 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
terminal2
6 row lock(s)という記載と6つのRecord lockのデータが記載されている。「0: len 4; hex 00000001」と書かれているのがid番号だろうか？
mysql> SHOW ENGINE INNODB STATUS\G;
--TRANSACTION 1288, ACTIVE 23 sec
2 lock struct(s), heap size 376, 6 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 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;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030148; asc X H;;
3: len 4; hex 00000005; asc ;;
4: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id=3 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
1 row lock(s)という記載と1つのRecord lockのデータが記載されている。
mysql> SHOW ENGINE INNODB STATUS\G;
--TRANSACTION 1288, ACTIVE 13 sec,
2 lock struct(s), heap size 376, 1 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_ari=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
3 row lock(s)という記載と3つのRecord lockのデータが記載されている。
ただし先の実験ではid:3,5にはロックが掛かっておらず、データの更新ができたことが確認されている
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 5 sec,
4 lock struct(s), heap size 1248, 3 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `i1` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 4; hex 00000004; asc ;;
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
RECORD LOCKS space id 0 page no 2555 n bits 80 index `i1` of table `test`.`test_lock` trx id 1288 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where index_nasi=4 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 4 | 4 | 4 |
+----+-----------+------------+
terminal2
6 row lock(s)という記載と6つのRecord lockのデータが記載されている。
テーブルロックの時とログの出方がほぼ同じであることからテーブルロックになっていると推定できる
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 3 sec,
2 lock struct(s), heap size 376, 6 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 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;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000005; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030148; asc X H;;
3: len 4; hex 00000005; asc ;;
4: len 4; hex 00000005; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 4 and id <> 1 for update;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 2 | 0 | 2 |
| 3 | 3 | 0 |
+----+-----------+------------+
terminal2
4 row lock(s)という記載と4つのRecord lockのデータが記載されている。
「0: len 4; hex 00000001」の箇所のhexがidを表していると仮定すると、id:1,2,3,4にロックが掛かっていることになる。
これは先の実験の結果とも一致する。
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 5 sec,
2 lock struct(s), heap size 376, 4 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000004; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803013a; asc X :;;
3: len 4; hex 00000004; asc ;;
4: len 4; hex 00000004; asc ;;
terminal1
mysql> rollback;
mysql> begin;
mysql> select * from test_lock where id < 3 and id <> 2 for update;
terminal2
3 row lock(s)という記載と3つのRecord lockのデータが記載されている。
「0: len 4; hex 00000001」の箇所のhexがidを表していると仮定すると、id:1,2,3にロックが掛かっていることになる。
これは先の実験の結果とも一致する。
mysql> SHOW ENGINE INNODB STATUS\G;
---TRANSACTION 1288, ACTIVE 4 sec,
2 lock struct(s), heap size 376, 3 row lock(s)
TABLE LOCK table `test`.`test_lock` trx id 1288 lock mode IX
RECORD LOCKS space id 0 page no 2555 n bits 80 index `PRIMARY` of table `test`.`test_lock` trx id 1288 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000001; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 80001858030110; asc X ;;
3: len 4; hex 00000001; asc ;;
4: len 4; hex 00000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000002; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803011e; asc X ;;
3: len 4; hex 00000000; asc ;;
4: len 4; hex 00000002; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 00000003; asc ;;
1: len 6; hex 00124cc99b3e; asc L >;;
2: len 7; hex 8000185803012c; asc X ,;;
3: len 4; hex 00000003; asc ;;
4: len 4; hex 00000000; asc ;;
今後の課題
InnoDBのREPEATABLE READにおけるLocking Readについての注意点
にあるlost updateは時間のあるときに追加で確認しておこうかと。
#terminal1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
#terminal2
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
#terminal1
mysql> update test_lock set index_ari=index_ari+1 where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#terminal2
mysql> update test_lock set index_ari=index_ari+1 where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
順番にupdateを実行すると、最初のupdateでロックが掛かって次のトランザクションは更新ができなくなるようだ。
#terminal2
mysql> update test_lock set index_ari=index_ari+1 where id=2;
即下記を実行。
#terminal1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
terminal1でコミットをするとterminal2の待ち状態のupdateが通る
#terminal2
Query OK, 1 row affected (3.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_lock;
+----+-----------+------------+
| id | index_ari | index_nasi |
+----+-----------+------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 0 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+----+-----------+------------+
ということで、同時updateはちょっとでも速い一方のトランザクションがロックを獲得するので、その更新がコミットされた後、もう一方のトランザクションの更新がなされるので、MySQL5.1.61では特にlost updateの不具合は生じないことがわかった。
参考文献
実践ハイパフォーマンスMySQL 第2版
