死锁Deadlock

[任重道远莫忘初心]

updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction


sql语句：
update table set money= money+? where id = ?

就这样一个语句，为什么出现死锁呢？整个表没有任何索引，id是主键。

我用的是spring+mybatis+mysql（InnoDB）
这个方法上有事务注解，应该是并发导致的死锁，我现在没想明白为什么会死锁呢？
有什么解决办法呢？

[rucypli]

show engine innodb status\G 查看死锁情况

[ZXYperseverance]

show processlist 查看进程任务，看下到底哪个语句正在占用数据库资源，就能找到原因。 杀掉正在占用的进程。

[任重道远莫忘初心]

===================================== 2018-03-19 10:52:38 0x7fe27c464700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 3 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 86544 srv_active, 0 srv_shutdown, 723633 srv_idle srv_master_thread log flush and writes: 810153 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 558726 OS WAIT ARRAY INFO: signal count 1370168 RW-shared spins 0, rounds 2289012, OS waits 283022 RW-excl spins 0, rounds 13951203, OS waits 223251 RW-sx spins 2324, rounds 50834, OS waits 507 Spin rounds per wait: 2289012.00 RW-shared, 13951203.00 RW-excl, 21.87 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2018-03-19 08:21:40 0x7fe28d72d700 *** (1) TRANSACTION: TRANSACTION 1686333, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 584, OS thread handle 140610723251968, query id 2634291 10.254.100.74 sy_user updating update ota_company set unused_bal = unused_bal+90.0000 where ota_company_id = '6' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 515 page no 3 n bits 80 index PRIMARY of table `trade-ota`.`ota_company` trx id 1686333 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 20; compact format; info bits 0 0: len 1; hex 36; asc 6;; 1: len 6; hex 00000019badd; asc ;; 2: len 7; hex 5d000006d92a74; asc ] *t;; 3: len 30; hex 363036453442304230463144334137434530353330313030303037464442; asc 606E4B0B0F1D3A7CE0530100007FDB; (total 32 bytes); 4: len 6; hex e690bae7a88b; asc ;; 5: len 30; hex 74726164652d6f74612d7368656e6779612d63747269702d736572766963; asc trade-ota-shengya-ctrip-servic; (total 31 bytes); 6: len 2; hex 3030; asc 00;; 7: len 9; hex 8000000000987a0000; asc z ;; 8: len 9; hex 8000000003af63251c; asc c% ;; 9: len 1; hex 31; asc 1;; 10: len 0; hex ; asc ;; 11: len 2; hex 3130; asc 10;; 12: len 5; hex 999ddca358; asc X;; 13: len 18; hex 323130323133313939303038303930353334; asc 210213199008090534;; 14: len 5; hex 999ddca35b; asc [;; 15: len 18; hex 323130323133313939303038303930353334; asc 210213199008090534;; 16: len 5; hex 999ddca372; asc r;; 17: len 18; hex 323130323033313938323034323234373135; asc 210203198204224715;; 18: len 0; hex ; asc ;; 19: SQL NULL; *** (2) TRANSACTION: TRANSACTION 1686334, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 7 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 560, OS thread handle 140611012450048, query id 2634293 10.254.100.75 sy_user updating update ota_company set unused_bal = unused_bal+180.0000 where ota_company_id = '6' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 515 page no 3 n bits 80 index PRIMARY of table `trade-ota`.`ota_company` trx id 1686334 lock mode S locks rec but not gap Record lock, heap no 7 PHYSICAL RECORD: n_fields 20; compact format; info bits 0 0: len 1; hex 36; asc 6;; 1: len 6; hex 00000019badd; asc ;; 2: len 7; hex 5d000006d92a74; asc ] *t;; 3: len 30; hex 363036453442304230463144334137434530353330313030303037464442; asc 606E4B0B0F1D3A7CE0530100007FDB; (total 32 bytes); 4: len 6; hex e690bae7a88b; asc ;; 5: len 30; hex 74726164652d6f74612d7368656e6779612d63747269702d736572766963; asc trade-ota-shengya-ctrip-servic; (total 31 bytes); 6: len 2; hex 3030; asc 00;; 7: len 9; hex 8000000000987a0000; asc z ;; 8: len 9; hex 8000000003af63251c; asc c% ;; 9: len 1; hex 31; asc 1;; 10: len 0; hex ; asc ;; 11: len 2; hex 3130; asc 10;; 12: len 5; hex 999ddca358; asc X;; 13: len 18; hex 323130323133313939303038303930353334; asc 210213199008090534;; 14: len 5; hex 999ddca35b; asc [;; 15: len 18; hex 323130323133313939303038303930353334; asc 210213199008090534;; 16: len 5; hex 999ddca372; asc r;; 17: len 18; hex 323130323033313938323034323234373135; asc 210203198204224715;; 18: len 0; hex ; asc ;; 19: SQL NULL; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 515 page no 3 n bits 80 index PRIMARY of table `trade-ota`.`ota_company` trx id 1686334 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 20; compact format; info bits 0 0: len 1; hex 36; asc 6;; 1: len 6; hex 00000019badd; asc ;; 2: len 7; hex 5d000006d92a74; asc ] *t;; 3: len 30; hex 363036453442304230463144334137434530353330313030303037464442; asc 606E4B0B0F1D3A7CE0530100007FDB; (total 32 bytes); 4: len 6; hex e690bae7a88b; asc ;; 5: len 30; hex 74726164652d6f74612d7368656e6779612d63747269702d736572766963; asc trade-ota-shengya-ctrip-servic; (total 31 bytes); 6: len 2; hex 3030; asc 00;; 7: len 9; hex 8000000000987a0000; asc z ;; 8: len 9; hex 8000000003af63251c; asc c% ;; 9: len 1; hex 31; asc 1;; 10: len 0; hex ; asc ;; 11: len 2; hex 3130; asc 10;; 12: len 5; hex 999ddca358; asc X;; 13: len 18; hex 323130323133313939303038303930353334; asc 210213199008090534;; 14: len 5; hex 999ddca35b; asc [;; 15: len 18; hex 323130323133313939303038303930353334; asc 210213199008090534;; 16: len 5; hex 999ddca372; asc r;; 17: len 18; hex 323130323033313938323034323234373135; asc 210203198204224715;; 18: len 0; hex ; asc ;; 19: SQL NULL; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 1690299 Purge done for trx's n:o < 1690297 undo n:o < 0 state: running but idle

[zhongyangjian]

根据主键来更新的语句也是会死锁的，我也遇到过，间隙锁了解一下https://blog.csdn.net/zhongyangjian/article/details/51968675

[生命沉思者]

你检查一下，ota_company表的ota_company_id列应该不是主键。