mysql中的锁 常见问题和总结(innodb)

死锁及其案例

我们知道，操作系统中的死锁是“占有并等待”，也就是一个线程占有资源A而等待资源B，另一个线程则占有资源B并等待资源A。mysql中的死锁也是同理。假设一个事务锁定了行A同时需要对行B进行操作，而另一个事务锁定了行B同时需要对行A进行操作，那么就会发生死锁。

死锁案例： 将投资的钱拆成几份随机分配给借款人。

投资人投资后，将金额随机分为几份，然后随机从借款人表里面选几个，然后通过一条条select for update 去更新借款人表里面的余额等。

抽象出来就是一个session通过for循环会有几条如下的语句：

Select * from xxx where id=‘随机id’ for update

基本来说，程序开启后不一会就死锁。

这可以是说最经典的死锁情形了。

例如两个用户同时投资，A用户金额随机分为2份，分给借款人1，2

B用户金额随机分为2份，分给借款人2，1

由于加锁的顺序不一样，死锁当然很快就出现了。

对于这个问题的改进很简单，直接把所有分配到的借款人直接一次锁住就行了。

Select * from xxx where id in (xx,xx,xx) for update即可。
关于死锁更详细的可以参考这篇文章。

InnoDB存储引擎的锁算法

我们有时候会听到这样的问题：“请描述一下InnoDB的三种锁算法？”有的人会觉得很懵，不知道这个问题是要问什么。
事实上，它们是：
Record lock：单个行记录上的锁；
Gap lock：间隙锁，锁定一个范围，不包括记录本身；
Next-key lock：record+gap 锁定一个范围，包含记录本身。

另外，InnoDB加锁的一些算法规则大致有六条：
1、在不通过索引条件查询时，InnoDB 会锁定表中的所有记录。 所以，如果考虑性能，WHERE语句中的条件查询的字段都应该加上索引。
2、InnoDB通过索引来实现行锁，而不是通过锁住记录。因此，当操作的两条不同记录拥有相同的索引时，也会因为行锁被锁而发生等待。
3、由于InnoDB的索引机制，数据库操作使用了主键索引，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。
4、锁降级：当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。
5、InnoDB对于辅助索引有特殊的处理，不仅会锁住辅助索引值所在的范围，还会将其下一键值加上Gap LOCK。
6、InnoDB使用Next-Key Lock机制来避免Phantom Problem（幻读问题）。（这里需要注意，只有当加上了for update关键字时，才会采用next-key lock，如果没有，则使用InnoDB自带的mvcc规则解决幻读问题）

关于六条算法规则更加详细的例子介绍和说明，可以参考这一篇文章。

表锁、行锁、页锁、间隙锁

顾名思义，表锁就是锁定整个表，行锁就是锁定一行数据，页锁就是锁定一个页，页是什么呢？就是我们上次在索引那篇文章中提到的，InnoDB的B+树结构中的一个页结构。所有的锁事实上都是对着索引添加的，索引的结构就是mysql的底层数据结构。

间隙锁是什么呢？定义是这样的：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁（注意是索引项，也就是说，如果没得索引，还是要直接简单粗暴地锁表）；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP LOCK），间隙锁和行锁合称Next-Key Lock。

为什么是和行锁合称next-key lock？因为间隙锁它其实是一个开区间，也就是说，它锁住的可能是范围（101，正无穷），但不会包括101。为了包括101还必须要把行锁加进去，锁住101这个行，因此合称为间隙锁。

什么时候用行锁，什么时候用间隙锁，什么时候用表锁？
首先对于行锁，它是自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁。也就是说，除了SELECT操作以外的增删改操作都会自动地被加上行锁。
然后如果不走索引查数据库，则不会加上行锁，会直接把表锁起来。这样首先可以避免脏读，其次也可以增加并发性，只锁定要操作的行，其他的事务可以操作其他的行。
再然后如果是范围查询，并且我们的查询语句末尾加上了for update，那么就会使用next-key lock。next-key lock锁住了一个范围内的数据，因此解决了幻读问题。（在InnoDB中，如果不加for update，则不会采用next-key lock，而是采用mvcc机制解决幻读问题，具体的会在讲事务的文章中提到）

