根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。 这篇文章主要来讨论下全局锁和表级锁。
全局锁
- 对
整个数据库实例
加锁。 - MySQL 提供加全局读锁的方法:
Flush tables with read lock(FTWRL)
。这个命令可以使整个库处于只读状态。使用该命令之后,数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。 - 使用场景:
全库逻辑备份
。 - 风险:
- 如果在主库备份,在
备份期间不能更新
,业务停摆 - 如果在从库备份,备份期间不能执行主库同步的 binlog,导致
主从延迟
- 如果在主库备份,在
- 官方自带的逻辑备份工具 mysqldump,当 mysqldump 使用参数
--single-transaction
的时候,会启动一个事务,确保拿到一致性视图
。而由于 MVCC 的支持,这个过程中数据是可以正常更新的。一致性读是好,但是前提是引擎要支持这个隔离级别
。 - 如果要全库只读,为什么不使用
set global readonly=true
的方式?- 在有些系统中,readonly 的值会被用来做
其他逻辑
,比如判断主备库。所以修改 global 变量的方式影响太大。 - 在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开,那么 MySQL 会自动释放这个全局锁,整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后,如果客户端发生异常,则数据库就会一直保持 readonly 状态,这样会导致整个库长时间处于
不可写状态
,风险较高。 - 全库只读 readonly = true 还有个情况在 slave 上如果用户有
超级权限
的话 readonly 是失效的。
- 在有些系统中,readonly 的值会被用来做
表级锁
- MySQL里面表级锁有两种,一种是
表锁
,一种是元数据锁
(meta data lock,MDL)。 - 表锁的语法是:
lock tables ... read/write
。- 可以用
unlock tables
主动释放锁,也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。 - 表锁
一般是在数据库引擎不支持行锁的时候才会被用到
,对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大
。
- 可以用
- 元数据锁(MDL):不需要显式使用,在访问一个表的时候会被
自动加上
。- MDL 的作用:
保证读写的正确性
。 - 在对一个表做
增删改查
操作的时候,加元数据(MDL)读锁。当要对表做结构变更操作
的时候,加元数据(MDL)写锁。 - 读锁之间不互斥。读写锁之间,写锁之间是互斥的,用来保证变更表结构操作的安全性。
- MDL 会直到
事务提交才会释放
,在做表结构变更的时候(特别是大表的时候),一定要小心不要导致锁住线上查询和更新。
- MDL 的作用:
笔记来源于《极客时间:MySQL实战45讲:全局锁和表锁 :给表加个字段怎么有这么多阻碍?》