概要

Transaction時にUPDATEを投げたとき、どの範囲までロックされるのか調べる。

調査

テスト用テーブルの作成

mysql> show create table test \G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `index_value` int(10) unsigned DEFAULT NULL,
  `value` int(10) unsigned DEFAULT NULL,
  `multi_value` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_value` (`index_value`),
  KEY `multi_value` (`multi_value`,`index_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
 
mysql> select * from test;
+----+-------------+-------+-------------+
| id | index_value | value | multi_value |
+----+-------------+-------+-------------+
|  1 |           1 |     1 |           1 |
|  2 |           2 |     2 |           2 |
|  3 |           3 |     3 |           3 |
|  4 |           4 |     4 |           2 |
+----+-------------+-------+-------------+
4 rows in set (0.00 sec)

[index, index]でUPDATE

同じ[index, index]への更新時のみロックがかかることを確認。

# process A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test set value=2 where multi_value=2 and index_value=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 
# process B
mysql> update test set value=4 where id=4;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 
mysql> update test set value=2 where id=2;
...
 
# process A
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
# process B
Query OK, 0 rows affected (3.21 sec)
Rows matched: 1  Changed: 0  Warnings: 0

[index, 非index]でUPDATE

同じ[index]への更新時のみロックがかかることを確認。

# process A
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update test set value=2 where multi_value=2 and value=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 
# process B
mysql> update test set value=1 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
 
mysql> update test set value=4 where id=4;
...
 
# process A
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
# process B
Query OK, 0 rows affected (3.59 sec)
Rows matched: 1  Changed: 0  Warnings: 0

まとめ

indexで絞れる範囲まで絞ってから、indexで絞りきれなかったrows全てにロックをかけてるっぽい。

参考にしたページ

ロック待ちでハマる前に知りたかったMySQL InnoDBの行ロックとテーブルロックの挙動