MySQL 锁

最新推荐文章于 2024-07-07 14:07:29 发布

猴子会跳舞

最新推荐文章于 2024-07-07 14:07:29 发布

阅读量2k

点赞数 3

分类专栏： mysql 文章标签：数据库

本文链接：https://blog.csdn.net/m0_51809035/article/details/127442787

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

Mysql 三大锁
全局锁
表级锁
行锁
两阶段锁协议
- 死锁和死锁检测

MySQL如果按锁的颗粒度划分，可以分为MySQL三大锁：全局锁，表级锁，行锁三大类。
在这里插入图片描述
最后一行是 Next-Key Lock ：间隙锁+行锁

Mysql 三大锁

全局锁

加全局锁
flush tables with read lock

释放全局锁
unlock tables

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

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

全局锁的问题：

会阻塞其它线程的更新、删除、修改操作，影响业务执行。
（读写分离的情况，就是主表用来增删改，从表用到查询，从表会接收主表的binlog来增删改数据）
- 在主库备份时，不能执行更新，业务基本停止
- 在从库备份时，从库不能执行主库发过来的binlog，造成 主从延迟

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

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

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

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

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

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

表级锁

MySQL中的表级别的锁：

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

表锁

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

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

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

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

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

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

主动释放锁
unlock tables

或 客服端断开的时候自动释放

举例：线程 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 ...

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

意向锁

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

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

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

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

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

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

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

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（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;

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。
在这里插入图片描述

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

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
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;

就是对 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 提交了事务。