如果要给一个大表加字段,你一般都会非常谨慎小心,以免对线上业务造成影响,但实际上给一个小表加字段不慎操作也会导致线上业务出问题,这篇文章主要学习一下MySQL
中MDL
锁对性能的影响,防止采坑。
Flush tables with read lock (FTWRL)
FTWRL
确保不会有其他线程对数据库做更新,然后对整个库做备份binlog
,会导致主从延迟mysqldump
mysqldump
使用single-transaction
参数,导数据之前就会启动一个事务,拿到一致性视图MVCC
的支持,这个过程中数据是可以正常更新的lock tables … read/write
A
中执行lock tables t1 read, t2 write
这个语句,则其他线程写t1
、读写t2
的语句都会被阻塞A
在执行unlock tables
之前,也只能执行读t1
、读写t2
的操作,不能访问其他表InnoDB
这种支持行锁的引擎,一般不使用lock tables
命令来控制并发unlock tables
主动释放锁,也可以在客户端断开的时候自动释放MDL
锁,其作用是保证读写的正确性MDL
读锁,读锁之间不互斥,可以有多个线程同时对一张表增删改查MDL
写锁,读写锁之间、写锁之间是互斥的,用来保证变更表结构操作的安全性现象:给一个小表加个字段,导致整个库挂了
给一个表加字段,或者修改字段,或者加索引,需要扫描全表的数据,一般在对大表操作的时候,会特别小心,以免对线上服务造成影响,实际上,即使是小表,操作不慎也会出问题
session A
先启动,这时候会对表t
加一个MDL
读锁
session B
需要的也是MDL
读锁,因此可以正常执行
session A
的MDL
读锁还没有释放,session C
需要MDL
写锁,因此只能被阻塞
之后所有要在表t
上新申请MDL
读锁的请求也会被session C
阻塞,所有对表的增删改查操作都需要先申请MDL
读锁,这个表此时完全不可读写
MDL
锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放MDL
锁,如果要做DDL
变更的表刚好有长事务在执行,要考虑先暂停DDL
,或者kill
掉这个长事务MDL
写锁最好,拿不到也不要阻塞后面的业务语句,先放弃MariaDB
已经合并了AliSQL
的这个功能,所以这两个开源分支目前都支持DDL NOWAIT/WAIT n
这个语法ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
当备库用single-transaction
做逻辑备份的时候,如果从主库的binlog
传来一个DDL
语句会怎么样?
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION 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;
/* 时刻 4 */
/* other tables */
Tips:
Q1
表示为了确保RR
(可重复读)隔离级别,设置RR
隔离级别(Q1)
,Q2
表示WITH CONSISTENT SNAPSHOT
这个语句执行完就可以得到一个一致性视图(Q2)
,Q3
表示设置一个保存点,Q4
表示show create
是为了拿到表结构,Q5
表示正式导数据,Q6
表示回滚到SAVEPOINT sp
,作用是释放t1
的MDL
锁。
DDL
命令在Q4
之前到达从库,没有影响DDL
在时刻2
到达从库,此时会有MDL写锁
,会造成Q5
执行失败,备份过程被终止DDL
在时刻3
到达从库,由于备份过程占用读锁,会导致binlog
复制被阻塞,会造成主从延迟,直到备份完成Q6
执行之后从库才能继续复制主库binlog
DDL
在时刻4
到达从库,没有影响