-- -- -- -- -- -- -- -- -- --
LATEST DETECTED DEADLOCK
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The 2020-02-26 13:44:12 7 f89dbb7a700
* * * (1) the TRANSACTION:
TRANSACTION 20422629, ACTIVE 27.029 SEC starting index read
Mysql tables in the use of 4, 4
lockedThe LOCK WAIT 14 LOCK struct (s), heap size 2936, 13 row LOCK (s), the undo log entries 1
LOCK BLOCKING MySQL thread id: 1329375 block 1330124
MySQL thread id 1330124, OS thread handle 0 x7f89dfb77700, query id 1402142078 10.240.240.12 dmeb Sending data
UPDATE xx_order o LEFT the JOIN xx_order_item oi ON oi. The orders=o.i d LEFT JOIN xx_stock s ON supachai panitchpakdi roduct=oi. Product AND s. arehouse=13 LEFT JOIN xx_warehouse w ON w.i d=13 SET o.w arehouse_id=13, o.w arehouse_code=baron n, oi. Stock_id=s.i d, oi. Occupied_stock=oi. Quantity, o.d istribution_date=DATE_FORMAT (NOW (), '% % Y - m - H: % d % % m: % s') WHERE o.i d=572615
* * * (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the LOCKS space id 3931 page no 5 n bits 528 index ` FK2620291779F8D99A ` of table ` dmeb `. ` xx_stock ` TRX id 20422629 LOCKS lock mode S rec but not gap waiting
Record the lock, heap no 104 PHYSICAL Record: n_fields 2; Compact format. The info bits (0
Zero: len 8; Hex 8000000000004653; Asc FS;;
1: len 8; Hex 800000000000015 e; Asc ^;;
* * * (2) the TRANSACTION:
TRANSACTION 20422613, ACTIVE 26.913 SEC fetching rows
Mysql tables in the use of 3, 3 locked
9 the lock struct (s), heap size 1184, 7 row lock (s), the undo log entries 1
MySQL thread id 1329375, OS thread handle 0 x7f89dbb7a700, query id 1402141466 10.240.240.12 dmeb Searching rows for update
The update xx_stock set modify_date=now (), where actual_stock=actual_stock - 4 warehouse=13 and product=18003 and actual_stock & gt;=4
Doesn * * * (2) THE LOCK (S) :
RECORD the LOCKS space id 3931 page no 5 n bits 528 index ` FK2620291779F8D99A ` of table ` dmeb `. ` xx_stock ` TRX id 20422613 lock_mode X LOCKS rec but not gap
Record the lock, heap no 104 PHYSICAL Record: n_fields 2; Compact format. The info bits (0
Zero: len 8; Hex 8000000000004653; Asc FS;;
1: len 8; Hex 800000000000015 e; Asc ^;;
* * * (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD the LOCKS space id 3931 page no 7 n bits 240 index ` PRIMARY ` of table ` dmeb `. ` xx_stock ` TRX id 20422613 lock_mode X LOCKS rec but not gap waiting
Record the lock, heap no 96 PHYSICAL Record: n_fields 19; Compact format. The info bits (0
Zero: len 8; Hex 80000000000000 e4; Asc;;
1: len (6); Hex 000001379 d03; Asc 7;;
2: len 7; Hex 750000048 c14b3; Asc u;;
3: len 8; Hex 8000000000000001; Asc;;
4: len 5; Hex 99 a422b0c4; Asc ";
5: len 5; Hex 99 a5b4da67; Asc g;;
6: SQL NULL;
7: len 4; Hex 8002 e588; Asc;;
8: len 4; Hex 80000000; Asc;;
9: len 4; Hex 80000000; Asc;;
10: len 4; Hex 80000000; Asc;;
11: len 4; Hex 80031 e62; Asc b;;
12: len 8; Hex 80000000000045 Ed; Asc E;;
13: SQL NULL;
14: len 8; Hex 800000000000000 d; Asc;;
15: len 4; Hex 800052 b7; Asc R;;
16: len 4; Hex 8000 f86b; Asc k;;
17: len 4; Hex 8001 dbc7; Asc;;
18: len 4; Hex 8002 d98b; Asc;;
* * * WE ROLL BACK the TRANSACTION (2)
CodePudding user response:
A deadlock is generally business logic implementation problems, such as:1. Transaction 1 lock A table of data first, then lock B table data;
2. Transaction 2 lock B table data first, then lock A table data,
If you can, the business logic into a consistent order; Such as the unified first lock A, again lock B, this transaction will be waiting for the completed transaction 1, 2 will not deadlock,
Or will the data of each operation, points to be executed multiple times, reduce deadlocks may,
If business order can't change, then add exception handling in the code, a deadlock when retry,
CodePudding user response:
The update operation into a single table, do not form;First good data query, update the set where id=constant;
CodePudding user response:
This update to update more than one of the two transaction data, and the transaction between the update data have intersection, in this way, it is easy to deadlock,