发现2个死锁

Question

发现2个死锁
第一个死锁
insert into out(..) (),(),(),......  这里java一次性程序插入有30多条记录
该表有55万纪录.并发环境
PRIMARY KEY  (`id`,`mesId`),
  KEY `idx_O_ehistory` (`eId`),
  KEY `idx_O_typeId_y` (`typeId`),
  KEY `idx_isHistory` (`isHis`),
  KEY `idx_EP_islast` (`isLatest`)我已经检这55万纪录的表,这里isHis isLatest这2个字段是char(1)类型只有'Y' 'N'2个值.
我的想法是把这2个索引给去掉 , 因为55万里只有2个就没有多大意义的.至少提高插入速度.
out有15个字段.  这里向下 . 我在java的一次性执行插入多条记录insert into out(..) (),(),(),..... 
一般插入多少条综合比较最好(比如50条 100条等)?第2个死锁
INSERT INTO B0(..) (),(),(),......
  PRIMARY KEY USING BTREE (`id`,`mesId`) 
  KEY `idx_isHistory` (`isHis`),
  KEY `idx_outmessagd` (`outId`),
  KEY `idx_EP_islast` (`isLatest`)B0有40万纪录. 这里很困惑 PRIMARY KEY USING BTREE 这个pk 跟上面的PRIMARY KEY  (`id`,`mesId`)
区别在那里?(这次deadlock发生在早上3点系统最闲)

Answer 1

