mysql元表 mysql 元数据锁

转载

我心依旧 2023-12-25 07:14:36

文章标签 mysql元表读锁 mysql 元数据 文章分类 MySQL 数据库

全局锁

全局锁是锁住整个数据库实例，只能读，任何关于更新操作的语句都会阻塞。

全局锁的适用场景

针对数据库做全库的逻辑备份操作时，需要使用全局锁。

全局锁的影响：

如果在主库上做全局锁操作，业务基本停摆
如果在从库上做全局锁操作，备份期间从库不能更新主库同步过来的binlog，可能导致主从不一致

如果不加锁，备份完成后可能得到不一致的状态，不安全，所以一定要加锁。

如何加全局锁？

非innodb引擎，需要使用Flush table with read lock命令
innodb引擎，可以使用mysqldump命令实现，加入一个参数 --single-transaction，在备份前开启一个事务，保证视图的一致性。
（不建议使用）set global readonly=true; 原因如下：

修改参数的影响面大。有些系统中，这个参数用来作其他用途，比如判断是主库还是从库，因此修改这个参数的影响面比较大。
异常的处理机制不友好。FTWRL如果客户端连接异常断开，mysql会自动释放全局锁；如果设置参数，出现异常后，数据库仍旧是readonly为true的状态，风险较高。

表锁

表锁是锁住整张表，通过不同的表锁设置，控制并发访问。某些引擎不支持行锁，需要通过表锁来控制并发。支持行锁的引擎，就不建议使用表锁了。

如何加表锁？

lock tables t1 read,t2 write;
这个语句有两个含义：

对其他线程来说，t1表，可以读，不可以写；t2表，读写都不可以
对本线程来说，t1表只能读，t2表只能读写

元数据锁（Metadata Lock 简称MDL）

元数据锁主要是面向DML和DDL之间的并发控制，如果对一张表做DML增删改查操作的同时，有一个线程在做DDL操作，不加控制的话，就会出现错误和异常。元数据锁不需要我们显式的加，系统默认会加。

元数据锁的原理

当做DML操作时，会申请一个MDL读锁
当做DDL操作时，会申请一个MDL写锁
读锁之间不互斥，读写和写写之间都互斥。

实验验证

mysql实验环境：5.7
mysql客户端：mysql命令行工具

一共开启3个session，SessionA,SessionB,SessionC。

第一次实验：
时间线和执行命令如下

A：begin; select * from t;-------------------------------------------------commit;------------
----------------------------B: alter table t add f1 int;-----------------------------------------
--------------------------------------------------------C: select * from t;----------------------

实验结果：
在执行commit前，B和C都会阻塞住。
执行commit后，看起来B先返回数据，C后返回数据。

第二次实验：
时间线和执行命令如下

A：begin; select * from t;---------------------------------------------------------commit;----
----------------------------B: alter table t add f2 int;-----------------------------------------------
----------------------------------------------------------C: begin; select * from t;------------commit-

实验结果：
在执行commit前，B和C都会阻塞住。
执行commit后,B正常返回，C依旧阻塞住。

在B执行commit后，C正常返回。