MySQL锁 - 简单易懂

1、MySQL锁的类型

        在 MySQL 里，根据加锁的范围，可以分为 全局锁、表级锁 和行锁 三类。锁是一种常见的并发事务的控制方式。

MySQL锁类型 

 1.1、共享锁和排他锁

        不论是表级锁还是行级锁，都存在共享锁（Share Lock ， S锁）和排他锁（Exclusive Lock ， X 锁）。

• 共享锁（S 锁）：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
• 排他锁（X 锁）：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

 注意：排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

1.2、全局锁 

使用全局锁前要先在数据库中执行命令：

flush tables with read lock

执行后，此时整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞

• 对数据的增删改操作，比如 insert、delete、update等语句；
• 对表结构的更改操作，比如 alter table、drop table等语句。

释放全局锁命令：

unlock tables

 就算不执行，当会话断开了，全局锁会被自动释放

        应用场景：

        全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

1.3、表级锁

MySQL 里面表级别的锁有以下四种：

1. 表锁
2. 元数据锁（MDL）
3. 意向锁
4. AUTO-INC 锁

1.3.1、表锁

如果我们想对订单表（common_order ）加表锁，可以使用下面的命令：

//表级别共享锁 - 读锁；
lock tables d_student read;

//表级别独占锁 - 写锁；
lock tables d_stuent write;

注意：表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

        如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。

释放当前会话的所有表锁：

unlock tables

当会话退出后，也会释放所有表锁。

        不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 实现了颗粒度更细的行级锁 。

1.3.2、元数据锁 (MDL)

当我们对数据库表进行操作时，会自动给这个表加上 MDL：

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

        当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

        当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 是在事务提交后才会释放，这意味着 事务执行期间， MDL 是一直持有的 。

        如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

1. 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上MDL 读锁；
2. 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
3. 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

这是由于申请 MDL 锁的操作会形成一个队列，队列中 写锁 获取优先级高于读锁 ，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。  

1.3.3、意向锁 

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享 锁」；
• 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占 锁」；

当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

不过， select 也是可以对记录加共享锁和独占锁的，具体方式如下：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

//先在表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;

        意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables... write）发生冲突。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历 表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

        意向锁的目的是为了快速判断表里是否有记录被加锁

1.3.4、AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁 实现的。

AUTO-INC 锁是特殊的表锁机制，锁 不是再一个事务提交后 才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

• 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
• 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
• 当 innodb_autoinc_lock_mode = 1：
  • 普通 insert 语句，自增锁在申请之后就马上释放；
  • 类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释 放；

当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题 。

这是我遇到的一个实际场景，分享一下：

session A 往表 t 中插入了 4 行数据，然后创建了一个相同结构的表 t2 ，然后 两个 session 同时执行向表 t2 中插入数 据 。

如果 innodb_autoinc_lock_mode = 2 ，意味着「申请自增主键后就释放锁，不必等插入语句执行完」。那么就可能出现这样的情况：

• session B 先插入了两个记录，(1,1,1)、(2,2,2)；
• 然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；
• 之后，session B 继续执行，插入两条记录 (4,3,3)、(5,4,4)。

 可以看到，session B 的 insert 语句，生成的 id 不连续。

当「主库」发生了这种情况， binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句，如果binlog_format=statement，记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句，要么先记session B 的 insert 语句。

但不论是哪一种，这个 binlog 拿去「从库」执行，这时从库是按「顺序」执行语句的，只有当执行完一条 SQL 语句后，才会执行下一条 SQL 。因此，在 从库上「不会」发生像 主库那样两个 session 「同时」执行向表 t2 中插入数据的 场景。所以，在备库上执行了 session B 的 insert 语句， 生成的结果里面， id 都是连续的。这时，主从库就发生了 数据不一致 。

要解决这问题， binlog 日志格式要设置为 row ，这样在binlog 里面记录的是主库分配的自增值，到备库执行的时候，主库的自增值是什么，从库的自增值就是什么。

所以， 当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row ，既能提升并发性，又不会出现数据 一致性问题 。

1.4、行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中， 因为当事务提交了，锁 就会被释放 ，所以在使用这两条语句的时候，要加上begin、 start transaction 或者 set autocommit = 0 。

共享锁（ S 锁）满足读读共享，读写互斥。独占锁（ X 锁）满足写写互斥、读写互斥。

行级锁的类型主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

1.4.1、Record Lock 

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X锁不兼容）；

• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X锁不兼容）。

举个例子，当一个事务执行了下面这条语句：当事务执行commit 后，事务过程中生成的锁都会被释放。

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。

 

当事务执行 commit 后，事务过程中生成的锁都会被释放。  

1.4.2、Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（ 3 ， 5 ）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包 含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁 的目的是防止插入幻影记录而提出的 。

 1.4.3、Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务 将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-keylock，那么另外一个事务在获取相同范围的 X 型的 next-keylock 时，就会被阻塞。

虽然相同范围的间隙锁是多个事务相互兼容的，但对于记录锁，我们是要考虑 X 型与 S 型关系，X 型的记录锁与 X 型的记录锁是冲突的。