怎样让mysql 同时更新两个mysql数据库自动更新中的表

来源：蜘蛛抓取(WebSpider) 时间：2016-10-28 05:10 标签： java更新mysql数据库
mysql 2个sql更新不同记录但死锁 - 海阔天空 - ITeye技术网站
问题起因：
两条写sql，操作的记录没有任何冲突，但发生死锁
预备知识：
InnoDB行锁是通过给索引上的索引项加锁来实现的
创建测试表
CREATE TABLE `t1` (
`pk_id` INT(11) NOT NULL,
`type` INT(11) NOT NULL,
`status` INT(11) NOT NULL,
PRIMARY KEY (`pk_id`)
create index idx_type on t1(type);
create index idx_status on t1(status);
生成测试数据
INSERT INTO
t1 (pk_id,TYPE,STATUS)
例1.不一样的锁等待
SET autocommit=0;
SELECT * FROM t1 force index(PRIMARY) WHERE pk_id&4 AND TYPE=1 AND pk_id!=2 FOR UPDATE;
马上返回查到的结果有两条pk_id为1和3
SET autocommit=0;
SELECT * FROM t1 WHERE pk_id=2 FOR UPDATE;
执行后连接B一直是等待状态，如果连接A commit，连接B马上就执行完成
说明：连接A虽然查出来的结果只有pk_id为1和3的两条记录，但把pk_id为2的PRIMARY索引记录也锁住了，所以连接B一直等待
换个索引试试
SELECT * FROM t1 force index(idx_type) WHERE pk_id&4 AND TYPE=1 AND pk_id!=2 FOR UPDATE;
注意只换了force index使用的索引，其他都没变
在连接B里想写操作TYPE=1的记录(pk_id为1、2、3、6、7)都等待，因为连接A把idx_type中TYPE=1的记录都锁了
和之前例子对照可以发现，索引锁是按使用的索引来操作，并且可以确定的是锁的范围会超出查询结果范围，这点和一般以为的不一样，具体算法还有待研究。
COMMIT;SET autocommit=0;SELECT * FROM t1 WHERE pk_id&5 FOR UPDATE;
连接A先锁住了pk索引的部分记录
接着连接B执行
COMMIT;SET autocommit=0;
SELECT * FROM t1 FORCE INDEX (idx_status) WHERE STATUS=0 FOR UPDATE;
连接B锁往了idx_status的部分记录，再要锁pk时被连接A block，所以只能等待
最后连接A执行
UPDATE t1 SET STATUS=6 WHERE pk_id&5;
这时连接B报dead lock found
简单来讲连接A先锁住pk，B先锁住idx_status再拿pk就拿不到，这时A再拿idx_status就死锁了
类似于一个人有X但要Y，一个人有Y但要X，互不相让，就死锁了。
例3.想不到的死锁
把例1和例2的情况结合起来，就会出来本文最开始碰到的问题，想不到的死锁，即更新的记录完全不冲突，但就是死锁了
SELECT * FROM t1 force index(idx_type) WHERE pk_id&4 AND TYPE=1 FOR UPDATE;
update t1 set status=1 where pk_id=6
虽然想操作的记录不同，但锁的记录有相同的，所以也可能会死锁
例4.index merge死锁
如果sql where里同时使用了type和status，因为type和status上都有单字段索引，所以explain会发现使用了index merge
有的sql使用的索引是先idx_type再idx_status，有的先idx_status再idx_type
这样如果锁的记录有冲突，就可能和例3一样死锁了
解决方案：
1.只有一个pk，不要其他索引。这样只有lock wait，不会死锁
2.有多个index，但写数据时使用的都是同样的index组合
3.有多个index，按不同的index组合写数据，但逻辑上保证锁的记录不冲突
时间所限，只整理了大概的逻辑，一些细节未深入。有兴趣的可以看看mysql的next-key locking
浏览: 34716 次
来自: 上海
多谢，无私的分享
gjs_nj 写道打不开啊可以找开了，原来是系统里需要设置，解 ...
终于找到个，太谢了2271人阅读
数据库（19）
update table_a a set a.fa=(select b.fb from table_b b where a.id=b.id);
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：733746次
积分：8715
积分：8715
排名：第1866名
原创：208篇
转载：60篇
评论：83条
(1)(1)(2)(4)(2)(1)(5)(1)(1)(1)(2)(3)(1)(1)(4)(4)(4)(8)(4)(10)(4)(2)(1)(1)(1)(2)(1)(3)(1)(1)(3)(1)(1)(2)(1)(3)(9)(10)(5)(5)(9)(1)(4)(3)(2)(3)(1)(1)(5)(2)(4)(1)(5)(6)(4)(1)(3)(4)(2)(4)(6)(5)(3)(2)(4)(6)(2)(4)(1)(1)(2)(5)(1)(1)(7)(4)(3)(1)(2)(3)(2)(4)(3)(3)(4)(7)(3)2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
2015年6月 其他数据库开发大版内专家分月排行榜第二2015年5月 其他数据库开发大版内专家分月排行榜第二2013年6月 其他数据库开发大版内专家分月排行榜第二2013年3月 其他数据库开发大版内专家分月排行榜第二2012年12月 其他数据库开发大版内专家分月排行榜第二2012年10月 其他数据库开发大版内专家分月排行榜第二2012年9月 其他数据库开发大版内专家分月排行榜第二2012年7月 其他数据库开发大版内专家分月排行榜第二2012年6月 其他数据库开发大版内专家分月排行榜第二2011年11月 其他数据库开发大版内专家分月排行榜第二2011年10月 其他数据库开发大版内专家分月排行榜第二2011年9月 其他数据库开发大版内专家分月排行榜第二2011年6月 其他数据库开发大版内专家分月排行榜第二2011年5月 其他数据库开发大版内专家分月排行榜第二2011年4月 其他数据库开发大版内专家分月排行榜第二2011年3月 其他数据库开发大版内专家分月排行榜第二2011年2月 其他数据库开发大版内专家分月排行榜第二2010年12月 其他数据库开发大版内专家分月排行榜第二2010年11月 其他数据库开发大版内专家分月排行榜第二2010年9月 其他数据库开发大版内专家分月排行榜第二2010年7月 其他数据库开发大版内专家分月排行榜第二2010年4月 其他数据库开发大版内专家分月排行榜第二2010年3月 其他数据库开发大版内专家分月排行榜第二2010年1月 其他数据库开发大版内专家分月排行榜第二2009年12月 其他数据库开发大版内专家分月排行榜第二2008年6月 其他数据库开发大版内专家分月排行榜第二2008年5月 其他数据库开发大版内专家分月排行榜第二2008年4月 其他数据库开发大版内专家分月排行榜第二2007年5月 其他数据库开发大版内专家分月排行榜第二2007年1月 其他数据库开发大版内专家分月排行榜第二2006年10月 其他数据库开发大版内专家分月排行榜第二2006年8月 其他数据库开发大版内专家分月排行榜第二2006年6月 其他数据库开发大版内专家分月排行榜第二2006年4月 其他数据库开发大版内专家分月排行榜第二2005年9月 其他数据库开发大版内专家分月排行榜第二
2013年12月 其他数据库开发大版内专家分月排行榜第三2013年5月 其他数据库开发大版内专家分月排行榜第三2013年4月 其他数据库开发大版内专家分月排行榜第三2013年2月 其他数据库开发大版内专家分月排行榜第三2013年1月 其他数据库开发大版内专家分月排行榜第三2012年5月 其他数据库开发大版内专家分月排行榜第三2012年4月 其他数据库开发大版内专家分月排行榜第三2012年3月 其他数据库开发大版内专家分月排行榜第三2012年2月 其他数据库开发大版内专家分月排行榜第三2011年8月 其他数据库开发大版内专家分月排行榜第三2011年7月 其他数据库开发大版内专家分月排行榜第三2010年10月 其他数据库开发大版内专家分月排行榜第三2010年5月 其他数据库开发大版内专家分月排行榜第三2010年2月 其他数据库开发大版内专家分月排行榜第三2009年11月 其他数据库开发大版内专家分月排行榜第三2009年10月 其他数据库开发大版内专家分月排行榜第三2009年5月 其他数据库开发大版内专家分月排行榜第三2009年4月 其他数据库开发大版内专家分月排行榜第三2008年12月 其他数据库开发大版内专家分月排行榜第三2008年11月 其他数据库开发大版内专家分月排行榜第三2008年10月 其他数据库开发大版内专家分月排行榜第三2008年9月 其他数据库开发大版内专家分月排行榜第三2007年9月 其他数据库开发大版内专家分月排行榜第三2007年8月 其他数据库开发大版内专家分月排行榜第三2007年4月 其他数据库开发大版内专家分月排行榜第三2007年3月 其他数据库开发大版内专家分月排行榜第三2007年2月 其他数据库开发大版内专家分月排行榜第三2006年7月 其他数据库开发大版内专家分月排行榜第三2006年2月 其他数据库开发大版内专家分月排行榜第三2006年1月 其他数据库开发大版内专家分月排行榜第三2005年11月 其他数据库开发大版内专家分月排行榜第三2005年10月 其他数据库开发大版内专家分月排行榜第三
2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
2015年9月 其他数据库开发大版内专家分月排行榜第三2015年7月 MS-SQL Server大版内专家分月排行榜第三2015年6月 其他数据库开发大版内专家分月排行榜第三
匿名用户不能发表回复！|
每天回帖即可获得10分可用分！小技巧：
你还可以输入10000个字符
(Ctrl+Enter)
请遵守CSDN，不得违反国家法律法规。
转载文章请注明出自“CSDN（www.csdn.net）”。如是商业用途请联系原作者。

我要回帖

更多关于 java更新mysql数据库 的文章

 

随机推荐