概要
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全てにロックをかけてるっぽい。