mysql锁相关

0 概述

今天我们就来聊聊mysql锁相关事情，下图给出mysql使用InnoDB存储层相关锁的概述，本文所在实验可重复读 （repeatable read）隔离级别下。

建用户表，为后续测试使用

CREATE TABLE `user` (
  `id` bigint NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `nick` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_nick` (`nick`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

1 锁的粒度&范围

1.1全局锁

全局锁就是对整个数据库实例加锁，当你需要让整个库处于只读状态时候可以这个全局锁。加上这个锁以后，其他线程以下语句将会被阻塞：DML（数据操作增删改）DDL（数据定义包含建表、修改、删除表结构）和各类更新事务的提交语句。

使用场景：全局锁的典型使用场景是，做全库逻辑备份；也就是把整库每个表都 select 出来存成文本。也就是说，在整个备份过程中，整个库都是只读的，其实这样风险挺大的。如果是在主库备份，会导致业务不能修改数据；而如果是在从库备份，就会导致主从延迟。好在 mysqldump 包含一个参数 --single-transaction，备份数据志强就会启动一个事务，来确保拿到一致性视图，由于mvcc支持，备份过程数据是可以正常更新的，但是需要所有表都是事务引擎表。所以这也是建议使用 InnoDB 存储引擎的原因之一。
如果执行全局锁 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态，这个是和set global readonly=true方式让数据库进入只读状态的一个很大区别。
特点：加锁快、开销小、不会出现死锁；但是期锁粒度比较大，并发度最低，在客户端发生异常的端口，mysql会释放这个全局锁。
加锁&解锁语句：
加锁：flush tables with read lock
解锁：unlock tables;

1.2 表级别锁

mysql 里面表级别的锁有两种：一种是表锁，一种元数据锁（meta data lock，MDL）。
表锁：
使用场景：在没有更细粒度锁之前，表锁是最常用的处理并发方式。当然像InnoDB这种支持行锁的引擎一般不会使用lock tables命令来控制并发。
加锁&解锁语句：
加锁：lock tables xxxx（表名） read/wirite; demo: lock tables user write;
解锁：unlock tables；
特点：加锁快、开销小、不会出现死锁；但是期锁粒度比较大，并发度低

session A 给user表加了写锁

session B 读写都会被阻塞

使用show processlist 命令查看执行情况。

另一类表级别的锁是MDL(metadata lock)。MDL不需要显示使用，在访问一个表的时候会被自动加上，是系统默认会加的。当对一个表做增删改查的时候会加MDL读锁；当要对表做结构变更的时候会加MDL写锁。
session A 开启一个事务，执行查询，加了MDL 读锁。

sessionB 加MDL写锁，修改user表结构线程被阻塞，

sessionC 加MDL读锁查询user 表数据线程也被阻塞。

从上面实验可以看出，不仅仅sessionB被block，后续请求都会被阻塞，等于这个标完全不可读写了。如果这个表的查询语句频繁，而且客户端有重试机制，这样的话这个数据库很快就会爆满。因此在做DDL时候，需要谨慎一点，首先需要解决长事务、因为事务部提交会一直占着锁，可以考虑kill 这个长事务。对于变更热点表（读写请求频繁），在DDL时候，可以考虑在流量低峰时间做，或者在变更表结构语句中加上等待时间。

1.2 行锁

mysql的行锁是在引擎层由各个引擎自己实现的，但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁而InnoDB支持行锁。不支持行锁意味着并发控制只能使用表锁。这也是MyISAM被InnoDB替代的重要原因之一。

**Intention Locks（意向锁）：**用于事务稍后对表中的某些行需要加上哪种锁类型（共享或者排他锁），也就是提前锁定。如果命中了索引会加记录锁，否则的会加表锁
加锁方式：select XXXX for share; select XXXX for update;
Record Locks（记录锁）： 记录锁是索引记录上锁。例如 select * from user where id=1 for update，这样就可以防止其它任何事务插入、更新删除id=10的行。
Gap Locks （间隙锁）： 间隙锁和 next-key lock 的引入，是为了解决幻读的问题。
间隙锁，锁定一个索引记录的范围，但不包括记录本身。例如 select * from user where id between 100 and 150 for update;这个就会阻止其他事务在100~150 插入新的数据，当然更新也不行。
Next-Key Lock： 可以简单理解为记录锁+间隙锁。

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

**实验一 **
session A，InnoDB存储引擎会在 id = 1 这个主键索引上加一把 S 锁（共享锁或者叫读锁）。

begin;
select * from user where id=1 lock in share mode;
或者写成这样
select * from user where id=1 for share;

值得说明是：普通select 查询不会默认加行锁

• lock in share mode 是价共享锁，加上共享锁（读锁）之后其他事务可以对该数据加共享锁，但是不是加排他锁，可以读这行数据，但是不能修改。
• for update 排他锁，如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据。
  
  session B

begin;
update user set name='hsc' where id=2;
update user set name='hsc' where id=1;

从上面两个实验可以看出，sessionB执行更新行Id=2 没有阻塞，更新id=1阻塞了，直到session A 事务提交后才能继续执行，由此这可以看出session A的锁并没有释放，直到commit后才释放的。
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
值得说明的是InnoDB行锁是加在主键索引上的，如果条件是非组件索引select * from user where nick=“test” lock in share mode;则会通过普通索引找到主键索引然后进行加锁。
实验二 从下面实验不难发现，在没有命中索引的话，行锁会退化为表锁。
session A 这里sex 上没有索引。

 select * from user where sex=1 lock in  share mode;

'session B 更新Id=3 行数据，session B被阻塞。

 update user set name='hsc' where id=3;

实验三 间隙锁实验
session A 对id100-150加上间隙锁,其锁住的范围是[100,150]

select * from user where id between 100 and 150 for update;

被锁住的范围内，更新被阻塞

被锁住的范围内，插入被阻塞

不在范围的是可以插入的

实验四 间隙锁范围实验
先看表中数据，如下图所示，因此可以产生的间隙(3,150),(150,510),(510,positive infinity)

参考文献
[1] http://mysql.taobao.org/monthly/2017/12/02/?spm=ata.21736010.0.0.181364a2l8bwMw
[2] https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
[3] 高性能mysql 第三版
[4]极客时间mysql实战45讲