关键词：
【中文标题】将共享锁升级为独占锁时避免 MySQL 死锁【英文标题】：Avoiding MySQL deadlock when upgrading shared to exclusive lock 【发布时间】：2016-12-07 11:05:48 【问题描述】：我使用的是 MySQL 5.5。我注意到在并发场景中发生了一个特殊的死锁，我认为这种死锁不应该发生。
像这样重现，使用同时运行的两个 mysql 客户端会话：
mysql 会话 1：
create table parent (id int(11) primary key);
insert into parent values (1);
create table child (id int(11) primary key, parent_id int(11), foreign key (parent_id) references parent(id));
begin;
insert into child (id, parent_id) values (10, 1);
-- this will create shared lock on parent(1)
mysql 会话 2：
begin;
-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- this will block because of shared lock in session 1
mysql 会话 1：
-- try and get exclusive lock on parent row
select id from parent where id = 1 for update;
-- observe that mysql session 2 transaction has been rolled back
mysql 会话 2：
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
show engine innodb status报告的信息是这样的：
------------------------
LATEST DETECTED DEADLOCK
------------------------
161207 10:48:56
*** (1) TRANSACTION:
TRANSACTION 107E67, ACTIVE 43 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 13074, OS thread handle 0x7f68eccfe700, query id 5530424 localhost root statistics
select id from parent where id = 1 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E67 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000107e65; asc ~e;;
2: len 7; hex 86000001320110; asc 2 ;;
*** (2) TRANSACTION:
TRANSACTION 107E66, ACTIVE 52 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 12411, OS thread handle 0x7f68ecfac700, query id 5530425 localhost root statistics
select id from parent where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000107e65; asc ~e;;
2: len 7; hex 86000001320110; asc 2 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 3714 n bits 72 index `PRIMARY` of table `foo`.`parent` trx id 107E66 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000107e65; asc ~e;;
2: len 7; hex 86000001320110; asc 2 ;;
*** WE ROLL BACK TRANSACTION (1)
您可以看到事务 (1) 没有显示任何已获得的 S 或 X 锁；它只是被阻止试图获取独占锁。既然没有循环，这种情况应该不会出现死锁，据我了解。
这是一个已知的 MySQL 错误吗？其他人遇到过吗？使用了哪些解决方法？
这些是我们可以采取的可能步骤：
减少外键的使用（在我们的生产场景中，我们只软删除引用表中的行，但是很糟糕） 预先获取排他锁而不是隐式共享锁（会降低我们的并发吞吐量） 更改我们的逻辑，以便我们不再需要在添加子行的同一事务中对父级进行排他锁（风险和困难） 将我们的 MySQL 版本更改为不会出现这种行为的版本还有其他我们没有考虑的选择吗？
【问题讨论】：
刚刚复制了上面的步骤。 Mysql 5.1.73 UPD 上没有错误。但错误存在于 5.7.17，所以我认为，它是版本 > 5.5 的特定行为 bugs.mysql.com/bug.php?id=48652 特别是 Marko Mäkelä 于 2012 年 10 月 22 日 12:32 发表的评论。 【参考方案1】：这是一个长期存在的错误，您可以从以下网址了解更多信息：This bug report
这是 MySQL 级别的表锁定问题。
在 InnoDB 内部，FOREIGN KEY 约束检查可以读取（或者， 使用 ON UPDATE 或 ON DELETE 子句，写入）父表或子表。
通常，表访问由以下锁控制： 1. MySQL 元数据锁 2. InnoDB 表锁 3. InnoDB 记录锁
所有这些锁都被持有到事务结束。
在某些模式下会跳过 InnoDB 表和记录锁，但 不是在外键检查期间。死锁是因为 MySQL 只为显式的表获取元数据锁 SQL 语句中提到的。
我想一种解决方法可能是访问孩子（或父母） 事务开始时的表，在有问题的 FOREIGN 之前 按键操作。
阅读讨论和它的回复
【讨论】：
【参考方案2】：没有给出更新父行的原因， 但我认为这与一些去规范化有关，基于问题中的这个序列：
-- session 1
begin;
insert into child (id, parent_id) values (10, 1);
...
select id from parent where id = 1 for update;
例如，一个订单（父表）有一列金额， 它被维护为所有订单行的金额的总和（子 表）。
似乎维护父数据的逻辑是在应用程序中编码的 本身（带有显式更新语句），具有以下后果：
如果在许多不同的地方插入子元素， 那么客户端中的应用程序逻辑必须在所有这些地方更新 保持完整性。这是代码重复。
即使这仅在一个地方完成，父表的事实 当服务器无法找到添加孩子时，需要更新。
请考虑以下选项：
在子表上定义触发器，根据需要更新父表。
它具有以下含义：
首先，维护父表的逻辑不再（可能） 重复，因为它在触发器本身中。
第二，这是这里的重要部分，MySQL 服务器现在知道 每当插入子记录时，父表就会更新，并且因为 其中，会采用适当的锁（独占而不是共享）。
用 8.0 测试，见下文。
关于并发吞吐量的问题，
在不同父行上操作的不同事务将在 并行，因为排他锁是在父（不同）行上进行的，而不是 父表。
在同一父行上同时运行的事务确实会 被序列化......这实际上是预期的结果，因为它们完成 还是一样的记录。
保证成功的序列化事务应该提供更好的吞吐量（就应用程序工作负载而言）， 某些事务失败，只能重试。
显然，还需要更新和删除触发器，以更新父级，具体取决于应用程序逻辑。
设置
create table parent (
id int(11) primary key,
number_of_children int(11));
create table child (
id int(11) primary key,
parent_id int(11),
foreign key (parent_id) references parent(id));
delimiter $$;
create trigger bi_child before insert on child
for each row
begin
update parent
set number_of_children = number_of_children + 1
where id = NEW.parent_id;
end
$$
delimiter ;$$
begin;
insert into parent values (1, 0);
insert into parent values (2, 0);
commit;
会话 1
begin;
insert into child values (10, 1);
第 2 场
begin;
insert into child values (20, 2);
未阻止，因为使用了不同的父级。
第 3 节
begin;
-- this now blocks, waiting for an X lock on parent row 1.
insert into child values (11, 1);
会话 1
-- unlocks session 3
commit;
第 3 节
提交；
第 2 场
提交；
结果
select * from parent;
id number_of_children
1 2
2 1
【讨论】：
不，不是反规范化，逻辑不能作为触发器实现。父级的状态本质上表明后台处理正在发生。异步地，将行添加到引用父行的另一个表中。但是如果父行已经处于状态，我们不会启动异步作业。所以我们在父行上使用排他锁来控制与后台作业的同步。奇怪的是，应该是安全的序列变成了检测到的死锁。 @BarryKelly，很抱歉听到触发器不适用于您的情况。这个约束也没有记录在问题中......如果你问“我们没有考虑其他选项吗？”，我认为解释你想要实现的目标是公平的。 我知道。对不起。我将其缩减为一组最小的可重现步骤。在这样做的过程中，我超越了我的应用程序要求。但是，如果我描述了我的应用需求，我们就会进行更大范围的讨论。使用乐观锁时会不会出现死锁？
...Optimisticvs.Pessimisticlocking悲观锁定是指您锁定记录以供您独占使用直到你完成它。它的完整性比乐观锁定，但需要你小心你的避免Deadlocks的应用程序设计。还知道，Optimis 查看详情
aqs共享锁和独占锁
参考技术A本文使用ReentrantLock和CountDownLatch演示独占锁和共享锁的实现。独占锁获取锁释放锁共享锁通过status标识锁ReentrantLock使用排他锁。AQS的status>0表示加锁，thread是当前获取锁的线程。该锁时可重入锁，所以status>0。CountD... 查看详情
mysql面试题之死锁
...过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定2、合理设计索引。不经常使用的列最好不加锁3、尽可能减少基于范围的数据检索过滤条件 查看详情
MariaDB 避免死锁
...2:20【问题描述】：我原来的错误是错误号：1213-尝试获取锁时发现死锁；尝试重启交易好的，所以我写了一个循环，最大重试次数，中间有一个等待来尝试解决死锁。$Try=0;while(!$Result=$dbs->query($MySQL))$Try++;if($Try===MYSQL_MAX_RETRIES) 查看详情
避免活跃性
...个线程永远地持有一个锁，并且其他线程都尝试获得这个锁时，那么它们将永远被阻塞。 10.1.1锁顺序死锁 如果用固定的顺序来获取锁，就不会发生死锁。 10.1.2动态的锁顺序死锁解决方案 其中一个线程从X向Y转 查看详情
死锁的处理策略—预防死锁避免死锁检测和解除死锁
...抢才会导致死锁。如果把只能互斥使用的资源改造为允许共享使用，则系统不会进入死锁状态。比如:SPOOLing技术。操作系统可以采用SPOOLing技术把独占设备在逻辑上改造成共享设备。比如，用SPOOLing技术将打印机改造为共... 查看详情
死锁的处理策略—预防死锁避免死锁检测和解除死锁(代码片段)
...抢才会导致死锁。如果把只能互斥使用的资源改造为允许共享使用，则系统不会进入死锁状态。比如:SPOOLing技术。操作系统可以采用SPOOLing技术把独占设备在逻辑上改造成共享设备。比如，用SPOOLing技术将打印机改造为共... 查看详情
死锁的处理策略—预防死锁避免死锁检测和解除死锁(代码片段)
...抢才会导致死锁。如果把只能互斥使用的资源改造为允许共享使用，则系统不会进入死锁状态。比如:SPOOLing技术。操作系统可以采用SPOOLing技术把独占设备在逻辑上改造成共享设备。比如，用SPOOLing技术将打印机改造为共... 查看详情
java中死锁的简单例子及其避免
...个线程永远地持有一个锁，并且其他线程都尝试获得这个锁时，那么它们将永远被阻塞。比如，线程1已经持有了A锁并想要获得B锁的同时，线程2持有B锁并尝试获取A锁，那么这两个线程将永远地等待下去。我们来看一个死锁的简... 查看详情
mysql死锁(代码片段)
...三、MySQL如何处理死锁？杀死进程MySQL表间隙锁排他锁共享锁分析行锁定行锁优化四、如何避免发生死锁1.对索引加锁顺序的不一致很可能会导致死锁；2.Gap锁往往是程序中导致死锁的真凶；3.为表 查看详情
mysql死锁(代码片段)
...三、MySQL如何处理死锁？杀死进程MySQL表间隙锁排他锁共享锁分析行锁定行锁优化四、如何避免发生死锁1.对索引加锁顺序的不一致很可能会导致死锁；2.Gap锁往往是程序中导致死锁的真凶；3.为表 查看详情
aqs
...成功时将自己设置为首节点。如下图所示： 独占式/共享式锁获取独占式：有且只有一个线程能获取到锁，如：ReentrantLock。共享式：可以多个线程同时获取到锁，如：CountDownLatch独占式每个节点自旋观察自己的前一节点是不... 查看详情
mysql-死锁的产生及解决方案(代码片段)
...锁产生的4个必要条件2.死锁案例2.1表锁死锁2.2行锁死锁2.3共享锁转换为排他锁3.死锁排查4.实例分析4.1案例描述4.2案例死锁问题复现4.3死锁排查4.4解决死锁5.如何避免死锁1.死锁与产生死锁的四个必要条件1.1什么是死锁死锁是指2... 查看详情
mysql----共享锁独占锁行锁表锁(代码片段)
锁1、一致性读(ConsitentReads)2、锁定读(LockingReads)2.1共享锁2.2独占锁2.2锁定读语句3、行锁3.1行锁3.1.1RecordLocks(记录锁)3.1.2GapLocks3.1.3Next-KeyLock3.1.4InsertIntentionLocks3.1.5隐式锁3.2表锁3.2.1表锁的S、X锁3.2.2表锁意向锁的IS、IX锁3.2.3表级别的... 查看详情
怎么避免mysql死锁
怎么避免mysql死锁1、以固定的顺序访问表和行。比如两个更新数据的事务，事务A更新数据的顺序为1,2；事务B更新数据的顺序为2，1；。这样更可能会造成死锁。2、大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆... 查看详情
当多个对象持有一个共享锁并且一个想要升级到独占时，sql中基于锁的并发控制如何工作？
】当多个对象持有一个共享锁并且一个想要升级到独占时，sql中基于锁的并发控制如何工作？【英文标题】：Howdoeslock-basedconcurrencycontrolinsqlworkwhenmultipleobjectsholdasharedlockandonewantstoupgradetoexclusive?【发布时间】：2013-12-2716:21:53【问... 查看详情
多线程死锁避免
...价，另一个用户继续执行，牺牲的事物将回滚。 死锁避免加锁顺序（线程按照一定的顺序加锁）加锁时限（线程尝试获取锁的时候加上一定的时限，超过时限则放弃对该锁的请求，并释放自己占有的锁）死锁检测 查看详情
mysql中的锁机制
...段只能进行加锁操作。如果事务要读取对象，必须先获得共享锁。可以有多个事务同时获得一个对象的共享锁如果事务要修改对象，必须先获得独占锁。只能有一个事务获得对象的独占锁。如果某个事务已经获得了对象的 查看详情