InnoDB为什么update要尽量使用索引?

转载是一种动力 分享是一种美德

从一个问题展开讨论

问题:innodb行级锁是通过锁索引记录实现的,如果update语句的where列没建索引,即使只update一条记录也会锁住整张表吗?

答案是肯定的。 我从当前本地数据库中找出一张建立自增主键的表来演示。打开两个终端窗口,用于验证当a端执行update语句时b端是否会被锁住。首先表的结构如下图所示。

测试表stu_score中,id是主键索引,其它的列都是没有建立索引的。现在要测试一下当执行update语句时,如果where没有使用索引,那么是整张表都被锁住呢,还是只会锁住满足条件的列呢?注意记得不要忘记开启一个事务。

从上面的截图中可以看出,当在A端执行where name=“wujiuye”的update语句时,虽然满足name=“wujiuye”的记录只有一条,但是innodb却将整张表都锁住了,在A端未将事务提交之前B端执行更新其它列的update语句被阻塞了。

接着来看一个使用索引的update示例

如上面截图所示,在a端由于update语句只对id=1008的这条记录做修改,id是主键,所以innodb只将id=1008的这行记录加锁了,所以在b端同样只对id=1007的行做修改,所以也只会锁住id=1007这行记录。

你也可以验证一下是否加了行锁,在a、b端不同事务间对同一行记录执行 update语句。

很明显,a端先执行了update where id=1008,所以先获取了id=1008这行记录的锁,然后再到b端执行update where id=1008的时候由于锁已经被其它事务占用了,只能进入等待状态。当a端提交事务时b端才会获得锁继续执行,或者b端等待超时时(事务默认锁超是50s)直接执行失败,事务进行回滚。

如果你在b端执行where name=“wujiuye”的update语句就会进入阻塞了,因为它需要获得表锁,会将整个表锁住,不信可以看下面的实验结果。

使用行锁会出现死锁情况,来看个例子。

在a端先对id=1008这行记录做update操作,获得了id=1008的行锁,(在需要的时候才会加锁,但是只有事务提交的时候才会释放锁,这是二阶段锁的概念。)然后到b端对id=1007这行记录做update操作,获得了id=1007这行记录的行锁,这时候a事务持有id=1008的行锁,b事务持有id=1007的行锁,也都没有什么问题,但是当a端继续执行对b端已经加锁的记录执行写操作时,就会进行阻塞状态,而如果此时b端也想对a端加锁的记录执行写操作,那么就会出现死锁状态了。

不过经过上面的实验验证,innodb已经默认帮我们排除死锁情况了,所以出现当我在b端执行最后一句update的时候就立马报错了,提示:deadlock found when trying to get lock;尝试获取锁时发现死锁。

总结

两阶段锁协议:在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。

了解这个对我们有什么帮助?在并发情况下,对于那种多用户共享操作的记录是不是加锁的时间越少越好?比如,购买一件商品,同一件商品是不是可以有多个用户同时购买,这样就是多个事务中访问了同一商品表的同一行记录,而购买付款是不是每个事务中只对当前用户对应的记录做修改,所以怎么优化呢?记住,只有在需要的时候才会加锁,所以对商品记录加行锁是在减库存的时候进行加锁的,为了加锁时间少,是不是可以先执行用户的扣款操作,添加订单记录操作,最后再执行减库存操作,然后提交事务,在准备提交事务时再执行减库存操作,这样就是减少加锁时间,也能提交并发效率。

如果上面的例子看不懂没关系,我在网上找了一个例子。 假设你负责实现一个电影票在线交易业务,顾客 A 要在影院 B 购买电影票。我们简化一点,这个业务需要涉及到以下操作:

(1)从顾客 A 账户余额中扣除电影票价;

(2)给影院 B 的账户余额增加这张电影票价;

(3)记录一条交易日志。

也就是说,要完成这个交易,我们需要 update 两条记录,并 insert 一条记录。当然,为了保证交易的原子性,我们要把这三个操作放在一个事务中。

那么,你会怎样安排这三个语句在事务中的顺序呢?试想如果同时有另外一个顾客 C 要在影院 B 买票,那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额,需要修改同一行数据。

根据两阶段锁协议,不论你怎样安排语句顺序,所有的操作需要的行锁都是在事务提交的时候才释放的。所以,如果你把语句 2 安排在最后,比如按照 3、1、2 这样的顺序,那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待,提升了并发度。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181220G1JEQQ00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

扫码关注云+社区

领取腾讯云代金券