服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - 聊聊MySQL中的死锁

聊聊MySQL中的死锁

2023-11-10 01:00未知服务器之家 Mysql

死锁是指两个或者多个事务互相持有对方所需的资源,从而导致它们都无法继续执行的情况。下图是一个死锁的示例,事务1锁住了id=1的数据(比如更新id=1的数据记录),同时请求锁住id=2的数据,但事务2持有id=2的锁,同时又请求

死锁是指两个或者多个事务互相持有对方所需的资源,从而导致它们都无法继续执行的情况。下图是一个死锁的示例,事务1锁住了id=1的数据(比如更新id=1的数据记录),同时请求锁住id=2的数据,但事务2持有id=2的锁,同时又请求id=1的锁,这样就造成了相互等待对方释放锁的情况,从而产生了死锁:

聊聊MySQL中的死锁图片

上图是死锁产生的示例说明,我们用实际的SQL来演示死锁的产生,首先创建一个测试表,它只有两个字段,id和数量,id为自增类型,然后向表中插入两条数据:

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quantity` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `t_test` VALUES ('1', '1');
INSERT INTO `t_test` VALUES ('2', '2');

如果有两个事务更新表中id等于1和2的数据,但更新的顺序相反,像下面这样,就会出现死锁:

聊聊MySQL中的死锁图片

最后,事务2提示死锁的错误,而事务1则执行成功,当然,在事务的最后需要加上COMMIT语句。查询表中的数据进行确认,发现id=1的数量更新为了101,而id=2的数量更新成了102。

另外,由于sql执行较快,直接执行上面两个事务中的sql可能不会产生死锁的情况,我们可以稍做修改,也就在UPDATE语句后面加上SLEEP函数,SLEEP会让当前进程暂停执行指定的时间(单位为秒)。分别在两个事务中执行下面的语句,稍等几秒钟,就可以看到出现死锁:

# 事务1
START TRANSACTION;
UPDATE t_test SET quantity=101 WHERE id = 1;
SELECT SLEEP(10) FROM dual;
UPDATE t_test SET quantity=102 WHERE id = 2;
COMMIT;
# 事务2
START TRANSACTION;
UPDATE t_test SET quantity=201 WHERE id = 2;
SELECT SLEEP(10) FROM dual;
UPDATE t_test SET quantity=202 WHERE id = 1;
COMMIT;

在MySQL中,死锁检测的选项默认是开启的:innodb_deadlock_detect,如果InnoDB检测到死锁,则会把其中一个或者多个事务进行回滚,以这种方式来解决死锁,InnoDB会尝试回滚较小的事务。可以通过执行以下命令来查看死锁的检测情况:  

SHOW ENGINE INNODB STATUS;

比如以上两个事务执行以后,再执行上面的命令,就会看到以下的结果(只摘取死锁检测的部分),通过这种方式可以较为清晰的看到死锁的产生过程:

------------------------
 LATEST DETECTED DEADLOCK
 ------------------------
 2023-11-08 15:57:23 0x4df8
 *** (1) TRANSACTION:
 TRANSACTION 350231, ACTIVE 12 sec starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
 MySQL thread id 3, OS thread handle 19044, query id 339 localhost ::1 root updating
 UPDATE t_test SET quantity=102 WHERE id = 2
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 743 page no 3 n bits 72 index PRIMARY of table `test`.`t_test` trx id 350231 lock_mode X locks rec but not gap waiting
 Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  0: len 4; hex 80000002; asc     ;;
  1: len 6; hex 000000055818; asc     X ;;
  2: len 7; hex 2f000001401cb2; asc /   @  ;;
  3: len 4; hex 800000c9; asc     ;;
 
 *** (2) TRANSACTION:
 TRANSACTION 350232, ACTIVE 10 sec starting index read, thread declared inside InnoDB 5000
 mysql tables in use 1, locked 1
 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
 MySQL thread id 5, OS thread handle 19960, query id 340 localhost 127.0.0.1 root updating
 UPDATE t_test SET quantity=202 WHERE id = 1
 *** (2) HOLDS THE LOCK(S):
 RECORD LOCKS space id 743 page no 3 n bits 72 index PRIMARY of table `test`.`t_test` trx id 350232 lock_mode X locks rec but not gap
 Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  0: len 4; hex 80000002; asc     ;;
  1: len 6; hex 000000055818; asc     X ;;
  2: len 7; hex 2f000001401cb2; asc /   @  ;;
  3: len 4; hex 800000c9; asc     ;;
 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 743 page no 3 n bits 72 index PRIMARY of table `test`.`t_test` trx id 350232 lock_mode X locks rec but not gap waiting
 Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
  0: len 4; hex 80000001; asc     ;;
  1: len 6; hex 000000055817; asc     X ;;
  2: len 7; hex 2e0000018d1edf; asc .      ;;
  3: len 4; hex 80000065; asc    e;;
 
 *** WE ROLL BACK TRANSACTION (2)

从上面可以看出,MySQL可以检测到死锁,并通过回滚事务的方式来打破这种循环等待,但无论如何,在代码中还是需要尽量减少或者避免死锁的发生,可以尝试通过以下方法来达到这样的目的:

  • 让事务尽可能的小且短;
  • 合理设置事务隔离级别;
  • 合理设置锁等待超时时间;
  • 确定好事务操作的顺序;
  • 创建合适的索引,减少加锁的情况。

以上就是关于MySQL中的死锁介绍。在实际编码中,死锁也是较为常见的一种错误,如果对于它不了解,那么碰到这种异常的时候就会显得手足无措,希望本文有所帮助。

鸣谢:https://dev.mysql.com/doc/refman/5.7/en/

本文转载自微信公众号「互联网全栈架构」,可以通过以下二维码关注。转载本文请联系互联网全栈架构公众号。

聊聊MySQL中的死锁

延伸 · 阅读

精彩推荐