MySQL-InnoDB死锁

daliucheng

已于 2022-06-05 17:15:08 修改

阅读量415

点赞数

分类专栏：数据库文章标签： mysql 数据库经验分享

于 2022-06-05 17:11:51 首次发布

本文链接：https://blog.csdn.net/daliucheng/article/details/125134094

版权

数据库专栏收录该内容

6 篇文章 1 订阅

订阅专栏

MySQL-InnoDB死锁

死锁是指不同的事务不能继续运行，每个事务都持有对方需要的锁。两个事务都在等同一个资源变得可用。

死锁例子

需要两个客户端：A和B。先看A

A客户端先创建了一个表，插入了一条数据。之后开启了一个事务，获取S锁，查询。

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+

B客户端

B客户端开启事务，delete操作，delete操作要获取X锁。但是这一行数据，被A客户端持有S锁，X锁和S锁是互斥的，所以，B客户端只能等待A释放S锁之后，才可以删除。

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

于此同时，A客户端也想删除i=1的数据

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

死锁发生，A客户端想要删除，需要获取X锁，但它的前面B客户端还在等待S锁。A客户端不能直接将S锁升级为X锁，这就造成了，B在等待A释放S锁，A在等待B释放X锁。发生死锁。

死锁检测

InnoDB提供了死锁检测机制，默认是启用的。InnoDB提供了自动检测机制，采取情况，回滚一个或者多个事务来打破这种死锁。他会选择代价小的的事务来回滚。

怎么来判断代价小？

事务插入，更新，删除的行数决定。

死锁检测开启

innodb_deadlock_detect
锁分为表级锁和行级锁，这里的检测有区分吗？

表锁innodb_table_locks = 1 （默认就是1） and autocommit = 0,知道表级锁和行级锁。

行锁：行锁一开始是支持的。

处理死锁

这里说的是最小化的处理死锁。

首先说，死锁是事务性数据库的一个经典的问题，但是他们也并不是谈虎色变，除非这些问题经常出现，在正常的情况下，在事务里面的操作需要考虑到因为死锁导致事务重新开始。

可以通过以下技巧来应对死锁并减少其发生的可能性:

通过SHOW ENGINE INNODB STATUS 来看死锁产生的原因。

------------------------

LATEST DETECTED DEADLOCK

------------------------

2022-06-05 07:03:25 140017239475968

*** (1) TRANSACTION:

TRANSACTION 31017, ACTIVE 5 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 1128, 1 row lock(s)

MySQL thread id 617, OS thread handle 140016743249664, query id root updating

/* ApplicationName=DataGrip 2021.2.2 */ DELETE FROM t WHERE i = 1

*** (1) HOLDS THE LOCK(S):

RECORD LOCKS space id 1058 page no 4 n bits 72 index GEN_CLUST_INDEX of table recover_data.t trx id 31017 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 6; hex 000000008f00; asc ;;

1: len 6; hex 000000007927; asc y’;;

2: len 7; hex 820000018d0110; asc ;;

3: len 4; hex 80000001; asc ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1058 page no 4 n bits 72 index GEN_CLUST_INDEX of table recover_data.t trx id 31017 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 6; hex 000000008f00; asc ;;

1: len 6; hex 000000007927; asc y’;;

2: len 7; hex 820000018d0110; asc ;;

3: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:

TRANSACTION 31018, ACTIVE 84 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s)

MySQL thread id 615, OS thread handle 140016787367680, query id root updating

/* ApplicationName=DataGrip 2021.2.2 */ DELETE FROM t WHERE i = 1

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1058 page no 4 n bits 72 index GEN_CLUST_INDEX of table recover_data.t trx id 31018 lock mode S

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 6; hex 000000008f00; asc ;;

1: len 6; hex 000000007927; asc y’;;

2: len 7; hex 820000018d0110; asc ;;

3: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1058 page no 4 n bits 72 index GEN_CLUST_INDEX of table recover_data.t trx id 31018 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

0: len 6; hex 000000008f00; asc ;;

1: len 6; hex 000000007927; asc y’;;

2: len 7; hex 820000018d0110; asc ;;

3: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (1)

------------
如果死锁经常的发生，使用 innodb_print_all_deadlocks变量来收集更加详细的信息（每个死锁的信息都会记录，不仅仅是最新的一条）。
事务要尽可能的小，消耗的时间尽可能的少。
在进行一系列的操作之后，立即提交事务，减少冲突，对于未提交的事务，不要让它有太长的时间。大事务是不好的。
如果用了锁定读，尝试用隔离级别低的事务。
在修改一个事务中的多个表或同一个表中的不同行时，在每个事务中使用相同的操作顺序。
添加索引，索引可以减少锁的范围。
少用显示的锁，如果可以接受一致性的读获取的数据，那就不要用显示锁。
使用事务，少用LOCK TABLES 语句。