1 Overview
InnoDB自动检测死锁。如果死锁发生，那么InnoDB会回滚权重相对小的事务。实际上，InnoDB中存在以下两种类型的死锁：
- 真正的事务间循环等待。
- 在进行死锁检测的过程中，如果InnoDB认为检测的代价过大（例如需要递归检查超过200个事务等），那么InnoDB放弃死锁检测，并认为死锁发生。
本文中使用的MySQL版本: 5.1.42，InnoDB plugin版本: 1.0.6。
2 Scenarios
如果死锁发生，除了应用程序的日志之外，最有价值的信息恐怕就是show innodb status的输出了，然而show innodb status的输出中死锁相关的信息并不完整（例如只记录导致死锁的最后两个事务，以及最后执行的两个SQL等）。 基于在日常工作中的经验，笔者总结了以下一定/可能会导致死锁的场景。
2.1 Scenario 1
CREATE TABLE test(id INT PRIMARY KEY, name VARCHAR(10)) ENGINE=InnoDB;
INSERT INTO test VALUES(1, '1'), (2, '2');
SET @@tx_isolation = 'READ-COMMITTED';
|
Session A
|
Session B
|
| START TRANSACTION; |
START TRANSACTION; |
| UPDATE test SET name = '11' WHERE id = 1; |
|
|
UPDATE test SET name = '22' WHERE id = 2; |
|
UPDATE test SET name = ‘21' WHERE id = 2;
# BLOCKED
|
|
|
UPDATE test SET name = ‘12' WHERE id = 1;
# DEADLOCK
|
点评：这是最常见的死锁场景之一，解决方法就是resource ordering，即确保所有关联事务均以相同的顺序持有锁。
2.2 Scenario 2
CREATE TABLE t (id INT PRIMARY KEY, count INT) ENGINE = InnoDB;
INSERT INTO t VALUES(1, 1);
SET @@tx_isolation = 'READ-COMMITTED';
|
Session A
|
Session B
|
| START TRANSACTION; |
START TRANSACTION; |
| SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; |
|
|
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE; |
|
UPDATE t SET count = 2 WHERE id = 1;
# BLOCKED
|
|
|
UPDATE t SET count = 3 WHERE id = 1;
# DEADLOCK
|
点评：在这种场景下，resource ordering也无济于事，SELECT ... LOCK IN SHARE MODE 调整为SELECT ... FOR UPDATE即可。
2.3 Scenario 3
CREATE TABLE parent(id int PRIMARY KEY, count INT) ENGINE=InnoDB;
CREATE TABLE child(id int PRIMARY KEY, parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent(id)) ENGINE=InnoDB;
INSERT INTO parent VALUES(1, 0);
SET @@tx_isolation = 'READ-COMMITTED';
|
Session A
|
Session B
|
| START TRANSACTION; |
START TRANSACTION; |
| INSERT INTO child VALUES(1, 1); |
|
|
INSERT INTO child VALUES(2, 1); |
|
UPDATE parent SET count = count + 1 WHERE id = 1;
# BLOCKED
|
|
|
UPDATE parent SET count = count + 1 WHERE id = 1;
# DEADLOCK
|
点评：在进行外键完整性检查时，InnoDB会在被检查的记录上设置一把共享读锁。本例中，在对child进行插入时，parent表中id为1的记录也被设置了共享读锁。
需要注意的是，CAS SSO在登录时进行了类似的数据库操作，因此也存在潜在的死锁可能性。
2.4 Scenario 4
CREATE TABLE parent(id int PRIMARY KEY, count INT) ENGINE=InnoDB;
CREATE TABLE child(id int PRIMARY KEY, parent_id INT) ENGINE=InnoDB;
INSERT INTO parent VALUES(1, 0);
SET @@tx_isolation = 'READ-COMMITTED';
|
Session A
|
Session B
|
... |
Session N
|
| START TRANSACTION; |
START TRANSACTION; |
|
START TRANSACTION; |
| INSERT INTO child VALUES(1, 1); |
INSERT INTO child VALUES(2, 1); |
|
INSERT INTO child VALUES(n, 1); |
| UPDATE parent SET count = count + 1 WHERE id = 1; |
UPDATE parent SET count = count + 1 WHERE id = 1; |
|
UPDATE parent SET count = count + 1 WHERE id = 1; |
| Deadlock may occur in some sessions. |
点评：以上场景中，如果N>200，并且这些事务并发执行，那么可能会导致死锁，并且一部分事务被会滚。这是第二种类型死锁的典型场景。在show innodb status的输出中会包含如下内容：“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH”。
需要注意的是，在UPDATE parent SET count = count + 1 WHERE id = 1;语句之前进行过何种操作并不重要，关键是这些事务都并发更新同一条记录，最终导致InnoDB放弃了死锁检测。
2.5 Scenario 5
CREATE TABLE test(id varchar(10) primary key, count int) ENGINE=InnoDB;
INSERT INTO test values('ID00000001', 0), ('ID00000002', 0), ('ID00000003', 0);
SET @@tx_isolation = 'READ-COMMITTED';
|
Session A
|
Session B
|
| START TRANSACTION; |
START TRANSACTION; |
|
update test inner join (select *, sleep(15) from test where id <= 'ID00000002') t on test.id = t.id set test.count = 1;
# SLEEPING
|
|
|
update test set count = 3 where id = 'ID00000001';
# BLOCKED
|
| ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction # 15 × 2 （2 records selected）seconds later |
|
点评：由于InnoDB采用了MVCC，因此在通常情况下（非SERIALIZABLE事务隔离级别），普通的SELECT语句不会对查询结果集中的记录加锁，也不会被已有的锁阻塞住。但是，InnoDB会在update语句的select子句的查询结果集的每条记录上设置一把共享读锁。这是本例中导致死锁的原因。
需要注意的是，本例中select子句中的sleep函数调用只是为了更容易地重现死锁，并没有其它特殊作用。 针对这种类型的死锁，最好还是调整业务逻辑，正如本例中Session A的update语句试图有条件的更新test表的部分记录，应该调整该update语句以避免死锁。
2.6 Scenario 6
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(10)) ENGINE = InnoDB;
SET @@tx_isolation = 'SERIALIZABLE';
|
Session A
|
Session B
|
| START TRANSACTION; |
START TRANSACTION; |
| select * from t1 where id = 1; |
|
|
select * from t1 where id = 1; |
|
insert into test values(1, 'a');
# BLOCKED
|
|
|
insert into test values(1, 'a');
# DEADLOCK
|
点评：在SERIALIZABLE事务隔离级别下，如果autocommit被禁用，那么InnoDB会隐式地将普通的SELECT语句转换为SELECT ... LOCK IN SHARE MODE，即在查询结果集的每条记录上设置共享读锁。
需要注意的是，如果完全采用默认配置，那么Spring Batch 2.0.0会在SERIALIZABLE事务隔离级别下进行类似的数据库操作，最终可能导致死锁。如果使用MySQL存储Spring Batch相关的数据库表，那么需要调整Spring Batch的配置，将事务隔离级别从默认的SERIALIZABLE调整为REPEATABLE READ。
2.7 Scenario 7
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
SET @@tx_isolation = 'READ-COMMITTED';
|
Session A
|
Session B
|
Session C
|
| START TRANSACTION; |
START TRANSACTION; |
START TRANSACTION; |
| INSERT INTO t1 VALUES(1); |
|
|
|
INSERT INTO t1 VALUES(1);
# BLOCKED
|
INSERT INTO t1 VALUES(1);
# BLOCKED
|
| ROLLBACK; |
|
|
|
Deadlock occurs in either Session B or Session C |
点评：这种类型的死锁不常见，如果发生duplicate-key error，那么InnoDB会在重复的索引记录上设置一把共享读锁，最终导致了本例中的死锁。
分享到：
相关推荐
近似动态规划方法，百度apollo2.0系统里planning模块...（Optimal Trajectory Generation for Dynamic Street Scenarios in a Frene´t Frame Moritz Werling, Julius Ziegler, So¨ren Kammel, and Sebastian Thrun）
day-to-day and practical scenarios are covered in this book. Chapter 1, MySQL 8 - Installing and Upgrading, describes how to install MySQL 8 on different flavors of Linux, upgrade to MySQL 8 from ...
WPF uncertainty in the stochastic UC alternative is captured by a number of scenarios that include crosstemporal dependency. A comparison among a diversity of UC strategies (based on a set of ...
宝马公司的工程师Moritz Werling在其发表的研究成果中，提出了一种在Frenet框架内针对动态城市街道情景的最优轨迹生成方法。此方法主要针对的是自动驾驶车辆在城市以及高速公路动态交通场景下的安全行驶问题。...
Functional Programming in C++ teaches developers the practical side of functional programming and the tools that C++ provides to develop software in the functional style. This in-depth guide is full ...
06 Typical Processes in FusionCloud 6.3 Scenarios.pptx
Java 5.0 is a huge step ...describing their behavior and features, we present the underlying design patterns and anticipated usage scenarios that motivated their inclusion in the platform libraries.
### SPLUS Server 部署方案详解 #### 概述 本文档旨在为采用S-PLUS企业服务器进行分析驱动、基于Web或定制客户端应用程序的部署提供几种样本场景，并重点介绍首次部署及后续更新中的架构与工作流程。...
In this book, you will see how Database Administrators (DAs) can use MySQL to handle billions of records and load and retrieve data with performance comparable or superior to commercial DB solutions ...
Unfortunately, exploring the complete myriad of scenarios that you may encounter in your application requirements simply isn’t possible, nor is it possible to cover every possible solution to a ...
MySQL的优化技巧，如索引使用、存储引擎选择（InnoDB for transactions, MyISAM for read-heavy scenarios）等，都是开发者需要掌握的关键知识。 PHP（Hypertext Preprocessor）是一种解释型的、面向对象的脚本语言...
采用Scenarios进行需求建模能够反映从用户角度观察到的系统的行为,状态图是系统行为的精确描述。从Scenario到状态图的自动转换是指软件开发过程中从UML需求模型自动生成行为模型的过程，其研究对于细化系统行为,保持...
AEC论文解读 - Deep Learning for Acoustic Echo Cancellation in Noisy and Double-Talk Scenarios
Windows Security Monitoring Scenarios and Patterns 英文epub 本资源转载自网络，如有侵权，请联系上传者或csdn删除 查看此书详细信息请在美国亚马逊官网搜索此书
Illuminating Statistical Analysis Using Scenarios and Simulations By 作者: Jeffrey E. Kottemann ISBN-10 书号: 1119296331 ISBN-13 书号: 9781119296331 Edition 版本: 1 出版日期: 2017-03-06 pages 页数: ...
"Optimal trajectory generation for dynamic street scenarios in a Frenet Frame"这个主题探讨的是如何在动态街景中为自动驾驶汽车生成最优轨迹，特别是在Apollo系统中的应用。Apollo是百度开发的一个开放源代码...
Functional Programming in C++ teaches developers the practical side of functional programming and the tools that C++ provides to develop software in the functional style. This in-depth guide is full ...
Deeper algorithm scenarios, such as navigation algorithms used most frequently in travel, greatly differ in required time and traffic conditions at different moments. Such sophistication would be ...
iAdSuite is a set of samples demonstrating how to manage an ADBannerView in many common scenarios, each scenario demonstrated in a particular sample application. In many of the samples the content is...