MySQL 锁

MySQL如果按 锁的颗粒度划分，可以分为MySQL三大锁：全局锁，表级锁，行锁三大类。

最后一行是 Next-Key Lock ：间隙锁+行锁

---

Mysql 三大锁

全局锁

加全局锁
flush tables with read lock

释放全局锁
unlock tables
1
2
3
4
5

全局锁 是对整个数据库实例加锁，加全局读锁的命令是：Flush tables with read lock (FTWRL)，使用这个命令后，整个数据库处于只读状态，会阻塞其它线程的以下语句：1、对数据的增删改操作，比如 insert、delete、update等语句；2、对表结构的更改操作，比如 alter table、drop table 等语句。

全局锁的典型使用场景是，做全库逻辑备份，MyISAM中使用全局锁做全局逻辑备份，MySQL使用其它方式 mysqldump

---

全局锁的问题：

1. 会阻塞其它线程的更新、删除、修改操作，影响业务执行。

2. （读写分离的情况，就是主表用来 增删改，从表用到查询，从表会接收主表的binlog来增删改数据）

   • 在主库备份时，不能执行更新，业务基本停止
   • 在从库备份时，从库不能执行主库发过来的binlog，造成 主从延迟

问题的解决：
全局锁加上后，整个数据库都是只读模式，会影响业务，但是对于 MySQL这样支持 可重复读的隔离级别的引擎来说有其它更好解决的解决办法

• 在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

数据库备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。

但有些事务的引擎不支持事务比如 MyISAM，这种情况我们就只能使用FTWRL命令了

---

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
建议你用 FTWRL 方式，主要有两个原因：

1. 在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议你使用。
2. 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

MySQL中的表级别的锁：

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

表锁

表锁语法：lock tables … read / write
如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;
1
2
3
4
5

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

也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。（和全局锁很像，全局锁是锁所有的表，表锁是锁一张表）

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

主动释放锁
unlock tables

或 客服端断开的时候自动释放
1
2
3
4

---

举例：线程 A 中执行 lock tables t1 read, t2 write;

• 其它线程写 t1，读写 t2的语句会被阻塞，同时A线程也只能执行读 t1 和 读写 t2，但无法写 t1

• 写是排他锁，写锁意味着其他线程不能读也不能写。读锁是共享锁，加上后其他锁只能读不能写，本线程也不能写

如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

---

对于InnoDB 这种支持行锁的引擎，一般不使用lock tables，因为它的影响面还是太大，我们需要用更细粒度的锁。

元数据锁（MDL）

meta data lock

MDL 元数据锁
DML 数据操纵语言
DDL 数据定义语言

MDL 不需要显示操作，访问表的时候会自动加上。
对一张表进行 CRUD 操作时，自动加的是 MDL 读锁；
对一张表做结构变更操作的时候，自动加的是 MDL 写锁；

读锁之间不互斥，读写之间和写写之间互斥，所谓互斥就是不能同时发生，只有一个等另一个执行完才能开始执行

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

上面看着没有问题，但我们需要知道一点：MDL 读锁和写锁在事务提交后才释放，在语句开始时，自动加上
所有下面这种情况就会出问题：

sessionA 事务开始，然后会对这个表加一个MDL读表，select查询，然后sessionB的select查询，到这都没有问题，但是之后的sessionC会被阻塞，因为现在的读锁还没有释放，而sessionC需要写锁，但读写互斥，加不进来，所有只能被阻塞。
现在这个线程阻塞，其它线程不能读写，如果后面来的select语句频繁，还有session查询进来，这个库可能就很快爆满了。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

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

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

---

如何安全地给小表加字段（sessionC）？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

但考虑一下这个场景。如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？这时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 
1
2

一个小问题:
备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

意向锁

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

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

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

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

注意：意向锁是在表级别，而下面两个sql 在在行级别加锁

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

//先在表上加上意向独占锁，然后对读取的记录加独占锁
select ... for update;
1
2
3
4
5
6
7

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

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

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

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

所以，意向锁的目的是为了快速判断表里是否有记录被加锁。给表锁当辅助的

AUTO-INC 锁

我们在把表的主键设置为自增之后，在不指定主键的情况下，数据库会自动给主键赋递增的值，这就是通过AUTO-INC 锁实现的

它是特殊的表锁机制，不是在事务提交之后才释放，而是在执行完插入语句之后立即释放。

在插入时，加这个锁，然后主键字段赋值 递增，等插入语句执行完之后，锁就会释放掉

但是这种方式还是有一些问题，我们加AUTO-INC 锁之后，会阻塞其它事务的插入语句，尤其当有大量数据插入的时候，这个问题会更严重，更加影响性能，所以MySQL推出了轻量级锁，我们可以通过系统变量innodb_autoinc_lock_mode 来控制选 AUTO-INC 锁（设置为0），还是轻量级的锁（设置为2）。
轻量级的锁：给AUTO_INCREMENT 修饰的字段加锁，该字段赋值 自增的值之后，轻量级锁就释放了，不需要等到整个插入语句执行完后才释放锁。

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

行锁

InnoDB支持行锁，但MyISAM不支持。

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

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

//对读取的记录加独占锁   X锁
select ... for update;
1
2
3
4
5

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

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

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

Record Lock 记录锁

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

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

eg，当一个事务执行了下面这条语句：

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

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

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

Gap Lock 间隙锁

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

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

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

Next-Key Lock 临键锁

Next-Key Lock 称为临键锁，可能S锁可能X锁，主要看Record 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 时，是会被阻塞的。

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

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在阻塞期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

两阶段锁协议

上面两条语句是怎么执行的呢？
首先事务A在执行完两条update语句之后，还会持有行锁，在commit之后才释放。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。这样可以最大程度的减少事务之间的锁等待，提高并发度

死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都会进入无限等待的状态，称为死锁。

事务A和事务B都在互相等待对方的资源释放，就进入到死锁状态。
此时有两个策略：

1. 进入等待，直到超时，超时时间参数为 innodb_lock_wait_timeout
2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其它事务得以继续执行，将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

第一种情况，超时时间设置过长，等待时间就会太长，漫长的无法接受，如果设置过短，对于锁等待就可能误伤

所以一般使用第二种策略，主动死锁检测，在有死锁的情况下，可以很快检测出
每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

死锁检测也有缺点：因为每加入一个新来的被堵住的线程，都会判断会不会是这个线程导致的死锁，这一操作的时间复杂度是O(n)。假如有1000个并发线程同时更新同一行，死锁检测就是100万量级。这期间会消耗大量CPU资源，这时就会看到，CPU利用率很高，但每秒执行不了几个事务。

那么怎么解决死锁检测消耗大量CPU资源这件事呢？

第一种方法，就是在确认一定不会发生死锁的地方，关闭死锁检测。但这有一定风险，如果有死锁就会出现大量的超时。

第二种方法就是控制并发度，如果同一时间的并发数量少，那么死锁检测的成本就减少了。并发控制要做在数据库服务端，让对于同一行的更新在进入引擎前排队，这样InnoDB内部就不会有大量的死锁检测了。

还有一种方案是，把多条记录变为一条，比如更新账户的余额，我们可以把100条记录的值相加，求出总和，再去和账户的余额相加 求出余额，这样冲突就变成了原来的 1 / 100。但这个方案需要根据具体业务做调整，比如有退票的可能，余额不能是负数