ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionについて

Mysqlでタイトルのようなエラーが出てドキッとすることがあった。

原因としては、MySQLのセッションがトランザクションを使用している時に、ロックがかかった状態のまま、他のセッションがそのテーブルに対して更新しようとして、timeoutが発生している事らしい。

他のセッションによりロックが長時間保持されていること

timeoutまでの時間の確認方法

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

結論から書くと・・

インデックス、ユニークキー制約のないカラムに対して、トランザクションを貼ったまま放置すると、行ロックではなくてテーブル・ロックがかかってしまうので気をつけようという話です。

テストしてみた。

用意したテーブル

mysql> show create table lock_test;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                         |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lock_test | CREATE TABLE `lock_test` (
  `id` int(11) NOT NULL default '0',
  `c1` int(11) default NULL,
  `c2` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

用意したデータ

insert into lock_test (id, c1, c2) values (1, 10,10);
insert into lock_test (id, c1, c2) values (2, 20,20);

状態の確認

mysql> select * from lock_test;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 |   10 |   10 | 
|  2 |   20 |   20 | 
+----+------+------+
2 rows in set (0.00 sec)

実験１(primaryキーがあるカラム)

【セッション１】

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

mysql> update lock_test set c2 = 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

【セッション２】

mysql> update lock_test set c2 = 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

実験2(インデックス貼ってないカラム)

【セッション１】

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

mysql> update lock_test set c2 = 100 where c1 = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

【セッション２】

mysql> update lock_test set c2 = 200 where c1 = 20;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

実験３（c2にインデックスを貼って見る）
【セッション１】

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

mysql> update lock_test set c2 = 100 where c1 = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

【セッション２】

mysql> update lock_test set c2 = 200 where c1 = 20;
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 c2 = 100 where c1 = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

【セッション２】

mysql> update lock_test set c2 = 200 where c1 = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0