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）
這個方法上有事務注解，應該是并發導致的死鎖，我現在沒想明白為什么會死鎖呢？
有什么解決辦法呢？
uj5u.com熱心網友回復：
show engine innodb status\G查看死鎖情況
uj5u.com熱心網友回復：
show processlist查看行程任務，看下到底哪個陳述句正在占用資料庫資源，就能找到原因。 殺掉正在占用的行程。
uj5u.com熱心網友回復：
=====================================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
uj5u.com熱心網友回復：
根據主鍵來更新的陳述句也是會死鎖的，我也遇到過，間隙鎖了解一下https://blog.csdn.net/zhongyangjian/article/details/51968675uj5u.com熱心網友回復：
你檢查一下，ota_company表的ota_company_id列應該不是主鍵。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/90965.html
標籤：MySQL