稍改一下SQL语句: UPDATE `user_cart` SET `num` = 2 WHERE size = '3' AND user_id = '3' AND warehouse = 'VIP_CD' AND channel=1 AND flag=0 and id=4607667; 这个语句在执行的时候只会锁定1行 2 lock struct(s), heap size 376, 1 row lock(s),
1
事务1
执行UPDATE `user_cart` SET`num` = 2 WHERE size = '100064618' AND user_id = '32946212' AND warehouse ='VIP_SH' AND channel=1 AND flag=0 ORDER BY id DESC LIMIT 1
解决方案: 1. 避免index_merge,可以设置set global optimizer_switch='index_merge_intersection=off'; index_merge_intersection设置成off后,在and条件下不会走index merge,不影响or条件下选择index merge。 2. update 的sql改一下: 先使用select 得出具体的id,然后再使用id 为条件更新数据 SELECT id FROM user_cart WHERE size = '100064618' AND user_id = '32946212' AND warehouse = 'VIP_SH' AND channel=1 AND flag=0 ORDER BY id DESC LIMIT 1; UPDATE user_cart SET num=2 WHERE id=$id;