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的, 这里我把备份过程中几个关键的语句列出来:
1 | Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; /*确保RR(可重复读)隔离级别,再设置一次RR隔离级别(Q1)*/ |
那么会出现的情况有以下四种
- 如果在Q4语句执行之前到达,即还没有拿到MDL锁的时候到达,就不会有影响,备份拿到的是改变后的表结构
- 如果在时刻2之前到达,则表结构被改过,Q5执行的时候就build报错Table definition has changed, please retry transaction,则会mysqldump终止
- 若在时刻2和时刻3之间到达,Mysqldump占用这t1的MDL读锁,binlog被阻塞,则会导致主从延迟,直到Q6执行完成
- 若在时刻4开始,则已经释放完了MDL锁则不会有任何影响,备份拿到的是DDL前的表结构