第2个死锁的详细信息如下RECORD LOCKS space id 0 page no 628541 n bits 216 index `PRIMARY` of table `db/B0` trx id 1 1319374827 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 109 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 8; hex 000002e3d87d0835; asc      } 5;; 1: len 8; hex 0000000001d58cf6; asc         ;; 2: len 6; hex 00014ea40fc8; asc   N   ;; 3: len 7; hex 80000409690146; asc     i F;; 4: len 4; hex 00007042; asc   pB;; 5: len 1; hex 81; asc  ;; 6: len 8; hex 8000012e0d90d52a; asc    .   *;; 7: len 1; hex 81; asc  ;; 8: len 1; hex 81; asc  ;; 9: SQL NULL; 10: SQL NULL; 11: len 17; hex 6d6174636869643d313837313132323938; asc matchid=187112298;; 12: len 8; hex 00000000002dc866; asc      - f;; 13: len 8; hex 0000000766014015; asc     f @ ;; 14: len 4; hex 80000001; asc     ;; 15: len 1; hex 81; asc  ;; 16: len 1; hex 81; asc  ;; 17: len 1; hex 80; asc  ;; 18: len 8; hex 800000000000007b; asc        {;; 19: len 1; hex 43; asc C;; 20: len 1; hex 4e; asc N;; 21: len 1; hex 59; asc Y;; 22: len 3; hex 303a30; asc 0:0;;
判断是 index `PRIMARY` of table
PRIMARY KEY USING BTREE (`id`,`mesId`)  估计这个发生的问题.这里很困惑 PRIMARY KEY USING BTREE 这个pk 跟上面的PRIMARY KEY (`id`,`mesId`)
区别在那里?  (需要把这个 pk修改为PRIMARY KEY (`id`,`mesId`) 吗?)

Answer 2

最好不要用insert into out (..) (),(),(),...... 的模式，因为这里有数据量的限制，有的默认是一次insert不能超过3000条吧。

Answer 3

第1个的详细信息
LATEST DETECTED DEADLOCK
------------------------
090728 5:06:47
*** (1) TRANSACTION:
TRANSACTION 0 412100899, ACTIVE 0 sec, process no 27899, OS thread id 1175083328 inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1216MySQL thread id 34501, query id 1170219884 prod2a 192.168.10.15 betbrain update
INSERT INTO Out (id, aId, b,c,e,f,g,h,i,k,...) values (8081,5209319,a,47,2,3,null,null,null,4.0,null,'C','N','Y') ,(8082,5209319,a,47,2,4,null,null,null,4.0,null,'C','N','Y')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 345492 n bits 232 index `PRIMARY` of table `db/Out` trx id 0 412100899 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;*** (2) TRANSACTION:
TRANSACTION 0 412100900, ACTIVE 0 sec, process no 27899, OS thread id 1179875648 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1216
MySQL thread id 34533, query id 1170219891 prod2b 192.168.10.25 betbrain update
INSERT INTO Out (id, aId, b,c,e,f,g,h,i,k,...) values (8083,5209320,a,43,1,1,27330,null,null,null,null,'C','N','Y') ,(8084,5209320,a,43,1,1,27357,null,null,null,null,'C','N','Y')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 345492 n bits 232 index `PRIMARY` of table `db/Out` trx id 0 412100900 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 345492 n bits 232 index `PRIMARY` of table `db/Out` trx id 0 412100900 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Answer 4

需要的，因为BTREE索引，跟默认的主键索引不同。

Answer 5

不好意思，上面高层了，btree是默认的索引类型。

Answer 6

楼上认为PRIMARY KEY USING BTREE 这个pk 跟上面的PRIMARY KEY (`id`,`mesId`)
2个pk一样的? 

Answer 7

我贴出的1个
MySQL thread id 34501, query id 1170219884 prod2a 192.168.10.15 betbrain update
INSERT INTO Out (id, aId, b,c,e,f,g,h,i,k,...) values (8081,5209319,a,47,2,3,null,null,null,4.0,null,'C','N','Y') ,(8082,5209319,a,47,2,4,null,null,null,4.0,null,'C','N','Y')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 345492 n bits 232 index `PRIMARY` of table `db/Out` trx id 0 412100899 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
和第2个
MySQL thread id 34533, query id 1170219891 prod2b 192.168.10.25 betbrain update
INSERT INTO Out (id, aId, b,c,e,f,g,h,i,k,...) values (8083,5209320,a,43,1,1,27330,null,null,null,null,'C','N','Y') ,(8084,5209320,a,43,1,1,27357,null,null,null,null,'C','N','Y')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 345492 n bits 232 index `PRIMARY` of table `db/Out` trx id 0 412100900 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0请看看 这表out  pk(id, aId).2个insert  sql的id不是相同的拉, 为什么会发生ECORD LOCKS space id 0 page no 345492 n bits 232 index `PRIMARY` of table `db/Out` trx id 0 412100900 lock_mode X的 锁??

Answer 8

8081,5209319
8082,52093198083,5209320
8084,5209320
你的主键索引是复合索引PRIMARY KEY (`id`,`mesId`),虽然这2条记录的ID不一样，但是mesId是重复了的，在索引树里，两条记录的索引位置是相同的。虽然记录不一样，但是用到的是同一个索引位置，也会发生死锁。你可以在插入的时候全部单条记录的插入看看。

Answer 9

抱歉  准确数据仔细核对 应该是这样的web机器1
8081,5209319
8082,5209319
...............web机器2
8083,5209320
8084,5209320
..........这个应用已经用了5年了,一直是这个逻辑的.
复合索引PRIMARY KEY (`id`,`mesId`)  虽然这2条记录的ID不一样，但是mesId是重复了的，在索引树里，两条记录的索引位置是相同的。虽然记录不一样，但是用到的是同一个索引位置，也会发生死锁  结合上面的信息如何理解?  (难道在web机器1提交的sql语句已经发生deadlock
show innodb status 提示是 web机器15209319和web机器25209320的sql发生死锁了
)  

Answer 10

２台web机器（java）　，一台数据库服务器（mysql），
先把问题简化如下,现在２台机器同时往数据库服务器同１表插入或者修改数据，　导致死锁２台web机器，分别是web１　web２
web１是update　a 　where a='N' and id in (1,2,3,4,54,45,4,5,45,4,5,4,5,4,5,4,5,.......)
web２是update　a 　where a='N' and id in (1,2,3,4,54,45,4,5,45,4,5,4,5,4,5,4,5,.......)
是同一时刻，结果导致发生死锁。
这a表没有pk , 问web程序或者db 该如何操作才能避免deadlock?问该如何解决或者避免这个现象？？

Answer 11

全部是innodb  大哥否则不会讨论我的解决方法是在session 里 
serializalbe 序列化读 

Answer 12

看了 mysql 的 文档 。该 文档 说 实际上 mysql  的 可重复读（默认）已经 能否 避免 phantom。
似乎并不 需要  serializalbe 序列化读 。---------------这里无法 确认现在 我 转移 思路 ， 从java代码 下手.现在 发现 代码 有些的 比如 上面的  out是 autocommit=true  insert 多条记录
                                B0 是 autocommit=false insert 多条记录
                                 a是 autocommit=false  update 多条记录                                －－－－－－－－现在 开始 质疑是否是 autocommit的原因？？

Answer 13

有可能的，你试试将autocommit设置成false或者0试试看。

Answer 14

在线并发环境 ，不是那么容易模拟环境的哦
代码 有些的 比如 上面的
  out是 autocommit=true insert 多条记录
  B0 是 autocommit=false insert 多条记录
  a是 autocommit=false update 多条记录 
若干年前已经设置了autocommit=false/true但无论什么情况
都发现都不能避免死锁 需要有建设性意见  （比如我降低隔离级别到  如READ COMMITTED。  或者将事务粒度减小
希望有实际运维体验的提出中肯看法
）

Answer 15

假设数据库是一个茅坑，嘎嘎
一人持续拉和多人并发拉是矛盾的，他们之间就会争抢茅坑~~
想要让（更多的人）（尽快地）进去，就要让进去的人别（总占住茅坑）
同样地，假设楼下买了票，就想围观5号坑主，
假设楼下的楼下也买了票，就想围观6号坑主，
这些人也会影响，他人如厕的速度，
你懂了么？呵呵~要求高的话，用mysql5.5或增大内存，换好硬盘试试吧

Answer 16

不管什么锁
现在发现丢失数据发现死锁的时候
web机器1
8081,5209319
8082,5209319
  这边的数据正常会插入...............web机器2
8083,5209320
8084,5209320 如果死锁 这边数据将丢失原理在于
发生deadlock后 innodb 一般都能自动检查到 并使得一个事务释放lock并回退
另外一个事物获得lock 继续 执行事. 这样只能保证一边数据不丢失问该如何让数据不丢?

Answer 17

`PRIMARY` of table `db/Out`
这个表 的pk (id,mesId) 2个字段是否是cluster的速度有问题.我思考把pk()------>唯一性索引 不知道能否解决问题