mysql表锁了怎么办 mysql的表锁

转载

mob64ca1417eedd 2023-08-08 11:17:31

文章标签 mysql表锁了怎么办读锁元数据表名 文章分类 MySQL 数据库

全局锁与表锁

锁的概念
数据准备
全局锁
表锁和元数据锁

1）表锁

表锁使用场景及分类
表锁上锁命令
表读锁使用
表写锁使用
表锁使用总结

2）元数据锁(MDL)

元数据锁相关概念
元数据锁阻塞示例
online DDL插队现象

锁的概念

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

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 外键名;	删除外键约束