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 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。