所有分类
  • 所有分类
  • 未分类

MySQL–死锁的原因及解决方法

简介

说明

本文介绍MySQL死锁的原因及解决方法。产生死锁的必要条件是:这里

InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,所以可能发生死锁。

不会死锁的情况

MyISAM表不会出现死锁(Deadlock Free) 。

原因:用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及到表的锁,并且MySQL不支持锁升级。也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MyISAM总是一次获得SQL语句所需要的全部锁。

导致死锁的场景

先申请共享锁后申请排它锁(同一张表)

在事务中,更新记录时应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁。因为当申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。

下面的例子中,先申请共享锁,更新时再申请排他锁,造成死锁。

session_1session_2
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 = 178; +———-+————+———–+ | actor_id | first_name | last_name | +———-+————+———–+ | 178      | LISA       | MONROE    | +———-+————+———–+ 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 = 178; +———-+————+———–+ | actor_id | first_name | last_name | +———-+————+———–+ | 178      | LISA       | MONROE    | +———-+————+———–+ 1 row in set (0.00 sec)
当前session对actor_id=178的记录加共享锁: mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode; +———-+————+———–+ | actor_id | first_name | last_name | +———-+————+———–+ | 178      | LISA       | MONROE    | +———-+————+———–+ 1 row in set (0.01 sec)
其他session仍然可以查询记录,并也可以对该记录加共享锁: mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode; +———-+————+———–+ | actor_id | first_name | last_name | +———-+————+———–+ | 178      | LISA       | MONROE    | +———-+————+———–+ 1 row in set (0.01 sec)
当前session对锁定的记录进行更新操作,等待锁: mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; 等待
其他session也对该记录进行更新操作,则会导致死锁退出: mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
获得锁后,可以成功更新: mysql> update actor set last_name = ‘MONROE T’ where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched: 1  Changed: 1  Warnings: 0

访问两个表的顺序不同

如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。

下面的例子中,由于两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可以避免。

session_1session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 1 for update; +————+———–+ | first_name | last_name | +————+———–+ | PENELOPE   | GUINESS   | +————+———–+ 1 row in set (0.00 sec)
mysql> insert into country (country_id,country) values(110,’Test’); Query OK, 1 row affected (0.00 sec)
mysql>  insert into country (country_id,country) values(110,’Test’); 等待
mysql> select first_name,last_name from actor where actor_id = 1 for update; +————+———–+ | first_name | last_name | +————+———–+ | PENELOPE   | GUINESS   | +————+———–+ 1 row in set (0.00 sec)
发生死锁 mysql>  insert into country (country_id,country) values(110,’Test’); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

表数据操作顺序不一致

程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。

下面的例子中,处理数据的顺序不同,导致死锁。

session_1session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 1 for update; +————+———–+ | first_name | last_name | +————+———–+ | PENELOPE   | GUINESS   | +————+———–+ 1 row in set (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 3 for update; +————+———–+ | first_name | last_name | +————+———–+ | ED         | CHASE     | +————+———–+ 1 row in set (0.00 sec)
mysql> select first_name,last_name from actor where actor_id = 3 for update; 等待
mysql> select first_name,last_name from actor where actor_id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> select first_name,last_name from actor where actor_id = 3 for update; +————+———–+ | first_name | last_name | +————+———–+ | ED         | CHASE     | +————+———–+ 1 row in set (4.71 sec)

重复读插入数据(同一张表)

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

下面的例子中,隔离级别为重复读,导致死锁。

session_1session_2
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)
当前session对不存在的记录加for update的锁: mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)
其他session也可以对不存在的记录加for update的锁: mysql> select actor_id,first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)
因为其他session也对该记录加了锁,所以当前的插入会等待: mysql> insert into actor (actor_id , first_name , last_name) values(201,’Lisa’,’Tom’); 等待
因为其他session已经对记录进行了更新,这时候再插入记录就会提示死锁并退出: 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
由于其他session已经退出,当前session可以获得锁并成功插入记录: mysql> insert into actor (actor_id , first_name , last_name) values(201,’Lisa’,’Tom’); Query OK, 1 row affected (13.35 sec)

提交读插入数据(同一张表)

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

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

session_1session_2session_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.01 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.01 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.01 sec)
Session_1获得for update的共享锁: mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)由于记录不存在,session_2也可以获得for update的共享锁: mysql> select actor_id, first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec)
Session_1可以成功插入记录: mysql> insert into actor (actor_id,first_name,last_name) values(201,’Lisa’,’Tom’); Query OK, 1 row affected (0.00 sec)
Session_2插入申请等待获得锁: mysql> insert into actor (actor_id,first_name,last_name) values(201,’Lisa’,’Tom’); 等待
Session_1成功提交: mysql> commit; Query OK, 0 rows affected (0.04 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; 等待
这个时候,如果session_2直接对记录进行更新操作,则会抛出死锁的异常: mysql> update actor set last_name=’Lan’ where actor_id = 201; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Session_2释放锁后,session_3获得锁: mysql> select first_name, last_name from actor where actor_id = 201 for update; +————+———–+ | first_name | last_name | +————+———–+ | Lisa       | Tom       | +————+———–+ 1 row in set (31.12 sec)

死锁排查

如果出现死锁,可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息:

  1. 引发死锁的SQL语句
  2. 事务已经获得的锁
  3. 正在等待什么锁
  4. 被回滚的事务

据此可以分析死锁产生的原因和改进措施。

下面是一段SHOW INNODB STATUS输出的样例:

mysql> show innodb status \G
…….
------------------------
LATEST DETECTED DEADLOCK
------------------------
070710 14:05:16
*** (1) TRANSACTION:
TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS thread id 1197328736 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216
MySQL thread id 7521657, query id 673468054 localhost root update
insert into country (country_id,country) values(110,'Test')
………
*** (2) TRANSACTION:
TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS thread id 1164048736 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 7521664, query id 673468058 localhost root statistics
select first_name,last_name from actor where actor_id = 1 for update
*** (2) HOLDS THE LOCK(S):
………
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
………
*** WE ROLL BACK TRANSACTION (1)
……
0

评论0

请先

显示验证码
没有账号?注册  忘记密码?

社交账号快速登录