全局锁

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

全局锁的适用场景

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

全局锁的影响:

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

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

如何加全局锁?
  1. 非innodb引擎,需要使用Flush table with read lock命令
  2. innodb引擎,可以使用mysqldump命令实现,加入一个参数 --single-transaction,在备份前开启一个事务,保证视图的一致性。
  3. (不建议使用)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正常返回。

元数据实验结果分析

现象1
当开启一个事务时,在事务中做DML操作时,就会拿到读锁,在事务未提交之前,如果有一个DDL操作,那么会阻塞,同时还会阻塞后面的所有读和写操作。

原因
获取锁有一个队列,写操作先进入队列中,并且写操作的优先级很高,如果写操作被阻塞了,后面的读和写都会被阻塞。

现象2
在读和写都被阻塞后,提交事务,看起来反倒是读先拿到锁,返回数据。

原因
mysql5.6以后,加入了onlineDDL的操作,一共有5个步骤。

  1. 申请MDL写锁
  2. 申请到后降级为读锁
  3. 真正的DDL操作
  4. 申请MDL写锁
  5. 释放锁

在SessionA的事务提交后,确实是SessionB写操作先拿到写锁,然后在第二步降级为读锁后,后面的SessionC的读操作就可以正常获取读锁,执行后返回。

  • 如果SessionC释放了读锁,SessionB的写操作在第四步的时候就可以成功
  • 如果SessionC没释放读锁,SessionB的写操作在第四步就会阻塞住

所以SessionC如果是自动提交,执行完毕后自动释放锁,SessionB也可以返回;SessionC如果使用begin手动开启事务,执行完成后,commit前都不会释放锁,SessionB也就会一直阻塞,直到SessionC执行了commit操作SessionB才会返回。