MySQL死锁

InnoDB存储引擎中死锁的例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from emp t where t.id = 1 for update; +------+ | id   | +------+ |    1 | |    1 | +------+ 2 rows in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql> select * from emp1 t where t.id = 1 for update; +------+ | id   | +------+ |    1 | +------+ 1 row in set (0.00 sec)
mysql> select * from emp1 t where t.id = 1 for update; +------+ | id   | +------+ |    1 | +------+ 1 row in set (8.74 sec)
	mysql> select * from emp t where t.id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题

session_1	session_1
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation  | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id , first_name, last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)
	mysql> select actor_id , first_name, last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)
mysql>  insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); .....等待
	mysql>  insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>  insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); Query OK, 1 row affected (8.85 sec)

当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。

对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁

mysql> set session transaction isolation level  read committed;
Query OK, 0 rows affected (0.00 sec)

session_1	session_2	session_3
mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)	mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id, first_name , last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)	mysql> select actor_id, first_name , last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)
mysql>  insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); Query OK, 1 row affected (0.00 sec)
	mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); .....等待
mysql> commit; Query OK, 0 rows affected (0.02 sec)
	Session_2获得锁，发现插入记录主键重，这个时候抛出了异常，但是并没有释放共享锁 mysql> insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY'
		Session_3申请获得共享锁，因为session_2已经锁定该记录，所以session_3需要等待 mysql> select actor_id, first_name , last_name from actor where actor_id = 201 for update;
	mysql> update actor set last_name = 'Lan' where actor_id = 201; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0
		mysql> select actor_id, first_name , last_name from actor where actor_id = 201 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30024909/viewspace-1395172/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30024909/viewspace-1395172/