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)
这个方法上有事务注解,应该是并发导致的死锁,我现在没想明白为什么会死锁呢?
有什么解决办法呢?
sql语句:
update table set money= money+? where id = ?就这样一个语句,为什么出现死锁呢?整个表没有任何索引,id是主键。我用的是spring+mybatis+mysql(InnoDB)
这个方法上有事务注解,应该是并发导致的死锁,我现在没想明白为什么会死锁呢?
有什么解决办法呢?
查看死锁情况
查看进程任务,看下到底哪个语句正在占用数据库资源,就能找到原因。 杀掉正在占用的进程。
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