锁升级：如果一个表内的大部分数据都要进行操作，此时一行一行地加锁实在是太浪费了，所以mysql会索性给整个表加上锁，即实际上并没有使用索引。

行锁和间隙锁理论上都会造成死锁。更多关于行锁、表锁和间隙锁的信息可以参考这一篇博客，讲得比较全面。

共享锁和排他锁

又称S锁和X锁。S锁即共享锁，X锁即排他锁。
共享锁和排他锁事实上可以理解为对上面提到的具体的锁的类型的一种抽象归类。上面提到的行锁、表锁、next-key lock，统统都是排他锁。我们也提到过，增删改操作全部会被自动地加上行锁（也就是排他锁），而对于SELECT操作，如果

select * from table1 for update

这就是加了排他锁，也就是我们上面说过的next-key lock。
如果

select * from table2 in share mode

这就是加了共享锁。共享锁的作用是什么呢？简单来说，大家读可以一起读，但一旦你想写，就必须要等到所有的共享锁释放才可以。这样就显式地避免了幻读。（如果读的事务提前把要读的所有数据加上了共享锁的话）

共享锁这里有一个锁升级机制可能会产生死锁。如果一个事务首先在select里加了共享锁，然后又来了一个update，那么这个update就必须要等待共享锁升级到排他锁。而共享锁升级到排他锁又必须等待所有其他共享锁的释放，于是问题来了，如果事务A和B俩人都这么干，A在等B释放共享锁，B在等A释放共享锁，那么就产生了死锁。

具体代码如下：

T1:begin tran

     select * from table lock in share mode

     update table set column1='hello'

T2:begin tran

     select * from table lock in share mode

     update table set column1='world'

所以说这样的情况怎么避免呢？当然是……其实看看这个事务的代码就是有问题的，你既然后面要update，前面in share mode干嘛，干脆一起for update啊。

也就是写成这个样子：

T1:begin tran

     select * from table lock for update

     update table set column1='hello'

T2:begin tran

     select * from table lock in for update

     update table set column1='world'

好了，世界清净了。

意向共享锁和意向排他锁

这两个锁其实主要是针对大范围的锁（如表锁，可能还有页锁）而言的。一般来说，我们要为范围更大的数据进行加锁，需要保证这部分数据内部不存在与之冲突的锁。那么如果我们要给10000000条数据加锁，就得一条一条地去检查它们上面存不存在锁吗？这显然有点麻烦，于是有了意向锁。

意向锁（innodb特有）分意向共享锁和意向排他锁。
意向共享锁：表示事务获取行共享锁时，必须先得获取该表的意向共享锁；
意向排他锁：表示事务获取行排他锁时，必须先得获取该表的意向排他锁；

我们知道，如果要对整个表加锁，需保证该表内目前不存在任何锁。

因此，如果需要对整个表加锁，那么就可以根据：检查意向锁是否被占用，来知道表内目前是否存在共享锁或排他锁了。而不需要再一行行地去检查每一行是否被加锁。

乐观锁和悲观锁

乐观锁和悲观锁都是针对SELECT操作而言的。 这一点请务必注意。

悲观锁事实上也就是排他锁，而乐观锁一般通过加时间戳的方法来实现。有点类似于mvcc机制，这样保证了更新到的数据一定不会被其他事务所修改。然而，如果更新失败，意味着数据已经被修改了，这时候就需要重试。重试的次数可以自己规定，一般不得低于三次为好。

关于共享锁和排他锁、乐观锁和悲观锁更详细的解说，可以参考这一篇博客。

锁优化

这里只谈InnoDB的锁优化。
对于行锁而言，我们可以：
1、尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；
2、合理设计索引，让Innodb在索引键上面加锁尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
3、尽可能减少基于范围的数据检索过滤条件，避免间隙锁带来的负面影响而锁定了不该锁定的记录；
4、尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
5、在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成。

对于如何避免死锁，有几个常用的操作：
1、业务模块中，尽可能按照相同的访问顺序来访问，防止产生死锁；
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概。