refirio.org

■デッドロックの具体例

mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql2> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql1> DELETE FROM test WHERE id = 2; … 処理待ちが発生するが完了はできる Query OK, 1 row affected (7.33 sec) mysql2> DELETE FROM test WHERE id = 2; … デッドロック発生 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql2> DELETE FROM test WHERE id = 2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql2> DELETE FROM test WHERE id = 2; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted

CREATE TABLE test2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED, value INT UNSIGNED, PRIMARY KEY(id, user_id) ) ENGINE=InnoDB; INSERT INTO test2 VALUES(1, 100, 50); INSERT INTO test2 VALUES(2, 100, 100); INSERT INTO test2 VALUES(3, 200, 10); INSERT INTO test2 VALUES(4, 100, 90); INSERT INTO test2 VALUES(5, 300, 70); INSERT INTO test2 VALUES(6, 200, 120); SELECT * FROM test2; +----+---------+-------+ | id | user_id | value | +----+---------+-------+ | 1 | 100 | 50 | | 2 | 100 | 100 | | 3 | 200 | 10 | | 4 | 100 | 90 | | 5 | 300 | 70 | | 6 | 200 | 120 | +----+---------+-------+

mysql1> BEGIN; mysql2> BEGIN; mysql1> UPDATE test2 SET value = (value + 10) WHERE user_id = 500; … 条件に一致するものが無いので更新は発生しないが、トランザクション内で「一致するものが無い」ことを保証するために（他のトランザクションの影響を受けないように）ロックがかけられる mysql2> UPDATE test2 SET value = (value + 10) WHERE user_id = 600; … 処理待ちが発生。この時点でも条件に一致するものが無いのでロックがかけられる mysql1> INSERT INTO test2 (user_id, value) values (500, 50); mysql2> INSERT INTO test2 (user_id, value) values (600, 60); … デッドロックが発生する 以下のように強制終了させることで、改めてINSERTを実行することはできた # mysql -u root -p mysql> SHOW PROCESSLIST; +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ | 51 | webmaster | localhost | test | Sleep | 196 | | NULL | 0.000 | | 52 | webmaster | localhost | test | Query | 2 | update | INSERT INTO test2 (user_id, value) values (600, 60) | 0.000 | | 53 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ mysql> KILL 51; mysql> KILL 52;