MySQL 全局锁与表锁笔记

MySQL

Word count: 1.9kReading time: 6 min

 2019/10/27   Share

MySQL-全局锁与表锁笔记

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

根据加锁的范围,MySQL的锁大致可分为全局锁,表级锁和行锁三类

全局锁

全局锁就是对整个数据库实例加锁

MySQL提供了一个加全局读锁的方法,命令是Flush tables with read lock简称FTWRL

使用FTWRL会让整个库处于读状态,之后其他线程的数据更新语句,数据定义语句,更新类事务的提交语句就会被阻塞

全局锁的典型应用场景就是全库逻辑备份

在使用全局锁会让整个库都只读会出现的风险也非常大

若在主库上备份,那么在备份期间都不能执行更新,业务基本上就得停摆
若在从库上备份,那么在备份期间从库不能执行主库同步过来的binlog,会导致主从延迟

但是不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的

官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候,导数据之前就会启动一个事务,来确保拿到一致性视图.而由于MVCC的支持,这个过程中数据是可以正常更新的

一致性读是好,但前提是要引擎支持这个隔离级别即可重复读的隔离级别

若引擎不支持事务,那么就需要FTWRL命令

以上可知,single-transaction方法只适用于所有的表使用事务引擎的库

除了以上说的这两个方式,其实我们知道还可以通过修改全局可读set global readonly = true

这样做的风险在于以下两个

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

业务的更新

增删改数据(DML)

修改表结构(DDL)

表级锁

MySQL里面表级的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)

表锁

语法lock tables …. read/write,可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放

该语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象

在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式

而对于InnoDB这种支持行锁的引擎,,一般不使用lock tables命令来控制并发,毕竟锁住整个表的影响还是很大

MDL(metadata lock)

MDL不需要显示使用,在访问一个表的时候会被自动加上

MDL的作用是保证读写的正确性
MySQL5.5版本引入了MDL
- 增删改查操作的时候加MDL读锁
- 在对表做结构变更操作的时候加MDL写锁

我们在读写锁中需要注意的是

读锁之间不互斥
读写锁之间和写锁之间是互斥的,用来保证变更结构操作的安全性

MDL虽然是系统默认会加的,但是还是不能忽略,比如接下来这个场景

当你给想要给一个表加字段或修改字段

比如接下来这张图

虽然有online ddl,但是我们这里讨论的时候是第一步就被锁住了

Online DDL的过程是这样的：

拿MDL写锁

降级成MDL读锁

真正做DDL

升级成MDL写锁

释放MDL锁

当sessionA启动时会启动一个MDL读锁,sessionB需要的也是MDL读锁,因此可以正常执行

在前面两个读锁没有释放的时候sessionC需要写锁就会被Blocked,同时就会影响到后面的sessionD需要的读锁

这就会造成之后的操作都会被锁住,因为所有对表的增删改查操作都需要申请MDL读锁,那么就等于这个表现在完全不可读写了

而且如果这个表上的查询语句频繁,而且客户端有重试机制,也就是说超时后会再起一个新session再请求的话,这个库的线程很快就会爆满

你可能已经发现了,事务中的MDL锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放.

那么我们怎么安全地小表加字段呢

1.首先我们需要解决长事务,事务不提交则会一直占用MDL锁,所以你可以查询当前执行中的事务,若你要做DDL变更的时候刚好有长事务在执行,你可以选择kill掉这个事务,或者暂停DDL

2.若你需要变更的是一个热点表,如果你是kill事务可能不会起作用,这时候我们可以在alter table语句里面设定等待时间,若能在该时间拿到写锁最好,若拿不到也不会阻塞后面的业务语句,先放弃

ALTER TABLE tbl_name NOWAIT add column …
ALTER TABLE tbl_name WAIT N add column …

例子

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

假设这个DDL是针对表t1的，这里我把备份过程中几个关键的语句列出来：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  /*确保RR（可重复读）隔离级别，再设置一次RR隔离级别(Q1)*/
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT； /*启动事务，这里用 WITH CONSISTENT SNAPSHOT确保这个语句执行完就可以得到一个一致性视图*/
/* other tables */
Q3:SAVEPOINT sp; /*设置保存点*/
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp; /*回滚到保存点,释放t1的MDL锁*/
/* 时刻 4 */
/* other tables */

那么会出现的情况有以下四种

如果在Q4语句执行之前到达,即还没有拿到MDL锁的时候到达,就不会有影响,备份拿到的是改变后的表结构
如果在时刻2之前到达,则表结构被改过,Q5执行的时候就build报错Table definition has changed, please retry transaction,则会mysqldump终止
若在时刻2和时刻3之间到达,Mysqldump占用这t1的MDL读锁,binlog被阻塞,则会导致主从延迟,直到Q6执行完成
若在时刻4开始,则已经释放完了MDL锁则不会有任何影响,备份拿到的是DDL前的表结构

参考

https://time.geekbang.org/column/intro/139

Next Post

C语言-循环(二)
Previous Post

MySQL-索引笔记(下)

CATALOG

1. MySQL-全局锁与表锁笔记

