1、MySQL中的并发安全问题

如果有多个并发请求存取数据，就可能会产生多个事务同时操作同一行数据的情况。如果并发操作不加控制，不加锁的话，就可能写入了不正确的数据，或者导致读取了不正确的数据，破坏了数据的一致性，因此需要考虑加锁。
SQL标准规定不同事务隔离级别下可能发生的问题不一样：

  • 在READ UNCOMMITTED隔离级别下，脏读、不可重复读、幻读都可能发生；
  • 在READ COMMITTED隔离级别下，不可重复读、幻读可能发生；
  • 在REPEATABLE READ隔离级别下，幻读可能发生；
  • 在SERIALIZABLE隔离级别下，上述问题都不可以发生。
  • 脏读:一个事务A读取到事务B未提交的数据，就是脏读。
  • 不可重复读：事务A被事务B干扰到了，在事务A范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。
  • 幻读：事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

    2、MySQL锁介绍

    按照锁的粒度来说，MySQL主要包含三种类型（级别）的锁定机制：
    全局锁：锁的是整个database。由MySQL的SQL layer层实现的
    表级锁：锁的是某个table。由MySQL的SQL layer层实现的
    行级锁：锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。
    按照锁的功能来说分为：共享锁和排他锁。

    3. 共享/排他锁

    共享锁（S锁）：又称为读锁，可以查看但无法修改和删除的一种数据锁。
    加锁方式：select…lock in share mode
    在事务要读取一条记录时，需要先获取该记录的S锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排它锁。获准共享锁的事务只能读数据，不能修改数据。 共享锁下其它用户可以并发读取，查询数据。但不能修改，增加，删除数据。

排它锁（X锁）：又称为写锁、独占锁。
加锁方式：select…for update
在事务需要改动一条记录时，需要先获取该记录的X锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。这就保证了其他事务在T释放A上的锁之前不能再读取和修改A。

兼容性 S X
S 兼容 不兼容
X 不兼容 不兼容

X锁和S锁是对于行记录来说的话，因此可以称它们为行级锁或者行锁。可以认为行锁的粒度就比较细，其实一个事务也可以在表级别下加锁，对应的，称之为表锁。给表加的锁，也是可以分为X锁和S锁的。

2. 全局锁

全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句， 已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

DDL（Data Definition Language），即数据定义语言，例如建数据库、建表等，都属于数据定义语言； DML（Data Manipulation Language），即数据操纵语言，常用的增删改查就属于DML，操作对象是数据表中的记录；

加全局锁的命令为：mysql> flush tables with read lock；
释放全局锁的命令为：mysql>unlock tables；或者断开加锁session的连接，自动释放全局锁。
说到全局锁用于备份这个事情，还是很危险的。因为如果在主库上加全局锁，则整个数据库将不能写入，备份期间影响业务运行，如果在从库上加全局锁，则会导致不能执行主库同步过来的操作，造成主从延迟。
对于Innodb这种支持事务的引擎，使用mysqldump备份时可以使用--single-transaction参数，利用mvcc提供一致性视图，而不使用全局锁，不会影响业务的正常运行。而对于有MyISAM这种不支持事务的表，就只能通过全局锁获得一致性视图，对应的mysqldump参数为--lock-all-tables。

3. 表级锁

MySQL的表级锁有四种：

  1. 表读、写锁
  2. 元数据锁（meta data lock，MDL）
  3. 意向锁 Intention Locks（InnoDB）
  4. 自增锁（AUTO-INC Locks）

    3.1 表读、写锁

    MySQL 实现的表级锁定的争用状态变量：mysql> show status like 'table%'
    MySQL锁 - 图1
  • table_locks_immediate：产生表级锁定的次数；
  • table_locks_waited：出现表级锁定争用而发生等待的次数；

表锁有两种表现形式：

  • 表共享读锁（Table Read Lock）
  • 表独占写锁（Table Write Lock）

