MYSQL 全局锁和表锁

根据加锁的范围,MySQL 中的锁可分为三类:

全局锁

表级锁

行锁

MySQL 全局锁会关闭所有打开的表,并使用全局读锁锁定所有表。其命令为:

FLUSH TABLES WITH READ LOCK;

解锁:

UNLOCK TABLES;

老规矩:准备数据

drop table if exists t14;
CREATE TABLE `t14` (
`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 t14(a,b) values(1,1);

进行实验:

MYSQL 全局锁和表锁

那么全局锁一般什么时候会用到呢?

全局锁一般用在整个库(包含非事务引擎表)做备份(mysqldump 或者 xtrabackup)时。也就是说,在整个备份过程中,整个库都是只读的,其实这样风险挺大的。如果是在主库备份,会导致业务不能修改数据;而如果是在从库备份,就会导致主从延迟。

好在 mysqldump 包含一个参数 --single-transaction,可以在一个事务中创建一致性快照,然后进行所有表的备份。因此增加这个参数的情况下,备份期间可以进行数据修改。但是需要所有表都是事务引擎表。所以这也是建议使用 InnoDB 存储引擎的原因之一。

2 表级锁(表级锁有两种:表锁和元数据锁。)

2.1表锁

表锁使用场景:

  1. 事务需要更新某张大表的大部分或全部数据。如果使用默认的行锁,不仅事务执行效率低,而且可能造成其它事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高事务执行速度;
  2. 事务涉及多个表,比较复杂,可能会引起死锁,导致大量事务回滚,可以考虑表锁避免死锁。

其中表锁又分为表读锁和表写锁,命令分别是:

表读锁:

lock tables t14 read;
预览

表写锁:

lock tables t14  write;
MYSQL 全局锁和表锁

 

 

MYSQL 全局锁和表锁

 

 2.2元数据锁

在 MySQL 中,DDL 是不属于事务范畴的。如果事务和 DDL 并行执行同一张表时,可能会出现事务特性被破坏、binlog 顺序错乱等 bug。为了解决这类问题,从 MySQL 5.5.3 开始,引入了元数据锁(Metadata Locking,简称:MDL 锁)

从上面我们知道,MDL 锁的出现解决了同一张表上事务和 DDL 并行执行时可能导致数据不一致的问题。

但是,我们在工作中,很多情况需要考虑 MDL 的存在,否则可能导致长时间锁等待甚至连接被打满的情况。

MYSQL 全局锁和表锁

 

 3.总结:

其中全局锁会让所有的表变成只读状态,所有更新操作都会被阻塞。

而表级锁分为表锁和元数据锁。

表锁又提到了表读锁和表写锁,并都进行了实验。两者的区别是:

表读锁:本线程和其它线程可以读,本线程写会报错,其它线程写会等待。

表写锁:本线程可以读写,其它线程读写都会阻塞。

 

 

 

 

MYSQL 全局锁和表锁

上一篇:SQL注入


下一篇:sql优化之执行计划 explain