全局锁与表锁
锁的概念
锁用于协调多个客户端对同一数据的并发访问,保证并发访问时数据的有效性和一致性。
MySQL的锁分为全局锁、表锁和行锁。
数据准备
创建一个表格,对后续锁的使用演示做准备。
CREATE TABLE t (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) NOT NULL,
b int(11) NOT NULL,
PRIMARY KEY (id),
KEY idx_a (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t(a,b) values(1,1);
创建表格t,插入一行数据。
全局锁
全局锁会关闭所有打开的表并使用全局锁锁定数据库中全部表格。所有表都处于只读状态,任何数据、字段的更新都会被阻塞。
一般在数据库备份过程中会使用到全局锁,如使用mysqldump
命令。整个备份过程中,库都是只读的。不过该命令存在参数--single-transaction
,可在事务中创建一致性快照,增加该参数后在数据备份过程中可以对数据进行更新。
对库中所有表进行锁定和解锁的命令如下,
# 全局读锁锁定
flush tables with read lock;
# 全局读锁解锁
unlock tables;
全局锁实验,
session1 | session2 |
---|---|
flush tables with read lock; | |
select * from t; (正常返回结果) | select * from t; (正常返回结果) |
insert into t(a, b) values(2, 2); (报错) | insert into t(a, b) values(2, 2); (等待) |
unlock tables; | 解锁后上方insert语句执行成功 |
当全局读锁上锁后,所有表变为只读状态,数据更新或字段更新都会被阻塞。
表锁和元数据锁
1)表锁
表锁使用场景及分类
表锁使用场景:
- 事务中需要对某张大表内的大部分或全部数据进行更新。此时如果使用行锁,会引发低效、冲突等情况,而使用表锁可以提升性能。
- 事务涉及多个表,比较复杂且容易导致死锁,考虑使用表锁能够避免死锁。
表锁又分为表读锁和表写锁,对二者的使用进行说明,
表锁上锁命令
对之前构建的表t上表读锁和表写锁,
# 表读锁
lock tables t read;
# 表写锁
lock tables t write;
表读锁使用
session1 | session2 |
---|---|
lock table t read; | |
select id, a, b from t limit 1; (正常返回结果) | select id, a, b from t limit 1; (正常返回结果) |
insert into t(a, b) values(3, 4); (报错) | insert into t(a, b) values(3, 4); (阻塞) |
unlock tables; | 上方阻塞语句执行成功 |
表写锁使用
session1 | session2 |
---|---|
lock table t write; | |
select id, a, b from t limit 1; (正常返回结果) | select id, a, b from t limit 1; (阻塞) |
unlock tables; | 上方阻塞语句执行成功 |
lock table t write; | |
delete from t limit 1; (正常删除记录) | delete from t limit 1; (阻塞) |
unlock tables; | 上方阻塞语句执行成功 |
表锁使用总结
锁类型 | 当前session读 | 其余session读 | 当前session写 | 其余session写 |
---|---|---|---|---|
表读锁 | 可读 | 可读 | 不可写,报错 | 不可写,阻塞 |
表写锁 | 可读 | 不可读,阻塞 | 可写 | 不可写,阻塞 |
2)元数据锁(MDL)
元数据锁相关概念
MySQL中DDL(数据定义语言)不属于事务范畴,当DDL与事务并发时会出现事务特性被破坏、binlog顺序错乱等bug。从MySQL 5.5.3版本后引入元数据锁解决事务与DDL并行时数据不一致的问题。
概念辨析
- DML
数据操纵语言,用于查询和修改数据,如insert新增记录、update更新原有记录、delete删除原有记录和select:查询 - DDL
用于定义数据库的结构,比如创建,修改删除数据库对象,create table … 创建表、drop table… 删除表、create index…创建索引、drop index …删除索引和alter table…更改表结构,增加,删除列,修改列的数据类型,长度等;
每执行一条DML、DDL语句时都会申请MDL锁,DML操作需要MDL读锁,DDL操作需要MDL写锁(MDL加锁过程是系统自动控制,无法直接干预,读读共享,读写互斥,写写互斥)
MDL的存在可能导致长时间所等待,如果该表是查询频繁的表,很可能算时间内数据库连接数就被打满。
明确DDL操作,
操作 | 说明 |
---|---|
create database 库名; | 创建数据库 |
drop database 库名; | 删除数据库 |
show databases; | 查看MySQL下所有的库 |
desc 表名; | 查看表中的字段 |
rename table 旧表名 to 新表名; | 对已经存在的表进行重命名 |
alter table 表名 add 字段名 数据类型; | 向已存在的表中添加字段信息 |
alter table 表名 drop 被删除的字段名; | 删除指定表中的指定字段 |
alter table 表名 change 旧字段名 新字段名 新字段类型; | 对表中字段进行重命名 |
alter table 表名 engine=新引擎名; | 更改表的存储引擎 |
alter table 表名 drop foreign key 外键名; | 删除外键约束 |
元数据锁阻塞示例
MDL引发阻塞演示,假设四个语句先后执行,session A的语句执行过程需要一段时间,
为什么C等待拿锁之后,D也会阻塞?其实这里并没有解释清楚。因为如果按并发理解的话,C,D应当是同等级,都有可能拿到锁的。但C写锁与sessionA的读锁互斥,D读锁sessionA与不互斥,这样的话就跟上图所述相悖了。
申请MDL锁的操作会形成一个队列,队列中写锁获取优先级高于读锁。一旦出现写锁等待,不但当前操作会被阻塞,同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后,直到事务执行完才会将锁释放。
这样就能解释通为什么session C被阻塞后,session D也运行不了的原因了。
online DDL插队现象
结合上面的表格进行试验,实际操作过程中会出现这样的现象,
这个问题就要涉及到online DDL。由于DDL读写互斥,严重影响性能,于是MySQL推出了全新的online DDL概念,即通过,
- 拿MDL写锁
- 降级成MDL读锁
- 真正做DDL
- 升级成MDL写锁
- 释放MDL锁
具体图示如下,
该部分内容参考博文。