手动增加表锁：
lock table 表名称 read(write),表名称2 read(write)，其他
删除表锁：
unlock tables

3.2 元数据锁

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了 一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MySQL锁 - 图2

  1. session1（Navicat）、session2（mysql） 
  2. 1、session1: begin;--开启事务 select * from mylock;--加MDL读锁
  3. 2、session2: alter table mylock add f int; -- 修改阻塞
  4. 3、session1：commit; --提交事务 或者 rollback 释放读锁
  5. 4、session2：Query OK, 0 rows affected (38.67 sec) --修改完成 Records: 0 Duplicates: 0 Warnings: 0

3.3 意向锁 Intention Locks

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。
意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存，表明“某个事务正在某些行持有了锁、或该事务准备去持有锁” 。
例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁（或共享锁）就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。
意向锁分为两类：

  • 意向共享锁：简称IS锁，当事务准备在某些记录上加S锁时，需要现在表级别加一个IS锁。
  • 意向排他锁：简称IX锁，当事务准备在某条记录上加上X锁时，需要现在表级别加一个IX锁。

意向锁相互兼容，因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。整体兼容性如下：

兼容性 IS IX S X
IS 兼容 兼容 兼容 不兼容
IX 兼容 兼容 不兼容 不兼容
S 兼容 不兼容 兼容 不兼容
X 不兼容 不兼容 不兼容 不兼容

因为上了表级S锁后，不允许其他事务再加X锁，所以表级S锁和X、IX锁不兼容；而上了表级X锁后，会修改数据，所以表级X锁和 IS、IX、S、X（即使是行排他锁，因为表级锁定的行肯定包括行级锁定的行，所以表级X和IX、行级X）不兼容。
注意：上了行级X锁后，行级X锁不会因为有别的事务上了IX而堵塞，一个mysql是允许多个行级X锁同时存在的，只要他们不是针对相同的数据行。

3.4 自增锁(AUTO-INC Locks)

AUTO-INC锁是一种特殊的表级锁，发生涉及AUTO_INCREMENT列的事务性插入操作时产生。
在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值，多个事务插入记录时，如何保证记录AUTO_INCREMENT属性的列的自增性呢？就是采用的AUTO-INC锁，一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，可以保证一个语句中分配的递增值是连续的。

4. 行级锁

MySQL的行级锁，是由存储引擎来实现的，这里我们主要讲解InnoDB的行级锁。

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

InnoDB的行级锁，按照锁定范围来说，分为四种：

  • 记录锁（Record Locks）:锁定索引中一条记录。
  • 间隙锁（Gap Locks）:要么锁住索引记录中间的值，要么锁住第一个索引记录前面的值或者最后一个索引记 录后面的值。
  • 临键锁（Next-Key Locks）:是索引记录上的记录锁和在索引记录之前的间隙锁的组合（间隙锁+记录 锁）。
  • 插入意向锁(Insert Intention Locks)：做insert操作时添加的对记录id的锁。

InnoDB的行级锁，按照功能来说，分为两种：

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

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁。
手动添加共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
手动添加排他锁（x）：SELECT * FROM table_name WHERE ... FOR UPDATE

记录锁(Record Locks)

  1. 记录锁，仅仅锁住索引记录的一行，在单条索引记录上加锁。
  2. 记录锁锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
  3. 所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

一般看死锁日志时，都是找关键词，比如lock_mode X locks rec but not gap，就表示一个X型的记录锁。记录锁的关键词就是rec but not gap。

-- 加记录共享锁
select * from t1_simple where id = 1 lock in share mode;
-- 加记录排它锁 
select * from t1_simple where id = 1 for update;

间隙锁(Gap Locks)

