【MySQL】死锁案例之三

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

一前言
死锁，其实是一个很有意思，也很有挑战的技术问题，大概每个DBA和部分开发朋友都会在工作过程中遇见过。关于死锁我会持续写一个系列的案例分析，希望能够对想了解死锁的朋友有所帮助。
二背景知识
2.1 insert 锁机制
在分析死锁案例之前，我们先学习一下背景知识 insert 语句的加锁策略。我们先来看看官方定义:

sess1

sess2

begin;

delete from t8 where b = 1;

begin;

insert into t8 values (NULL,1);

commit；

update t8 set

begin;

insert into t7(id,a) values(26,10);

insert into t7(id,a) values(30,10);

insert into t7(id,a) values(40,9);

3.3 死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-17 15:15:03 7f78eac15700
*** (1) TRANSACTION:
TRANSACTION 462308661, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
insert into t7(id,a) values(30,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308661 lock mode S waiting
*** (2) TRANSACTION:
TRANSACTION 462308660, ACTIVE 43 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
insert into t7(id,a) values(40,9)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)