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）
You'll find plenty of examples using several language APIs in multiple scenarios and situations, includin retrieve and format data. There are also many new examples for using Perl, PHP, Python, and ...
Features an integrated approach of statistical scenarios and simulations to aid readers in developing key intuitions needed to understand the wide ranging concepts and methods of statistics and ...
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 Frame框架下的路径跟踪算法。
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 ...
TDOA positioning in NLOS scenarios by particle filtering
06 Typical Processes in FusionCloud 6.3 Scenarios.pptx
Many of these transformations are developed from real-world scenarios that are the result of key business decisions. In addition, you'll find formal definitions of refactoring techniques that you'll ...
Experiments under various scenarios show that, in terms of average query processing time, this hybrid caching approach outperforms the traditional approach, which relies only on the HTML cache.
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.
These “Troubleshooting Scenarios” differ from the “Labs” portion of the courseware in that they are not “click by click” guides but rather are loosely guided troubleshooting scenarios where ...
SPLUS是世界著名的统计分析软件，类似于SPSS和SAS，但SPLUS具有比后者更先进的架构，更主要的是有很强的开发能力。本文档就是介绍如何将分析驱动的，基于WEB的和自定义客户应用程序与SPLUS企业服务器集成的。
Optimal Trajectory Generation for Dynamic Street Scenarios in a Frenet Frame，该文章讲了FRNEET框架下动态街道场景的最优轨迹生成的方法，用于表示车辆与地图的相对位置。百度Apollo开源项目中应用到了此种方法...
The Fog Computing Paradigm: Scenarios andSEECS, University of Ottawa, Canada220
VMworld 2009 - PA4690:Successfully Selling VMware View in Competitve Scenarios
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 ...
采用Scenarios进行需求建模能够反映从用户角度观察到的系统的行为,状态图是系统行为的精确描述。从Scenario到状态图的自动转换是指软件开发过程中从UML需求模型自动生成行为模型的过程，其研究对于细化系统行为,保持...