MYSQL死锁案例_MySQL

MYSQL死锁案例

01下列的例子演示当锁定请求可能会导致死锁之时一个错误会如何发生。例子中包括两个客户端A和B。0203首先客户端A创建一个包含一个行的表，然后开始一个事务。在这个事务内，A通过在共享模式选择行获得对行的S 锁定：0405mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;0607Query OK, 0 rows affected (1.07 sec)0809 1011mysql> INSERT INTO t (i) VALUES(1);1213Query OK, 1 row affected (0.09 sec)1415 1617mysql> START TRANSACTION;1819Query OK, 0 rows affected (0.00 sec)2021 2223mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;2425+------+2627| i |2829+------+3031| 1 |3233+------+34351 row in set (0.10 sec)3637接着，客户端B开始一个事务并尝试从该表删除行：3839mysql> START TRANSACTION;4041Query OK, 0 rows affected (0.00 sec)4243 4445mysql> DELETE FROM t WHERE i = 1;47删除操作要求一个X 锁定。因为这个锁定不兼容客户端A持有的S锁定，所以X 锁定不被允许，所以请求进入对行及客户端阻挡的锁定请求队列。4849最后，客户端A也试图从表中删除该行：5051mysql> DELETE FROM t WHERE i = 1;5253ERROR 1213 (40001): Deadlock found when trying to get lock;5455try restarting transaction