- 浏览: 388081 次
- 性别:
- 来自: 上海
文章分类
- 全部博客 (285)
- test (0)
- 分类 (1)
- java (281)
- tttttttttttt (1)
- android程序复制数据库 (1)
- Hibernate 动态 HQL (1)
- java编写扑克的洗牌程序 (1)
- NIO 之 选择就绪模式 (1)
- wpo另类问题:不可轻视的蜘蛛爬行对服务器造成的负担 (1)
- haml (1)
- cvcvcvc (1)
- 开源史上最成功的8个开源产品 (1)
- XML文件转换成Word文件或者Excel文件 (1)
- 一些利用开源浏览器核心开发专用浏览器的连接 (1)
- 毛笔效果简单思路 (1)
- 年终考评以后 (1)
- RFC821 简单邮件传输协议(SMTP)中文定义文档 (1)
- JNI中jstring类型与c语言中的字符串的转换 (1)
- 极限编程(XP)的重构与设计模式 (1)
- MapInfo2005年第三期电子新闻 (1)
- 想到目前各BLOG站点和搜索引擎的一点点缺陷 (1)
- 成功通过DB2的700和701考试 (1)
- 基于记录登陆信息的防止网页暴力破解方法 (1)
- Router路由 (1)
- 终于稍微完整的学习了一下Linux (1)
- android手机通讯录备份还原代码 (1)
- Qt之美(一):d指针/p指针详解 (1)
- DB2 静默安装 (1)
- linux开机启动脚本的顺序 (1)
- Hibernate实体对象的三种状态 (1)
- Hibernate面向对象的hql语句 (1)
- ibatIS调用存储过程 (1)
- Linux启动过程(详细说明) (1)
- C3P0配置 (1)
- memcache安装 (1)
- js event.keyCode (1)
- java获取汉子首字母 (1)
- Eclipse GC log (1)
- java轻量级httpserver (1)
最新评论
-
smilea001:
我知道了,作者采用的是gbk编码,我采用的是utf-8编码,
java获取汉子首字母 -
smilea001:
我输入的也是--
java获取汉子首字母 -
di1984HIT:
写的不错啊。
Router路由 -
kaixinyou:
...
2011.07.20——— android 获得当前view在屏幕的坐标 -
zhengjianbo:
你好,可以将你的实现代码公布下吗!万分感谢!
毛笔效果简单思路
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的记录也被设置了共享读锁。
需要注意的是,OpenSSO在登录时进行了类似的数据库操作,因此也存在潜在的死锁可能性。
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会在重复的索引记录上设置一把共享读锁,最终导致了本例中的死锁。
发表评论
-
java轻量级httpserver
2012-02-08 11:48 1564httpclient+httpserver demo ... -
Eclipse GC log
2012-02-04 16:59 1395为了查看Eclipse GC log,需要在eclip ... -
java获取汉子首字母
2012-02-03 09:14 1520public class StringUtil { ... -
js event.keyCode
2012-02-03 09:09 1202<span style="" ... -
memcache安装
2012-02-02 12:04 1143? <blockquote> 1. ... -
C3P0配置
2012-02-02 11:34 981cquireIncrement[3] ... -
Linux启动过程(详细说明)
2012-02-01 09:09 1560<div class="post ... -
ibatIS调用存储过程
2012-01-31 15:38 1603<p>一、ibatIS调用存储过程(调用存 ... -
Hibernate面向对象的hql语句
2012-01-11 13:14 998Hibernate中hql条件语句的书写方式有: ? ... -
Hibernate实体对象的三种状态
2012-01-11 12:59 1271<p style="text- ... -
linux开机启动脚本的顺序
2011-12-21 15:14 1103下开机自动启动脚本所涉及的知识和方法、如下: ... -
DB2 静默安装
2011-12-21 11:59 1051<span style="color: ... -
Qt之美(一):d指针/p指针详解
2011-12-20 15:14 1945[/b]2011.11.16 [size=18px;] ... -
android手机通讯录备份还原代码
2011-12-20 11:54 2939<span style="font-f ... -
终于稍微完整的学习了一下Linux
2011-12-19 12:14 1167Linux以前也装过两次,不过几乎没去用。头几天看一些 ... -
Router路由
2011-12-19 10:54 1157Router路由:设定线的轨迹,在Connection ... -
基于记录登陆信息的防止网页暴力破解方法
2011-12-17 10:49 1431对黑客方面比较感兴趣的或者是比较熟悉的,应该知道溯雪这 ... -
成功通过DB2的700和701考试
2011-12-16 17:27 859嘿嘿,原以为比较没有信心的701考试也被俺成功的攻克了 ... -
想到目前各BLOG站点和搜索引擎的一点点缺陷
2011-12-15 15:54 873感觉无聊,就到中国博客网的首页逛了逛,想看看有没有什么 ... -
MapInfo2005年第三期电子新闻
2011-12-15 10:49 897<span lang="EN-US&q ...
相关推荐
近似动态规划方法,百度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企业服务器集成的。
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需求模型自动生成行为模型的过程,其研究对于细化系统行为,保持...
Functional Programming in C# leads you along a path that begins with the historic value of functional ideas. Inside, C# MVP and functional programming expert Oliver Sturm explains the details of ...