鎖日志如下
----------
LATEST DETECTED DEADLOCK
------------------------
2020-02-26 13:44:12 7f89dbb7a700
*** (1) TRANSACTION:
TRANSACTION 20422629, ACTIVE 27.029 sec starting index read
mysql tables in use 4, locked 4
LOCK WAIT 14 lock struct(s), heap size 2936, 13 row lock(s), undo log entries 1
LOCK BLOCKING MySQL thread id: 1329375 block 1330124
MySQL thread id 1330124, OS thread handle 0x7f89dfb77700, query id 1402142078 10.240.240.12 dmeb Sending data
UPDATE xx_order o LEFT JOIN xx_order_item oi ON oi.orders=o.id LEFT JOIN xx_stock s ON s.product=oi.product AND s.warehouse = 13 LEFT JOIN xx_warehouse w ON w.id = 13 SET o.warehouse_id = 13, o.warehouse_code=w.sn, oi.stock_id=s.id, oi.occupied_stock=oi.quantity, o.distribution_date=DATE_FORMAT(NOW(),'%Y-%m-%d %H:%m:%s') WHERE o.id = 572615
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3931 page no 5 n bits 528 index `FK2620291779F8D99A` of table `dmeb`.`xx_stock` trx id 20422629 lock mode S locks rec but not gap waiting
Record lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000004653; asc FS;;
1: len 8; hex 800000000000015e; asc ^;;
*** (2) TRANSACTION:
TRANSACTION 20422613, ACTIVE 26.913 sec fetching rows
mysql tables in use 3, locked 3
9 lock struct(s), heap size 1184, 7 row lock(s), undo log entries 1
MySQL thread id 1329375, OS thread handle 0x7f89dbb7a700, query id 1402141466 10.240.240.12 dmeb Searching rows for update
update xx_stock set modify_date=now(),actual_stock=actual_stock-4 where warehouse=13 and product=18003 and actual_stock>=4
*** (2) HOLDS THE LOCK(S):
RECORD 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 lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8000000000004653; asc FS;;
1: len 8; hex 800000000000015e; asc ^;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD 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 lock, heap no 96 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 8; hex 80000000000000e4; asc ;;
1: len 6; hex 000001379d03; asc 7 ;;
2: len 7; hex 750000048c14b3; asc u ;;
3: len 8; hex 8000000000000001; asc ;;
4: len 5; hex 99a422b0c4; asc " ;;
5: len 5; hex 99a5b4da67; asc g;;
6: SQL NULL;
7: len 4; hex 8002e588; asc ;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 80000000; asc ;;
10: len 4; hex 80000000; asc ;;
11: len 4; hex 80031e62; asc b;;
12: len 8; hex 80000000000045ed; asc E ;;
13: SQL NULL;
14: len 8; hex 800000000000000d; asc ;;
15: len 4; hex 800052b7; asc R ;;
16: len 4; hex 8000f86b; asc k;;
17: len 4; hex 8001dbc7; asc ;;
18: len 4; hex 8002d98b; asc ;;
*** WE ROLL BACK TRANSACTION (2)
uj5u.com熱心網友回復：
死鎖一般是業務邏輯實作的問題，比如：1. 事務 1 先鎖 A 表的資料，再鎖 B 表的資料；
2. 事務 2 先鎖 B 表的資料，再鎖 A 表的資料。
如果可以，業務邏輯改成一致的順序；例如統一先鎖 A，再鎖 B，這樣事務 2 就會等待事務 1 完成，不會死鎖。
或者將每次操作的資料范圍縮小，分多次執行，減少死鎖可能。
如果業務順序不能改，那就在代碼里加上例外處理，死鎖時重試。
uj5u.com熱心網友回復：
update改成單表操作，不要聯表；先查詢好資料，update set where id = 常量；
uj5u.com熱心網友回復：
這兩個事務中的update更新不止一條資料，而且，事務之間update的資料有交集，這樣，很容易發生死鎖。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/28406.html
標籤：MySQL
上一篇：求解：SQL server 的GUID匯入到mySQL 有大括號問題{}
下一篇：指紋資料集sd4 NIST 8-Bit Gray Scale Images of Fingerprint 指紋影像資料