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
浏览: 32812 次
来自: 上海
多谢，无私的分享
gjs_nj 写道打不开啊可以找开了，原来是系统里需要设置，解 ...
终于找到个，太谢了一个MySQL死锁问题的解决 - zeromike - ITeye技术网站
博客分类：
PS本人个人情况:
因为我们用的是jbpm4，在100个并发测试下导致数据库死锁，jbpm4封装了插入数据库的操作，jbpm4用得是hibernate对数据库操作，对session的处理貌似没有处理并发的情况，我怀疑是否是这个原因，暂时怀疑，还未深究，我暂时对我们的方法加上synchronized，可以避免数据库死锁，也在寻找更好的解决办法。以下转载是我寻找死锁原因的时候，找到的文章，作为一个知识点留作备用，希望大牛们有更好的解决办法，赐教给小弟。
以下为转载:
近在项目开发过程中，碰到了数据库的死锁问题，在解决问题的过程中，加深了对MySQL InnoDB引擎锁机制的理解。
我们使用Show innodb status检查引擎状态时，发现了死锁问题：*** (1) TRANSACTION:TRANSACTION 0 , ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index readmysql tables in use 1, locked 1LOCK WAIT 3 lock struct(s), heap size 320MySQL thread id 83, query id
dcnet03 dcnet Searching rows for updateupdate TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME&date_sub(now(), INTERVAL 30 minute)*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0
lock_mode X locks rec but not gap waitingRecord lock, heap no 92 PHYSICAL RECORD: n_fields 11; info bits 00: len 8; hex 629c;; 1: len 6; hex
(; 2: len 7; hex 10; asc
@; 3: len 8; hex 50b2; asc
P ;; 4: len 8; hex 502a; asc
P*;; 5: len 8; hex 5426; asc
T&;; 6: len 8; hex d29c; asc
A,; 7: len 23; hex e636f6d2f6 870; /;; 8: len 8; hex 042b; asc
+;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 4e24; asc
N$;;*** (2) TRANSACTION:TRANSACTION 0 , ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499mysql tables in use 1, locked 13 lock struct(s), heap size 320, undo log entries 1MySQL thread id 84, query id
dcnet03 dcnet Updatingupdate TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `dcnet_db/TSK_TASK` trx id 0
lock_mode X locks rec but not gapRecord lock, heap no 92 PHYSICAL RECORD: n_fields 11; info bits 00: len 8; hex 629c;; 1: len 6; hex
(; 2: len 7; hex 10; asc
@; 3: len 8; hex 50b2; asc
P ;; 4: len 8; hex 502a; asc
P*;; 5: len 8; hex 5426; asc
T&;; 6: len 8; hex d29c; asc
A,; 7: len 23; hex e636f6d2f6 870; /hand.; 8: len 8; hex 042b; asc
+;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 4e24; asc
N$;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 0 page no 843102 n bits 600 index `KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0
lock_mode X locks rec but not gap waitingRecord lock, heap no 395 PHYSICAL RECORD: n_fields 3; info bits 00: len 8; hex 0425; asc
%;; 1: len 8; hex d29c; asc
A,; 2: len 8; hex 629c;;*** WE ROLL BACK TRANSACTION (1)该死锁问题涉及TSK_TASK表，该表用于保存系统监测任务，相关字段及索引如下：ID：主键；MON_TIME：监测时间；STATUS_ID：任务状态；索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。经分析，涉及的两条语句应该不会涉及相同的TSK_TASK记录，那为什么会造成死锁呢？查询MySQL官网文档，发现这跟MySQL的索引机制有关。MySQL的InnoDB引擎是行级锁，我原来的理解是直接对记录进行锁定，实际上不是这样的，其要点如下：不是对记录进行锁定，而是对索引进行锁定；在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking；如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID & 10000会锁定所有主键大于等于1000的所有记录，在该语句完成之前，你就不能对主键等于10000的记录进行操作；当非簇索引(non-cluster index)记录被锁定时，相关的簇索引(cluster index)记录也需要被锁定才能完成相应的操作。再分析一下发生问题的两条SQL语句，就不难找到问题所在了：当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () where STATUS_ID=1061 and MON_TIME&date_sub(now(), INTERVAL 30 minute)”执行时，MySQL会使用KEY_TSKTASK_MONTIME2索引，因此首先锁定相关的索引记录，因为 KEY_TSKTASK_MONTIME2是非簇索引，为执行该语句，MySQL还会锁定簇索引（主键索引）。假设“update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in (9921180)”几乎同时执行时，本语句首先锁定簇索引(主键)，由于需要更新STATUS_ID的值，所以还需要锁定 KEY_TSKTASK_MONTIME2的某些索引记录。这样第一条语句锁定了KEY_TSKTASK_MONTIME2的记录，等待主键索引，而第二条语句则锁定了主键索引记录，而等待KEY_TSKTASK_MONTIME2的记录，这样死锁就产生了。我们通过拆分第一条语句解决了死锁问题：即先查出符合条件的ID：select ID from TSK_TASK where STATUS_ID=1061 and MON_TIME & date_sub(now(), INTERVAL 30 minute)；然后再更新状态：update TSK_TASK set STATUS_ID=1064 where ID in (….)
这样就不会产生索引的竞争问题，死锁问题就解决了。
zhangzhaoaaa
浏览: 590765 次
来自: 北京
Ajaxfileupload异步上传隐藏“选择提交”，不同浏览 ...
document.ready 是页面加载完成的时候执行的。wi ...
楼主你好，现在测试发现有个问题，modifyParameter ...
c_yang13 写道博主能不能说清楚 代码2：调用请求路径中 ...mysql数据库中有一个表A，用户每天都会更新或修改该表的数据，日访问量20W左右。有一java程序每隔30S就会从该表中将最新的数据同步到另外一个表B（将表A的最新数据查出来插入表B）。在这一过程中经常出现锁等待超时和死锁现象（出现这一原因是java程序查表A的时候有用户去修改表A的数据或是有用户在修改表A占用的时间过长java程序去查表A时等待超时），求高手该怎么去解决呢？
该问题被发起重新开启投票
投票剩余时间：
之前被关闭原因：
该问题被发起删除投票
投票剩余时间：
距离悬赏到期还有：
参与关闭投票者：
关闭原因：
该问题已经被锁定
锁定原因：（）
保护原因：避免来自新用户不合宜或无意义的致谢、跟帖答案。
该问题已成功删除，仅对您可见，其他人不能够查看。
1.数据Ａ表　同步　到数据Ｂ表　为什么要查出来再更新到Ｂ表呢？　　myisam锁表，innodb锁行都是需要等待的，且耗资源，如果可以的话，如果可以，可以做个主　从走日志同步。2.等待超时这个问题，我们可以从两方面来聊一下。　a.数据表用的存储引擎　b.合理索引，只有理解了schema设计，索引设计才能写出更好的sql　　所以优化sql也是必要的。
德问是一个专业的编程问答社区，请
后再提交答案
关注该问题的人
共被浏览 (2984) 次MySQL数据表死锁解决办法
删除表的时候，突然发现数据表被锁住，于是想要查看一下MySQL的连接数
此前提是登录用户为root，如果是其他用户则只能查看到自己的连接。
如图13是当前的连接。
关闭用不到的连接
mysql& kill 4;
则无关连接已经被杀死。
继续杀死Id为5的连接就ok。
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。