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)
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博客 ” ，链接：https://blog.itpub.net/30024909/viewspace-1395172/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
MySQL SQL Mode
下一篇：
MySQL 锁问题
请登录后发表评论
登录
全部评论
