背景
让DBA查日志
show engine innodb status
可以看到两条数据
UPDATE nr_activity_dish SET day_stock='9999', dstock_update_time='20191024' WHERE (activity_id = '5000000003940695') AND (dish_id = '1547797854069605')
UPDATE nr_activity_dish SET day_stock='9999', dstock_update_time='20191024' WHERE (activity_id = '5000000003940695') AND (dish_id = '15477812970614023')
虽然acitivity_id和dish_id都是独立索引，但是update加锁时候只会使用一个，导致多行数据被锁
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-10-24 01:08:15 7f7f58063700
*** (1) TRANSACTION:
TRANSACTION 2559276227, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 396 lock struct(s), heap size 46632, 4 row lock(s)
MySQL thread id 22749534, OS thread handle 0x7f7f4ea61700, query id 5012948981 10.252.16.9 mai_elemarketing Searching rows for update
/*E:shardkey=shardid&appid=bdwaimai_marketing.elemarketing&shardvalue=11&rid=bdwaimai_wmq.pusher^^6004B4B47E7B4332B85DDBE44BD6E15C|1571850495596&rpcid=1.1.5:E*//*E:group=place_waimai_elemarketing-bdwaimai_marketing_elemarketing_group&sqlId=9000591fb4342a9196717dfad9f4d6c4:E*/UPDATE nr_activity_dish SET day_stock='9999', dstock_update_time='20191024' WHERE (activity_id = '5000000003940695') AND (dish_id = '1547797854069605')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 33335403 n bits 80 index `PRIMARY` of table `place_waimai_elemarketing`.`nr_activity_dish` trx id 2559276227 lock_mode X locks rec but not gap waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
0: len 8; hex 80000000a8f7234b; asc #K;;
1: len 6; hex 00009775741a; asc ut ;;
2: len 7; hex 38000078352df6; asc 8 x5- ;;
3: len 8; hex 0011c379381ca157; asc y8 W;;
4: len 8; hex 00000000850540fd; asc @ ;;
5: len 8; hex 0036fcfd28036d07; asc 6 ( m ;;
6: len 19; hex e781abe783a7203730672a34e4b8aa2fe7bb84; asc 70g*4 / ;;
7: len 4; hex 800001e0; asc ;;
8: len 4; hex 800001e0; asc ;;
9: len 30; hex 7b2261637469766974795f6964223a223530303030303030303339343036; asc {"activity_id":"50000000039406; (total 908 bytes);
10: len 4; hex 8000000a; asc ;;
11: len 4; hex 0000270f; asc ' ;;
12: len 4; hex 0000270f; asc ' ;;
13: len 4; hex 8000270f; asc ' ;;
14: len 4; hex 8000270f; asc ' ;;
15: len 4; hex 8134172f; asc 4 /;;
16: len 4; hex 80000000; asc ;;
17: len 4; hex 80000000; asc ;;
18: len 1; hex 01; asc ;;
19: len 4; hex 5da5e015; asc ] ;;
20: len 4; hex 5daf3711; asc ] 7 ;;
21: len 8; hex 8000000000000012; asc ;;
22: len 6; hex 5daf37110bae; asc ] 7 ;;
*** (2) TRANSACTION:
TRANSACTION 2559276230, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
396 lock struct(s), heap size 46632, 3 row lock(s)
MySQL thread id 22749588, OS thread handle 0x7f7f58063700, query id 5012948983 10.252.64.4 mai_elemarketing Searching rows for update
/*E:shardkey=shardid&appid=bdwaimai_marketing.elemarketing&shardvalue=11&rid=bdwaimai_wmq.pusher^^01FC6E0EFC4C4168BCEAD2F136D68D15|1571850495598&rpcid=1.1.5:E*//*E:group=place_waimai_elemarketing-bdwaimai_marketing_elemarketing_group&sqlId=9000591fb4342a9196717dfad9f4d6c4:E*/UPDATE nr_activity_dish SET day_stock='9999', dstock_update_time='20191024' WHERE (activity_id = '5000000003940695') AND (dish_id = '15477812970614023')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 31 page no 33335403 n bits 80 index `PRIMARY` of table `place_waimai_elemarketing`.`nr_activity_dish` trx id 2559276230 lock_mode X locks rec but not gap
Record lock, heap no 11 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
0: len 8; hex 80000000a8f7234b; asc #K;;
1: len 6; hex 00009775741a; asc ut ;;
2: len 7; hex 38000078352df6; asc 8 x5- ;;
3: len 8; hex 0011c379381ca157; asc y8 W;;
4: len 8; hex 00000000850540fd; asc @ ;;
5: len 8; hex 0036fcfd28036d07; asc 6 ( m ;;
6: len 19; hex e781abe783a7203730672a34e4b8aa2fe7bb84; asc 70g*4 / ;;
7: len 4; hex 800001e0; asc ;;
8: len 4; hex 800001e0; asc ;;
9: len 30; hex 7b2261637469766974795f6964223a223530303030303030303339343036; asc {"activity_id":"50000000039406; (total 908 bytes);
10: len 4; hex 8000000a; asc ;;
11: len 4; hex 0000270f; asc ' ;;
12: len 4; hex 0000270f; asc ' ;;
13: len 4; hex 8000270f; asc ' ;;
14: len 4; hex 8000270f; asc ' ;;
15: len 4; hex 8134172f; asc 4 /;;
16: len 4; hex 80000000; asc ;;
17: len 4; hex 80000000; asc ;;
18: len 1; hex 01; asc ;;
19: len 4; hex 5da5e015; asc ] ;;
20: len 4; hex 5daf3711; asc ] 7 ;;
21: len 8; hex 8000000000000012; asc ;;
22: len 6; hex 5daf37110bae; asc ] 7 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 35689285 n bits 824 index `idx_aid` of table `place_waimai_elemarketing`.`nr_activity_dish` trx id 2559276230 lock_mode X locks rec but not gap waiting
Record lock, heap no 63 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 0011c379381ca157; asc y8 W;;
1: len 8; hex 80000000a8f7234b; asc #K;;
*** WE ROLL BACK TRANSACTION (2)
