MySQL --- 锁的粒度和类型、死锁

lalala / 2023-05-03 / 原文

参考

https://xiaolincoding.com/mysql/lock/mysql_lock.html

全局锁（备份数据库）

加全局锁

flush tables with read lock

释放全局锁

unlock tables

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

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

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

但是这样会影响业务，那有什么其他方式可以避免？

如果数据库的引擎支持的事务支持 可重复读的隔离级别，那么在 备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数的时候，就会在备份数据库之前先开启事务。这种方法只适用于支持「可重复读隔离级别的事务」的存储引擎。

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

表级锁

1、表锁

　　加表锁

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

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

　　释放表锁

unlock tables

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

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

2、MDL锁（元数据锁--表结构变更）

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

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

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

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

- 当有线程在执行 select、insert、delete、update 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select、insert、delete、update 语句（释放 MDL 读锁）。
- 反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。

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

如果一个进行 CRUD 的长事务A一直没有提交，这时候它长时间持有MDL读锁不释放。此时一个新线程 B 想要变更表结构，申请了 MDL 写锁，就会在队列进行等待。而它后面想要进行 CRUD 操作的所有线程就会一直阻塞住，这时数据库的线程很快就会爆满了。因为它们要申请 MDL 读锁，而在队列里前面有优先级更高的线程 B申请的 MDL 写锁在等待。

2、意向锁（快速判断表里是否有记录被加行级X独占锁）

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

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。不过，select 也是可以对记录加共享锁和独占锁的，需要特殊指定。

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

意向共享锁和意向独占锁是表级锁

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

它的作用是什么呢？

假如Innodb需要对一个table上一个表锁（例如数据备份、alter table、drop table、 create index 之类的操作，系统会对整个表锁定，这说的表锁不是意向锁，请大家不要混淆），就需要先判断是否有某个行被上了行锁，如果有的话，说明某个或某些行正在执行读写操作，系统需要等待这个写操作完成了才能做备份之类的工作，才能加表级X独占锁。