间隙锁，顾名思义就是给记录之间（开区间）的间隙加上锁。需要注意的是，间隙锁只存在于可重复读（Repeatable Read）隔离级别下。
在事务的四大隔离级别中，不可重复读会产生幻读的现象，但是Mysql通过引入间隙锁的实现来解决幻读，通过给符合条件的间隙加锁，防止再次查询的时候出现新数据产生幻读的问题。
幻读是指在同一事务中，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。间隙锁的提出正是为了防止幻读中描述的幻影记录的插入而提出的，举个例子。
sessionA sessionB
begin;
select * from user where age=5;(N1)

insert into user values(2, ‘大波浪’, 5)
update user set name=’达闻西’ where age=5;
select * from user where age=5;(N2)

sessionA 中有两处查询N1和N2，它们的查询条件都是 age=5，唯一不同的是在N2处的查询前有一条更新语句。
照理说在 RR 隔离级别下，同一个事务中两次查询相同的记录，结果应该是一样的。但是在经过更新语句的当前读查询后（更新语句的影响行数是2），N1和N2的查询结果并不相同，N2的查询将 sessionB 插入的数据也查出来了，这就是幻读。
而如果在 sessionA 中的两次次查询都用上间隙锁，比如都改为select * from user where age=5 for update。那么 sessionA 中的当前读查询语句至少会将id在(-∞, 5)和(5, 10)之间的间隙加上间隙锁，不允许其他事务插入主键id属于这两个区间的记录，即会将 sessionB 的插入语句阻塞，直到 sessionA 提交之后，sessionB 才会继续执行。
也就是说，当N2处的查询执行时，sessionB 依旧是被阻塞的状态，所以N1和N2的查询结果是一样的，都是(5,重塑,5)，也就解决了幻读的问题。
MySQL锁 - 图3

临键锁(Next-Key Locks)

  1. next-key lock 是 record lock 与 gap lock 的组合，它既阻止了其他事务在间隙的插入操作，也阻止了其他事务对记录的修改操作。左开右闭区间，例如(5,8]。
  2. 默认情况下，innodb使用next-key locks来锁定记录。select … for update

    加锁规则

    ``` 1）主键索引
    1. 等值查询 （1）命中记录，加记录锁。 （2）未命中记录，加间隙锁。
    2. 范围查询 （1）没有命中任何一条记录时，加间隙锁。 （2）命中1条或者多条，包含where条件的临键区间，加临键锁

2）辅助索引

  1. 等值查询 （1）命中记录，命中记录的辅助索引项+主键索引项加记录锁，辅助索引项两侧加间隙锁。 （2）未命中记录，加间隙锁
  2. 范围查询 （1）没有命中任何一条记录时，加间隙锁。 （2）命中1条或者多条，包含where条件的临键区间加临键锁。命中记录的id索引项加记录锁。 ``` MySQL行锁规则

    插入意向锁(Insert Intention Locks)

    插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。
    总结来说，插入意向锁的特性可以分成两部分：
  1. 插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
  2. 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

需要强调的是，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。
插入意向锁在锁定区间相同但记录行本身不冲突的情况下互不排斥。

锁相关参数

Innodb所使用的行级锁定争用状态查看：

mysql> show status like 'innodb_row_lock%';

image.png

- Innodb_row_lock_current_waits：当前正在等待锁定的数量；
- Innodb_row_lock_time：从系统启动到现在锁定总时间长度； 
- Innodb_row_lock_time_avg：每次等待所花平均时间； 
- Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间；
- Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

对于这5个状态变量，比较重要的主要是：

- Innodb_row_lock_time_avg（等待平均时长）
- Innodb_row_lock_waits（等待总次数）
- Innodb_row_lock_time（等待总时长）这三项。

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的 等待，然后根据分析结果着手指定优化计划。
查看事务、锁的sql：

select * from information_schema.innodb_locks; 
select * from information_schema.innodb_lock_waits;
select * from information_schema.innodb_trx;

参考文档

MySQL的锁机制 - 记录锁、间隙锁、临键锁
大厂面试官必问的Mysql锁机制
深入理解Mysql——锁、事务与并发控制
再谈mysql锁机制及原理—锁的诠释