mysql先删除后插入报错 mysql先删再新增死锁

转载

mob64ca14147fe3 2023-12-14 19:17:00

文章标签 mysql先删除后插入报错 mysql 数据库经验分享死锁 文章分类 MySQL 数据库

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)
------------