MySQL-06：全局锁和表锁

冷不防 2022-12-22 13:29 253阅读 0赞

数据库在处理并发性请求的需要，避免出现并发性问题，需要对数据库资源指定合理的访问规则，**锁**就是用来实现这些访问规则的数据结构。

根据加锁的访问，MySQL中的锁分为**全局锁，表级锁，行级锁**。

### 1.1全局锁 ###

**全局锁：** 一旦加锁，会对整个数据库实例加锁。

MySQL提供加锁命令：Flush tables with read lock (FTWRL)，解锁命令：unloack tables或者客户端断开与数据库的连接。

在全局加锁的情况下，整个库处于只读的状态下，以下命令都会被阻塞：数据库定义语句(DDL)，数据库更新语句(DML)，数据库事务提交语句(DCL)。

全局锁的使用场景：做全库逻辑备份，全库逻辑备份：将数据按一定的逻辑关系导出到文本文件存储。但是一旦使用FTWRL指令，那么有关与当前数据库的业务也基本停摆了。

如果在不加锁的情况下来执行备份，备份恢复的时候难免会出现与实际情况不符合的情况。

第二种方式，之前有提到事务中有一个事务隔离级别为：可重复读，在当前级别下，在创建一个事务时，便会创建一个视图，后续事务内的操作都会依据该视图。

MySQL官方依据这个逻辑的备份工具mysqldump，它会将查询出来的语句转化为insert语句，当需要回复备份的时候，执行这些insert即可。当mysqldump使用参数-single-transaction的时候，导数据之前会启动一个事务拿到一致性视图，而依据MVVC特性，对库的操作还是正常的。

但是mysqldump工具只能支持事务的引擎使用，想MySQL的原生引擎MyISAM是无法使用的。

FTWRL指令的目的是为了全局只读，那它于set global readonly=true有什么区别呢？

1.  异常处理上的分歧：FTWRL下的全局锁在数据库与客户端断开之后，便会恢复指令之前的状态，而使用readonly则不会，会一直保持readonly，需要手动设置回来。
2.  在有些系统中，readonly属性可能另作他用，例如用来区分主从库。

### 1.2表级锁 ###

MySQL中表级锁分为两种：**表锁** 和 **元数据锁** 。

**表锁：** 语法→使用lock tables … read/write来加锁；使用unlock tables或者断开与客户端的连接都会解锁。lock tabls除了会限制别的线程对目标表的操作，也会限制当前线程对目标表的操作方式。

例如 加锁语句：lock tables T1 read,T2 write;，这样会给T1加读锁，T2加写锁，其他线程对T1可读不可写，对T2不可读也不可写，共享读锁，独占写锁，这里的共享/独占指的是资源。加上读锁，不会限制别的线程读，但会限制别的线程写。加上写锁，会限制别的线程读写。

**MDL：** MDL 不需要显式使用，在访问一个表的时候会被自动加上。 元数据锁是server层的锁，表级锁，主要用于隔离DML和DDL操作之间的干扰。每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）。

**如何安全地给小表加字段？**

长事务会长期持有锁，往往会导致后续的DDL语句无法执行阻塞挂起，从而阻塞住大部分的查询/更新数据请求，导致库挂掉。

1.  先解决长事务问题，事务不提交MDL锁就不会释放，后续修改表的操作便不能执行下去。 在 MySQL 的 information\_schema 库的 innodb\_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
2.  如果更新的表为热点表，kill事务并不是一个好决定，比较理想的机制为设定等待时间，一段时间如果获取了锁并执行，获取不了则放弃。

**练习问题：**

1.  什么是锁？MySQL中锁分几种？
2.  全局锁如何加锁？如何解锁？有几种实现方式？
3.  做数据库备份时为什么要加全局锁？
4.  MySQL的自带备份工具, 使用什么参数可以确保一致性视图, 在什么场景下不适用?
5.  不建议使用set global readonly = true的方法加全局锁有哪两点原因?
6.  表锁分几种？如何加锁？如何解锁？
7.  MDL中读写锁之间的互斥关系？
8.  如何安全地给小表加字段？