MYSQL deadlock case _ MySQL

MYSQL deadlock case bitsCN.com

MYSQL deadlock case

01 the following example shows how an error occurs when a lock request may cause a deadlock. The example contains two clients, A and B. 0203 first, client A creates A table containing A row and starts A transaction. In this transaction, A Gets the S lock on the row by selecting the row in the sharing mode: 0405 mysql> create table t (I INT) ENGINE = InnoDB; 0607 Query OK, 0 rows affected (1.07 sec) 0809 1011 mysql> insert into t (I) VALUES (1); 1213 Query OK, 1 row affected (0.09 sec) 1415 1617 mysql> START TRANSACTION; 1819 Query OK, 0 rows affected (0.00 sec) 2021 2223 mysql> 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 next, client B starts a TRANSACTION and tries to delete the row from the table: 3839 mysql> start transaction; 4041 Query OK, 0 rows affected (0.00 sec) 4243 4445 mysql> delete from t WHERE I = 1; 4647 the DELETE operation requires an X lock. Because the lock is not compatible with the S lock held by client A, the X lock is not allowed, so the request enters the lock request queue blocked by the row and client. 4849 finally, client A tries to DELETE the row FROM the table: 5051 mysql> delete from t WHERE I = 1; 5253 ERROR 1213 (40001): Deadlock found when trying to get lock; 5455try restarting transaction

BitsCN.com