MySQL锁(1)：MySQL全局锁和表锁

锁的概念

锁用于协调多个客户端对同一数据的并发访问，保证并发访问时数据的有效性和一致性。

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）表锁

表锁使用场景及分类

表锁使用场景：

1. 事务中需要对某张大表内的大部分或全部数据进行更新。此时如果使用行锁，会引发低效、冲突等情况，而使用表锁可以提升性能。
2. 事务涉及多个表，比较复杂且容易导致死锁，考虑使用表锁能够避免死锁。

表锁又分为表读锁和表写锁，对二者的使用进行说明，

表锁上锁命令

对之前构建的表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概念，即通过，

1. 拿MDL写锁
2. 降级成MDL读锁
3. 真正做DDL
4. 升级成MDL写锁
5. 释放MDL锁

具体图示如下，

该部分内容参考博文。