MySQLのインデックスによるロックの違いを確認してみる

MySQLのインデックスによるロックの違いを確認してみる。

【参考】
ロック待ちでハマる前に知りたかったMySQL InnoDBの行ロックとテーブルロックの挙動 | 三度の飯とエレクトロン
http://blog.katty.in/3082

準備

とりあえずMySQLをインストールする。

$ sudo yum -y install mysql-server
$ sudo service mysqld start
$ sudo chkconfig mysqld on

今回は、testデータベースを使う。

$ mysql -u root
mysql> use test

確認用のテーブルを作成する。

mysql> create table sample (
  id   int primary key auto_increment,
  name varchar(32) not null,
  val  int not null
) engine=innoDB default charset=utf8;

データを追加して

mysql> insert into sample (name, val)values
('satou', 0),
('suzuki', 0),
('takahashi', 0),
('tanaka', 0),
('ito', 0);

下記のような状態。

mysql> select * from sample;
+----+-----------+-----+
| id | name      | val |
+----+-----------+-----+
|  1 | satou     |   0 |
|  2 | suzuki    |   0 |
|  3 | takahashi |   0 |
|  4 | tanaka    |   0 |
|  5 | ito       |   0 |
+----+-----------+-----+

ターミナルを２つ立ち上げ、mysqlへの接続を２つ用意する。
以降、一つ目を

mysql1>

２つ目を

mysql2>

と記す。

update …

インデックスあり

idカラムを条件にして、更新。コミットしていない状態。

mysql1> begin;
mysql1> update sample set val = 0 where id = 2;

別の行を更新すると、すぐ更新できる。

mysql2> update sample set val = 0 where id = 3;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

同じ行だと待たされる。

mysql2> update sample set val = 0 where id = 2;
（待たされる）

インデックスのあるカラムを条件に更新した場合は行ロックになっている。

インデックスなし

nameカラムを条件にして、更新。コミットしていない状態。

mysql1> begin;
mysql1> update sample set val = 0 where name = 'suzuki';

別の行を更新すると、待たされる。

mysql2> update sample set val = 0 where id = 3;
（待たされる）

インデックスのないカラムを条件に更新した場合はテーブルロックになっている。

select … lock in share mode

インデックスあり

idカラムを条件にして、lock in share modeで検索。コミットしていない状態。

msyql1> begin;
mysql1> select * from sample where id = 2 lock in share mode;

別の行を更新すると、すぐ更新できる。

mysql2> update sample set val = 0 where id = 3;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

同じ行だと待たされる。

mysql2> update sample set val = 0 where id = 2;
（待たされる）

インデックスのあるカラムを条件にlock in share modeで検索した場合は行ロックになっている。

インデックスなし

nameカラムを条件にして、lock in share modeで検索。コミットしていない状態。

mysql1> begin;
mysql1> select * from sample where name = 'suzuki' lock in share mode;

別の行を更新すると、待たされる。

mysql2> update sample set val = 0 where id = 3;
（待たされる）

インデックスのないカラムを条件にlock in share modeで検索した場合はテーブルロックになっている。

select … for update

インデックスあり

idカラムを条件にして、for updateで検索。コミットしていない状態。

msyql1> begin;
mysql1> select * from sample where id = 2 for update;

別の行を検索すると、すぐ検索できる。

mysql2> select * from sample where id = 3 for update;
+----+-----------+-----+
| id | name      | val |
+----+-----------+-----+
|  3 | takahashi |   0 |
+----+-----------+-----+
1 row in set (0.00 sec)

同じ行だと待たされる。

mysql2> select * from sample where id = 2 for update;
（待たされる）

インデックスのあるカラムを条件にfor updateで検索した場合は行ロックになっている。

インデックスなし

nameカラムを条件にして、for updateで検索。コミットしていない状態。

msyql1> begin;
mysql1> select * from sample where name = 'suzuki' for update;

別の行を検索すると、待たされる。

mysql2> select * from sample where id = 3 for update;
（待たされる）

インデックスのないカラムを条件にfor updateで検索した場合はテーブルロックになっている。

結果

今回確認した結果。

検索条件のカラム	インデックスあり	インデックスなし
update …	行ロック	テーブルロック
select … lock in share mode	行ロック	テーブルロック
select … for update	行ロック	テーブルロック