BYR Achieve · 镜像论坛

数据库环境是mysql5和hibernate3.3.2 多个线程并发更新同一张表时发生死锁表结构为： CREAT TABLE 't_gs_config' { 'serverId'' int(11), 'activityId' int(11), 'name' varchar(255), PRIMARY KEY(`serverId`, `activityId`) } ENGINE=InnoDB DEFAULT CHARSET=utf8; 用的是联合主键serverId和activityId。猜测当时场景为，表t_gs_config的内容为 serverId activityId name 41 40 s11 42 40 s22 43 40 s13 75 45 s75 76 45 s76 77 45 s77 session1: delete from t_gs_config where activityId=37; session2: insert into t_gs_config (name, activityId, serverId) values ('s70', 44, 70); insert into t_gs_config (name, activityId, serverId) values ('s71', 44, 71); insert into t_gs_config (name, activityId, serverId) values ('s72', 44, 72); 程序中session1对应一个简单的删除操作，session2对应一个批量插入数据的操作。 SHOW INNODB STATUS 的结果如下： OS WAIT ARRAY INFO: reservation count 35764, signal count 33602 Mutex spin waits 0, rounds 1470116, OS waits 17851 RW-shared spins 11703, OS waits 5177; RW-excl spins 22364, OS waits 8975 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 130815 0:00:29 *** (1) TRANSACTION: TRANSACTION 0 801846, ACTIVE 0 sec, process no 23665, OS thread id 1172085056 fetching rows mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1216, 26 row lock(s) MySQL thread id 213595, query id 7789690 127.0.0.1 ms_admin updating delete from t_gs_config where activityId=37 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 331 n bits 120 index `PRIMARY` of table `tr`.`t_gs_config` trx id 0 801846 lock_mode X waiting Record lock, heap no 33 PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 4; hex 80000436; asc 6;; 1: len 4; hex 8000002c; asc ,;; 2: len 6; hex 0000000c3c35; asc <5;; 3: len 7; hex 80000001bf0110; asc ;; 4: len 6; hex 202d20783738; asc - x78;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 4; hex 80000000; asc ;; *** (2) TRANSACTION: TRANSACTION 0 801845, ACTIVE 0 sec, process no 23665, OS thread id 1103411520 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 368, 2 row lock(s), undo log entries 1 MySQL thread id 213579, query id 7789700 127.0.0.1 ms_admin update insert into t_gs_config (name, activityId, serverId) values ('s71', 44, 71) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 331 n bits 120 index `PRIMARY` of table `tr`.`t_gs_config` trx id 0 801845 lock_mode X locks rec but not gap Record lock, heap no 33 PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 4; hex 80000436; asc 6;; 1: len 4; hex 8000002c; asc ,;; 2: len 6; hex 0000000c3c35; asc <5;; 3: len 7; hex 80000001bf0110; asc ;; 4: len 6; hex 202d20783738; asc - x78;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 4; hex 80000000; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 331 n bits 120 index `PRIMARY` of table `tr`.`t_gs_config` trx id 0 801845 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 33 PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 4; hex 80000436; asc 6;; 1: len 4; hex 8000002c; asc ,;; 2: len 6; hex 0000000c3c35; asc <5;; 3: len 7; hex 80000001bf0110; asc ;; 4: len 6; hex 202d20783738; asc - x78;; 5: len 4; hex 80000000; asc ;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80000000; asc ;; 8: len 4; hex 80000000; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 802503 Purge done for trx's n:o < 0 802455 undo n:o < 0 0 History list length 37 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 23665, OS thread id 1167026496 MySQL thread id 214325, query id 7803328 localhost ms_admin 哪位大侠解答一下，很是迷茫。。。。。谢谢啦！！！

【求助】一个mysql发生死锁的问题