MySQL的锁

很早之前就投的岗位, 然后昨天中午突然来了电话, 下午就面试, 结果就是~被按在地上摩擦了一番。


面试官：你先来个自我1~2分钟的自我介绍吧。
我：阿巴阿巴(10s不到)
面试官：额…
面试官：看你简历里面写到了MySQL的锁机制, 你能详细讲一下吗？
我：纳尼，早忘记了… 然后就只说了锁的分类，直接G
面试官：讲一下你的项目吧。
我：巴拉巴拉。

20分钟结束之后我就知道我G了, 所以主要在此记录一下, 激励自己一番。

MySQL中锁的分类

MySQL中的锁，按照锁的粒度划分可以分为三类：

• 全局锁：锁定所有数据库的所有表
• 表级锁：每次操作锁住整张表
• 行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个数据库就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
命令：

-- 加全局锁
flush tables with read lock;

-- 释放全局锁
unlock tables;

全局锁的电影应用场景是做全库的逻辑备份，对所有的表锁定，从而获取一致性视图，相当于拿到当时的原始快照。
命令：

mysqldump -uroot -pxxx mydb > mydb.sql

数据库中加全局锁是一个很重的操作，存在着一些问题：

• 如果在主库备份，那么在备份期间就不能执行更新操作，业务会受到影响
• 如果在从库备份，那么在备份期间就不能执行主库同步过来的二进制文件（binlog），会发生主从延迟

所以，在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction参数来完成不加锁的一致性数据备份

mysqldump --single-transaction -uroot -pxxxx mydb > mydb.sql

表级锁

顾名思义，每次操作锁住的是某张表。
对于表级锁，主要分为三类：

• 表锁
• 元数据锁
• 意向锁

表锁

表锁也分为两类：

• 表共享读锁（read lock）
• 表独占写锁（write lock）

读锁：都可读，但是都阻塞写
写锁：自己可读可写，阻塞其他客户端的读、写


语法：
??加锁：lock tables 表名 read/write
??解锁：unlock tables

元数据锁

MDL(meta data lock)，元数据锁。
MDL锁的过程是系统自动控制的，在访问一张表的时候会自动加上，无需我们来显式使用。MDL锁主要是为了维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
这里的元数据，可以简单理解为一张表的表结构。也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的。
当对一张表进行增删改查的时候，加MDL读锁(读锁)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

1. case_one
   使用的SQL：
   ?lock tables xxx read/write
   锁的类型：
   ?SHARED_READ_ONLY/SHARED_NO_READ_WRITE
2. case_two
   使用的SQL：
   ?select、select … lock in share mode
   锁类型：
   SHARED_READ
   说明：
   与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥。
3. case_three
   使用的SQL：
   insert、update、delete、select … for update
   锁类型：
   SHARED_WRITE
   说明：
   与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥。
4. case_four
   使用的SQL：
   alter table …
   锁类型：
   EXCLUSIVE
   说明：
   与其他的MDL都互斥

总结：增删改查的时候不允许修改表结构。

意向锁

为了避免DML在执行时，加的行锁与表锁冲突(InnoDB默认为行锁)，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
一个客户端在执行DML操作时，会对涉及到的行加上行锁，同时也会对该表加上意向锁。其他客户端再对这张表加表锁的时候，会根据该表上所加的意向锁来判断是否可以成功加表锁，而不用逐行判断行锁情况了。(假如有五行，从第一行开始，看一下有没有加行锁，没加？好，看下一行。一直到第五行，加了行锁？好，表锁添加失败。这样无疑浪费了时间和性能)。
分类：

• 意向共享锁（IS）：由语句 select … lock in share mode 添加。与表锁共享锁(read)兼容，与表锁排它锁(write)互斥。
• 意向排它锁(IX)：由 insert、update、delete、select … for update 添加。与表锁共享锁(read)及排它锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排它锁都会自动释放。


查看意向锁及行锁的加锁情况：
?select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其它事务对此行进行 update 和 delete。在RC、RR隔离级别下都支持。
• 间隙锁（Gao Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其它事务在这个间隙进行 insert，产生幻读。在RR隔离级别下都支持。
• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gao。在RR隔离级别下支持。

行锁

InnoDB实现了两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁。

兼容情况：
只有当前锁和请求锁的类型都是共享锁才兼容，其他情况都会发生冲突。

常见的SQL语句在执行时所加的行锁（针对索引列）：

需要注意的是，如果是根据非索引列进行操作的话，会出现行锁升级为表锁的情况。

间隙锁&临键锁

默认情况下，InnoDB在RR（REPEATABLE READ）事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读。

• 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
  有m , n, 查询m-n之间的数，不存在，则锁住m-n的间隙，但是不包括x, y
• 索引上的等值查询（非唯一普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
  假如我们有数据10,13,15，我们根据二级索引查询值为10的数据并加上共享锁，我们并不是只锁定10这一行，因为是非唯一索引，索引会继续往后找，直到找到一个不满足的值，并对10加临键锁，并对第一个不满足的值之前的间隙加锁。
• 索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止。
  我们有数据10,15,19… 。假如查询的条件为id>=10，并添加共享锁，我们此时可以将数据分为三部分：[10]，(10, 15], (15, 19], (19, +00]。所以数据库加的锁：10加了行锁，15的临键锁（包含15以及15之前的间隙），19的临键锁（包含19以及19之前的间隙），正无穷的临键锁（正无穷以及之前的间隙）。

注意：间隙锁唯一目的是防止其它事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。