MySQL锁详解

1、锁是什么？

    锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制(以上内容来自必应)。通俗点来说锁机制就是用来管理对共享资源的并发操作，从而保证数据的正确性。

2、MySQL InnoDB中锁的分类

    为了准确我们直接去MySQL官网上来查找锁相关内容，通过下图我们可以看到InnoDB存储引擎中将锁分为8类，从上至下先来过一遍，第一个是行级别的锁即S锁和X锁，第二个为意向锁它们是表级别的锁，这四个可以归为是MySQL中锁的基本类型，接下来的三个锁记录锁、间隙锁、临界锁都可以归为MySQL中锁的算法，接下来是插入意向锁以及自增锁，最后一个是MySQL5.7新增的锁即空间索引谓词锁。本文主要介绍第一类和第二类即前五个。

3、表锁 VS 行锁

    表锁和行锁的概念很容易理解一个是锁定整张表一个是锁定一行记录，那么两者有什么区别呢。

锁的粒度：表锁 > 行锁 -- 这是因为表锁会锁定更多的记录以及资源因此粒度比较大

加锁效率：表锁 > 行锁 -- 这是因为表锁直接锁定了整个表资源而不需要向行锁一样一行行锁

冲突概论：表锁 > 行锁 -- 锁整张表数据所有写的操作都需要阻塞因此冲突更多

并发性能：表锁 < 行锁 -- 行锁的冲突概率小自然并发高

4、MyISAM、InnoDB支持的锁

   上面已经介绍到MySQL既支持表锁也支持行锁，而MyISAM只支持表锁，通过lock tables '表名' read(或write)来加表锁。  

5、共享锁

    先来介绍第一个分类里面的第一种锁即共享锁也叫"S"锁，当多个事物操作同一数据时可以共享一把锁，可以同时访问数据但是不可修改数据。加锁的方式为在查询语句后加Lock in share mode; 还是以昨天的cust表来示范下，先对cust表加一个S锁然后进行查询以及修改看效果。首先将MySQL自动提交事物关掉否则select后就直接提交事物无法看出效果，为了模拟2个事物操作这条数据我会开2个查询窗口，如下图所示首先手动开启事物，然后查询时加一把S锁但是此时先不提交事物。

接着打开另外一个查询窗口(代表另外起一个事物)同样先关闭事物，然后先进行查询操作，可以看到依然会查询出来数据不会阻塞。

最后我们执行update修改刘明手机号，如果阻塞的话会一直运行也就是停止按钮会一直红着，表示阻塞。如下图所示可以看到事物并没有结束而是一直阻塞等待到上个事物提交。

6、排他锁

    介绍下第一类的第二个锁即排他锁也叫X锁，排他锁与其他锁不可并存，如果一个事物获取了一行数据的排他锁，其他事物不可以读或者写。排他锁加锁的方式分为手动加锁和自动加锁，手动加锁的方式是在查询语句后加for update；自动加锁表示delete/update/insert语句会自动加X锁。下面演示一下，还是对刘明这条数据先加上排他锁，然后依次加X锁和S锁看是否都会阻塞。可以看到只要一个事物给数据加了X锁，其他事物加S锁、X锁或者自动加锁的update操作都会阻塞。

7、意向共享锁(IS) / 意向排他锁(IX)

    介绍第二类中的IS、IX锁，意向锁都是InnoDB存储引擎自己维护的用户是无法操作意向锁的。

    意向共享锁IS：意向2个字可以看出是在事物准备给给数据加共享锁之前先要获取这个意向，就像是你要去别人家做客需要告诉人家你有这个意向好让人家准备，只有人家有这个意向让你去做客你才能去，而对于意向锁来说在加S锁的时候需要看下表上是否有意向锁，有的话就表示已经被锁定而不需要扫描表上每行数据看是否有S锁。

    意向排他锁IX：同IS加意向锁的理由，在事物准备给数据加X锁的时候必须先获取该表的IX锁。下图为S锁、X锁、IS锁、IX锁锁定关系。

8、锁到底锁定了什么？

    本着大胆猜想，小心实践的原理先来猜想下，第一个猜想肯定是锁住了一行记录呗，这不是很正常嘛? 为此我们来实践下，我创建了一张m1表DDL如下：

 

为了验证我的猜想首先我给id=1的数据加一个X锁，然后另外一个事物操作id=2的数据，如果只是锁住一行数据的话那么id=2的数据很明显是可以操作成功的。

尴尬的一幕出现了如果锁在是锁住一行记录的话那么为什么id=2也会受到影响呢？而且我对id=3的数据操作以及修改id=4的数据都被锁住直接将整张表都锁了，因此猜想一肯定是不对的，而且还出现了锁表的问题。

    接着我有第二个猜想，会不会锁住的是列呢？因此创建了m3表，指定了id为主键同时指定了唯一键name。

