SQL statements:
The update table set money=money +? Where id=?
Such a statement, why a deadlock? The whole table without any index, id is the primary key,
I am using spring + mybatis + mysql (InnoDB)
There is transaction annotations on this method, should be the result of concurrent deadlock, I didn't want to understand why a deadlock now?
What's the solution?
CodePudding user response:
Show engine innodb status \ GView the deadlock situation
CodePudding user response:
Show the processlistCheck process tasks, see which statements are take up database resources, to find the reason, to kill the process is to take up,
CodePudding user response:
=====================================The 2018-03-19 10:52:38 0 x7fe27c464700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
BACKGROUND THREAD
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Srv_shutdown srv_master_thread loops: 86544 srv_active, 0, 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, 2289012 rounds, OS waits 283022
RW - excl spins 0, 13951203 rounds, OS waits 223251
RW - sx spins, 2324, 50834 rounds, OS waits 507
Spin rounds per wait: 2289012.00 RW -shared, RW - excl, 13951203.00 21.87 RW - sx
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
LATEST DETECTED DEADLOCK
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The 2018-03-19 08:21:40 0 x7fe28d72d700
* * * (1) the TRANSACTION:
TRANSACTION 1686333, ACTIVE 0 SEC starting index read
Mysql tables in use 1, locked 1
The LOCK WAIT 7 LOCK struct (s), the heap size of 1136, 3 row LOCK (s), the undo log entries 1
MySQL thread id 584, OS thread handle 140610723251968, query id 2634291 10.254.100.74 sy_user updating
The update ota_company
Set
Unused_bal=unused_bal + 90.0000
Where ota_company_id='6'
* * * (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the 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 the lock, heap no 7 PHYSICAL Record: n_fields 20; Compact format. The info bits (0
0: len 1; Hex 36; Asc 6;;
1: len (6); Hex 00000019 badd; Asc;;
2: len 7; Hex 5 d000006d92a74; Asc] * t;;
3: len 30; Hex 363036453442304230463144334137434530353330313030303037464442; Asc 606 e4b0b0f1d3a7ce0530100007fdb; (total 32 bytes);
4: len (6); Hex e690bae7a88b; Asc;;
5: len 30; Hex 74726164652 d6f74612d7368656e6779612d63747269702d736572766963; Asc trade - ota - shengya - ctrip - servic. (total 31 bytes);
6: len 2; Hex 3030; Asc 00;;
7: len 9; Hex 8000000000987 a0000; Asc z;;
8: len 9; Hex 8000000003 af63251c; 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 999 ddca358; Asc X;;
13: len 18; Hex 323130323133313939303038303930353334; Asc 210213199008090534;;
14: len 5; Hex 999 ddca35b; Asc [;
15: len 18; Hex 323130323133313939303038303930353334; Asc 210213199008090534;;
16: len 5; Hex 999 ddca372; Asc r;;
17: len 18; Hex 323130323033313938323034323234373135; Asc 210203198204224715;;
18: len 0; Hex. Asc;;
19: SQL NULL;
* * * (2) the TRANSACTION:
TRANSACTION 1686334, ACTIVE 0 SEC starting index read
Mysql tables in use 1, locked 1
7 the lock struct (s), the heap size of 1136, 3 row lock (s), the undo log entries 1
MySQL thread id 560, OS thread handle 140611012450048, query id 2634293 10.254.100.75 sy_user updating
The update ota_company
Set
Unused_bal=unused_bal + 180.0000
Where ota_company_id='6'
Doesn * * * (2) THE LOCK (S) :
RECORD the LOCKS space id 515 page no 3 n bits 80 index PRIMARY of table ` trade - ota `. ` ota_company ` TRX id 1686334 LOCKS lock mode S rec but not gap
Record the lock, heap no 7 PHYSICAL Record: n_fields 20; Compact format. The info bits (0
0: len 1; Hex 36; Asc 6;;
1: len (6); Hex 00000019 badd; Asc;;
2: len 7; Hex 5 d000006d92a74; Asc] * t;;
3: len 30; Hex 363036453442304230463144334137434530353330313030303037464442; Asc 606 e4b0b0f1d3a7ce0530100007fdb; (total 32 bytes);
4: len (6); Hex e690bae7a88b; Asc;;
5: len 30; Hex 74726164652 d6f74612d7368656e6779612d63747269702d736572766963; Asc trade - ota - shengya - ctrip - servic. (total 31 bytes);
6: len 2; Hex 3030; Asc 00;;
7: len 9; Hex 8000000000987 a0000; Asc z;;
8: len 9; Hex 8000000003 af63251c; 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 999 ddca358; Asc X;;
13: len 18; Hex 323130323133313939303038303930353334; Asc 210213199008090534;;
14: len 5; Hex 999 ddca35b; Asc [;
15: len 18; Hex 323130323133313939303038303930353334; Asc 210213199008090534;;
16: len 5; Hex 999 ddca372; nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull