15 InnoDB テーブルロック

※ユニーク制約 or インデックスが張られているカラムで検索した場合以外はテーブルロックになってしまう

mysql> select * from lock_test;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | tanaka |  20 |
|  2 | suzuki |  30 |
|  3 | sato   |  40 |
+----+--------+-----+
3 rows in set (0.02 sec)

create table lock_test (
  id   int primary key auto_increment,
  name varchar(32) not null,
  age  int not null
) engine=innoDB default charset=utf8;


insert into lock_test (name, age) values
 ('tanaka', 20),
 ('suzuki', 30),
 ('sato',   40);

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lock_test SET age = 21 WHERE name = 'tanaka';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lock_test SET age = 31 WHERE name = 'suzuki';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> show global variables like '%innodb_lock_wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.02 sec)

インデックス張っている方は行ロックとなる

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lock_test SET age = 21 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE lock_test SET age = 31 WHERE id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

InnoDB, mysql