为了验证猜想二我对name=1加X锁，那按照猜想我去操作id=2的数据应该是不会锁住的，结果看下图：

猜想二是也是错误的锁并不是锁住列，那就奇怪了锁到底是锁住什么呢？答案是锁定的是索引！！！这里先补充一个索引中漏掉的知识，每张表在创建后都会有一个索引即使你不指定索引，如果创建表的时候你指定了primary key则直接使用，如果没有指定primary key则InnoDB会使用unique key not null(不为空的唯一索引)，如果连唯一索引都没有则存储引擎使用默认的RowID (隐藏索引) 作为聚集索引。接着来解释猜想一为什么会锁表这是因为m1既没有指定primary key也没有指定unique key因此只有默认的RowID，因为没有索引因此在第一次加X锁检索的时候存储引擎会把每一个RowID都锁定所以会锁表。第二个猜想为什么我创建了主键索引也创建了唯一索引为何事物二还是会阻塞呢？想想上篇提到的辅助索引，m3表中name为辅助索引，辅助索引在检索数据的时候首先会从辅助索引中取到聚集索引也就是id，然后根据id再去聚集索引里面找完整的数据，因此会把聚集索引锁定所以猜想二也会阻塞。不信的话我们去看下INNODB_LOCKS表看下当前正在锁定的事物，lock_mode代表锁的类型为X锁，lock_type下面会说到，lock_index就表示锁的索引可以得到2个信息第一它真的是锁索引，第二本例子辅助索引会把聚集索引也加X锁。

9、Locks algorithm

    关于索引的算法第一部分提到过主要分为三种记录锁、间隙锁、临界锁为了介绍三者我创建了一张m2表如下所示：

同时为了介绍记录、间隙、临界的区别我还画了下面的草图：概念我已经在图下方标出来了，补充一下如果有N个Record会有N+1个Gap间隙，当然m2表中主键是int类型的如果你的主键是varchar类型会按照ASCII进行排序。

图1

Record Lock：使用条件为如果是根据唯一索引或者主键索引等值查询就会使用记录锁算法，如select * from m2 where id=1。

Gap Lock：使用条件为记录不存在的空间如select * from m2 where id=6这个时候会在4-7之间加一个Gap锁(是左开右开区间哦)，Gap锁的主要目的是阻塞在4-7之间插入数据。为此做如下实验首先对一条不存在的数据id=6加一个X锁，然后在4-7间隙中插入数据。

从上面图中的结果可以看出在4-7的间隙中是无法插入数据的，那么修改id=10的数据呢？从下图可以看到非常丝滑直接修改成功。

与此同时我再去看下当前执行的事物，可以看到我自己手动加的X锁，同时还有存储引擎自动加的Gap锁。

Next-key Lock：临界锁使用条件为范围查询，包含记录和区间，这是InnoDB中默认使用的行锁算法，想下为啥呢？请看图1如果默认使用记录锁那么当进行范围查询时则无法加锁，如果使用Gap锁会造成只要不在范围内的都加Gap锁无法适用于>=这种场景，而临界锁为左开右闭的形式，当查询id>5 and id<9的时候会发现7有记录，其他数据都在范围内这个时候就使用临界锁，当查询id>10的时候退化为Gap锁，当查询id=1的时候转化为记录锁。来看个试验。首先开启一个事物查询5<id<9的记录，由于临界锁会锁住(4,7]的下一个临界即(7,10]因此如果我开启另外一个事物查询id=10的记录也会被阻塞。

10、如何结束阻塞？

    上面介绍的阻塞情况在Navicat中即表示停止红色按钮亮起，那么如何结束上个事物从而让当前事物停止阻塞呢？答案是Commit或者Rollback，同时一个事物等待其他事物释放锁在InooDB中默认是50s，超过50s后就不再获取。

11、死锁

    ​死锁发生的条件主要有三种：1)互斥 2)不可剥夺 3)形成互相等待环路。举个例子去银行办理业务时一个柜员只能给一个客户办理业务，不可能同时给A客户办理取款业务又同时给B客户办理转账业务这叫做互斥，同时B客户也不可以在柜员在给A办理业务时直接中断柜员操作让柜员给自己办理业务这叫做不可剥夺。什么叫互相等待呢比如客户C去柜面办理取款业务这时候客户经理要给C客户先介绍理财产品，柜员说先办完​取款再去介绍，客户经理说先介绍完产品再办理取款，结果客户就一直等待着。 ​

总结：本文介绍了X锁、S锁、Record锁、Gap锁、Next-key锁等内容，下篇介绍MySQL事物。