MySQL 全局锁和表锁
今天我们来聊一聊 MySQL 中的锁。
在 MySQL 中,锁是一种机制,用于控制对数据库资源(如表或行)的并发访问,确保数据的一致性和完整性。它的作用主要有保证数据的一致性、防止并发冲突等。
MySQL 中的锁按照粒度的大小,可以分为全局锁、表锁和行锁。在这篇文章中,我们就先来学习一下全局锁和表级锁。
全局锁
顾名思义,全局锁会锁住整个数据库的所有表,阻止其他线程对数据库进行任何操作,从而实现对整个数据库的保护。可以将其看作是对整个数据库加上了“只读”的限制。
全局锁常通过以下语句来实现:(方便起见,我们简称它为 FTWRL)
FLUSH TABLES WITH READ LOCK;
这个语句会使数据库进入一种只读模式,其他线程无法对数据库中的表进行写操作,但仍可以进行读取操作。
全局锁的一个典型应用场景是做全库的逻辑备份。
逻辑备份是指将整个数据库中的数据以 SQL 语句的形式导出到文本文件中。由于备份过程可能持续较长时间,如果没有采取任何保护措施,在备份过程中其他事务可能会修改数据,导致备份数据不一致。因此,这个场景下就需要用到全局锁。
在做全库的逻辑备份时,流程一般是这样的:
- 加锁,执行 FTWRL 命令。
- 备份数据,使用工具(比如 mysqldump )将数据导出。
- 备份完成后,释放全局锁。
既然,全局锁会锁住整个数据库中的所有表。所以在主从架构下,使用全局锁也会有明显的缺点:
- 在主库上进行备份:全局锁会阻止从库上的所有写操作,从库也就无法处理更新事务,这会导致业务受到巨大影响。
- 在从库上进行备份:主库仍在进行写操作时,不断向从库同步 binlog。如果从库未完全应用主库的 binlog,备份时从库的数据可能会滞后于主库。这时就会导致数据不一致。
那么,既然全局锁的缺点这么明显,我们为什么还要用全局锁呢?如果不用全局锁,可以吗?下面我们就来看看在不加锁的情况下会出现的问题。
假如我们要维护一个订单系统,这个系统中有两张表:账户余额表(account)和订单表(order)。账户余额表记录了用户的账户余额,订单表记录了用户的订单信息。
我们假定,在账户余额表中,用户 A 的余额是 100 元,订单表中没有任何的记录。
现在我们要对数据库做逻辑备份,在备份期间发生了这些事:
- 用户 A 下了一笔订单,金额为 50 元。
- 系统扣除账户余额 50 元,并在订单表中插入一条新订单记录。
我们按照时间顺序来设定一个备份顺序:
- 首先,开始备份 account 表;
- 然后,用户 A 下单,余额扣减,订单表插入新订单;
- 最后,开始备份 order 表。
我们用一个流程图来说明数据库中的状态和备份的状态:
可以看到的是,在最后的备份结果里,用户 A 的余额并没有减少,但是订单中却多了一行数据。也就是说,用户买了东西但是没有花钱。作为商家来说,这是不能接受的;但是如果调换一下备份顺序,那么结果就是相反的,也余额减少了,但是没买到东西。作为消费者,这也是无法接受的。
通过这个场景,我们能够得出一个结论:如果备份的时候不加锁,那么备份得到的最终数据状态和数据库中的最终数据状态是不一致的,也就是视图是不一致的。
在讲解事务的文章中,我们学习过,在可重复读隔离级别下,我们可以得到一个一致性视图。
我们上文中提到过,在主从架构下,使用全局锁的缺点是很明显的。其实,这里有一个解决方法,那就是 使用这个参数:–single-transaction。 它会使用事务隔离机制(MVCC)生成一致性快照,从而来避免全局锁的使用。
说到这,你是不是觉得问题已经解决了?那使用逻辑备份的时候就用这个参数就好了呀,也不用加全局锁了。但是,事实是,这种方法只适用于 InnoDB 存储引擎。比如 MyISAM 就不能用,那么这种情况下,就只能使用 FTWRL 命令来加全局锁了。
还有一种情况,学数据库学的比较好的同学可能知道。想让整个库处于只读状态,可以用这个命令:
SET GLOBAL read_only = 1;
但是,这个命令也是有缺点的:
- 首先,它对超级用户无效。也就是说,即使设置了 read_only = 1,MySQL 的超级用户(具有 SUPER 或 SESSION_ADMIN 权限的用户)仍然可以绕过该限制进行写操作。
- 其次,如果备份的过程出现了异常,那整个数据库就会一直处于只读的状态。但是如果使用 FTWRL 命令,如果客户端出现异常断开,数据库就会恢复到正常的状态。
所以,综合考虑,我们在进行逻辑备份的时候,还是使用 FTWRL 命令。
好了,全局锁的内容我们就先讲到这里。在这一章,我们学习了全局锁的概念,以及使用场景。同时,结合一个场景我们分析了如果不使用全局锁会出现什么问题。
表级锁
在 MySQL 中,表级锁可以分为两种:表锁和元数据锁(MDL)。
表锁
表锁是用来保护表级资源的锁机制,主要用于 MyISAM、MEMORY 等存储引擎,InnoDB 更常用的是行锁,但也支持表锁。
表锁可以分为读锁和写锁。
读锁是共享锁,“共享”的意思是,当一个线程对表加了读锁时,其他线程也可以同时对该表加读锁并执行读取操作。它们 共享读取的权限,不会互相影响。 但是读锁会阻止任何线程对该表执行写操作。
比如我想给一张表加读锁:
LOCK TABLES my_table READ;
写锁是排他锁,“排他”的意思是写锁会阻塞其他的读锁和写锁。只有获取写锁的线程可以对表进行操作,其他线程对该表的所有读写操作都会被阻塞。
加写锁:
LOCK TABLES my_table WRITE;
有一点需要注意,表锁除了会限制别的线程的读写外,也会限制本线程接下来的读写操作。如果我在线程 A 中执行了这个语句:
lock tables t1 read, t2 write;
也就是,我给 t1 加了读锁,给 t2 加了写锁。
那么其他的线程就只能读 t1 ,不能写 t1 ,不能读 t2 也不能写 t2 。并且在解锁之前,线程 A 只能读 t1,读写 t2,而且不能写 t1 ,而且不能访问其他的表了。
总的来说,表锁的粒度较粗,通常只适合低并发或简单场景。所以在 InnoDB 中,一般使用粒度更小的行锁。
元数据锁
还有另外一种表级锁,叫做元数据锁(MDL)。
元数据锁不是用户显式加的锁,也就是说,当我们对数据库中的某个表进行操作时,这个锁就会被自动加上。它的主要作用是协调多个线程对表或其他数据库对象的访问和修改,防止数据定义语言(DDL,比如 ALTER TABLE )和数据操作语言(DML,比如 SELECT 、INSERT)之间的冲突。
同样的,元数据锁也分为读锁和写锁。
- 读锁是共享锁,当执行查询操作时,比如 SELECT , INSERT ,MySQL 会对表加一个共享的元数据锁。共享锁不会阻塞其他的共享锁,但会阻塞排他锁,比如 DROP,ALTER。
- 写锁是排他锁,当执行一些 DDL 操作时,比如 ALTER TABLE、DROP TABLE,MySQL 就会对表加一个排他锁。排他锁会阻塞其他任何对表的访问,包括共享锁和排他锁。
下面我们来看几个典型的场景,你会对它有一个更深入的理解。
第一个场景:防止查询和 DDL 的冲突
线程 1 开始执行查询:
SELECT * FROM my_table;
MySQL 会对 my_table 加一个共享元数据锁。
线程 2 尝试修改表结构:
ALTER TABLE my_table ADD COLUMN new_col INT;
结果:线程 2 会被阻塞,直到线程 1 释放共享元数据锁。
第二个场景:防止 DDL 和 DDL 的冲突
线程 1 给表增加列:
ALTER TABLE my_table ADD COLUMN new_col INT;
线程 2 同时尝试删除表:
DROP TABLE my_table;
结果:线程 2 会被阻塞,直到线程 1 的操作完成。
虽然元数据锁是 MySQL 自动加的,但是我们在具体的业务场景中是万万不能忽略这个机制的。我会举一个例子来向你说明。
假设数据库中存在一张小表 small_table :
CREATE TABLE small_table (
id INT PRIMARY KEY,
name VARCHAR(100)
);
有某些业务查询正在操作这张表,并且使用了事务
BEGIN;
SELECT * FROM small_table WHERE id = 1;
-- 事务还没有提交,导致元数据锁没有释放
但事务未提交,会导致这张表的共享元数据锁被长时间持有。
此时在另一线程中,某个开发人员试图给 small_table 添加一个字段:
ALTER TABLE small_table ADD COLUMN age INT;
这时问题就出现了。
ALTER TABLE 操作需要获取一个排他元数据锁,但由于长事务还持有共享元数据锁,导致 ALTER TABLE 这个操作被阻塞。而 MySQL 的 MDL 锁特性是,DDL 操作会阻塞后续所有对该表的其他操作,包括 DML 操作,比如SELECT、INSERT、UPDATE 等。
因此,其他线程试图查询或更新 small_table 时,也会因为无法获取共享锁而被阻塞,导致这些操作排队等待。
最后的结果就是,如果应用中后续存在大量并发查询或写操作,这些操作都被阻塞在队列中,连接数逐渐增加。
数据库连接池可能耗尽,最终导致整个数据库性能下降甚至挂掉!
那么基于上面这个可怕的场景,我们再来分析一个问题:如何安全地给小表加字段?
其实上面这个问题的根源,就在于 长事务 ,因为长事务一直没有提交,所以一直占用着 MDL 锁。所以,我们可以定期检查没有提交的长事务,使用 SHOW PROCESSLIST 命令,找到之后直接 kill 掉就好了。这种方式比较粗暴。
如果你要变更的表是一个热点表,虽然数据量不大,但是上面的请求很频繁,而你不得不加个字段,你该怎么做呢?
这时候 kill 就不管用了,因为你刚杀掉一个线程,新的线程就又来了,你杀不完的。在这种情况下,我们可以在 alter table 中加一个等待时间,如果能在设定的等待时间内拿到写锁是最好的。但是如果过了设定的时间,拿不到就放弃,以防越来越多的线程阻塞,从而影响后续的请求。
在这一章,我们讲了表锁和元数据锁,它们都可以分为读锁和写锁。表锁是主动加的,而元数据锁是自动加的。我们还分析了关于元数据锁的一个可怕的场景,而且我们给出了解决思路。
总结
这篇文章,我们主要讲了全局锁和表级锁这两个概念,表级锁又分为表锁和元数据锁。同时呢,我们也讲了它们的一些使用方法和经典的场景。好啦,谢谢你看到这,相信你看完这篇文章之后,一定会对 MySQL 中的锁有了一个更深的理解。