上周购物车出现了2次数据库死锁的情况,主要导致问题的是所使用的SQL语句和数据库index_merge这个开关
首先,出现这种情况的机率比较小,一般发生在用户快速将同一商品多次加入购物车这种情况。
我们建立一个测试表来模拟这中情况: selectid,size,user_id,num,warehouse,flag,channel from user_cart; <ignore_js_op> #一共有7条记录,模拟在大并发情景,用户购物车有多条相同商品的记录
出问题的SQL语句是这个:
UPDATE `user_cart` SET `num` = 2 WHERE size ='3' AND user_id = '3' AND warehouse = 'VIP_CD' AND channel=1 AND flag=0 ORDERBY id DESC LIMIT 1;
我们执行一下,可以发现这个语句执行的时候锁住了9行:(show engine innodb status;) 4 lock struct(s), heap size 1248, 9 row lock(s) 但实际上只会更新一条记录
稍改一下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),
表index的情况: PRIMARY KEY (`id`), KEY `size` (`size`), KEY `flag` (`flag`,`add_time`), KEY `idx_cart_id` (`cart_id`), KEY `userid_size` (`user_id`,`size`)
出现这种区别的原因: 使用了ORDER BY id DESC LIMIT 1, mysql执行时无法判断所要更新的具体记录,所以所有命中的记录都会被锁定。 index_merge打开,mysql会自动使用多个索引来做合并查询 这种情况下: index: size 命中锁定3行+index:userid_size命中锁定3行+primary key3行=>一共9行。
改动了sql语句之后,因为有:and id=4607667,mysql自动使用primary key,只锁定一行
再来看下线上死锁的具体情况:
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
通过(user_id,size)复合索引查找数据,锁定2行(满足这个索引查询条件有2个主键值),结果有6个rowlock(主键上2个,复合索引锁住4个)
2 事务2 执行UPDATE `user_cart` SET`add_time` = 1416968173 WHERE id in ('4179466', '4179478', '4179479') AND flag= '0' 通过主键ID来锁定记录,发现某个ID已被事务1锁定(2行之一),被堵塞
3 事务1继续执行sql,因为走了index_merge,走size上索引,发现这个索引值被事务2已锁住(此sql在死锁日志中未显示,已经获取size上的锁,说明有DML通过size索引获取数据),这时,事务1被事务 2堵塞。
这样,事务1和事务2互相堵塞,死锁出现。
解决方案: 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;
总结: 这种问题发生的机率较小,但大家在使用update语句时还是要尽量使用primary key来update, 避免使用上面这种不确定的更新方式。 特别是在索引及重复记录比较多的情况。
|
12
|
|
|