一 前言
死
锁，其实是一个很有意思，也很有挑战的技术问题，大概每个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
T2
|
|
begin;
|
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)
--结束END--
本文标题: 【MySQL】死锁案例之三
本文链接: https://lsjlt.com/news/46970.html(转载时请注明来源链接)
有问题或投稿请发送至:
邮箱/279061341@qq.com QQ/279061341
0