MySQL数据库学习- 5 | 全局锁和表锁:给表加个字段怎么有这么多阻碍?
- 锁的类型
- 全局锁
- 表级锁
- 总结
- 参考资料
- 写在后面
锁的类型
环境: MySQL 5.7.24, for linux-glibc2.12 (x86_64)
数据库锁设计的初衷是 处理并发 问题。
- 作为多用户共享的资源,当出现并发访问时,数据库需要合理的控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。
- 根据加锁的范围, MySQL 里的锁大致可以分成全局锁、表级锁和行锁三类。
全局锁
全局锁就是对整个数据库实例加锁。
MySQL 的FTWRL是在Server层1实现的。
加全局锁的命令(FTWRL)是
-- FTWRL
mysql> flush tables with read lock;
- 全局锁让整个数据库实例处于只读状态,之后其他线程的数据库更新语句(DML 增、删、改)、数据定义语句(DDL 建表、修改表结构等)、更新类事务提交语句都会被阻塞。
- 典型使用场景:做全库逻辑备份。即把整库每个表都select出来存成文本。
- 注意1:
MySQL官方自带的逻辑备份工具是mysqldump
,当mysqldump
使用参数-single-transaction
时,导数据之前就会启动一个事务,确保拿到一致性视图。由于MVCC(多版本并发控制)的支持,这个过程中的数据是可以正常更新的。既然如此,那为什么还需要FTWRL呢?
因为一致性读是好,但是前提是引擎要支持这个隔离级别。例如MyISAM引擎就不支持事务,就需要使用FTWRL命令了。 - 注意2:
为什么不使用set global readonly=true
这种方式?确实readonly方式也可以让全库进入只读状态,但还是建议使用FTWRL方式,主要有2个原因:
一是,有些系统中,readonly的值会被用来做其他逻辑。比如判断一个库是主库还是备库。因此修改global变量的方式影响面更大。
二是,在异常处理机制上有差异。执行FTWRL命令之后,由于客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。而如果修改global将整个库设置为readonly之后,如果客户端发生异常,则数据库就会一直保持readonly状态,这样会导致整个库长时间处于不可写状态,风险较高。
表级锁
MySQL里表级别的索有两种:表锁、元数据锁(meta data lock, MDL)。
表锁
mysql> lock tables tab_name read/write;
-- unlock tables tab_name ;
- 与FTWRL类似,可以用
unlock tables
主动释放表锁,也可以在客户端断开的时候自动释放。需要注意,lock tables
语法除了会限制别的线程的读写外,也限定了本线程接下来的操作对象。 - 一般不使用
lock tables
命令来控制并发,毕竟锁住整个表的影响面还是太大。
元数据锁(meta data lock, MDL)
- MDL作用是保证读写的正确性,MySQL5.5版本引入。MDL不需要显式使用,在访问一个表的时候会被自动加上。
- 当对一个表做增删改查操作时,加MDL读锁。
- 当需要对表做结构变更操作时,加MDL写锁。
- 读锁直接不互斥。因此可以有多个线程同时对一张表增删改查。
- 读写锁直接、写锁之间是互斥的,用来保证变更表结构操作的安全性。因此,如果2个线程同时给一个表加字段,其中一个要等另外一个执行完成才能开始执行。
-- 在information_schema 库的 innodb_trx
-- 查询超过60s的事务
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;
- 注意:如何安全的给小表加字段?
- 要解决长事务。事务不提交,就会一直占着MDL锁。
- 如果需要做DDL变更,正好有长事务在执行,要考虑先暂停DDL,或者
kill
掉这个长事务。 - 如果变更的表是热点表,虽然数据量不大,但是表上面的请求很频繁,而又不得不加个字段。这时
kill
可能未必管用,因为新的请求马上就来。比较理想的机制是在alter table
语句里设定等待时间。如果在这个指定的等待时间里能够拿到MDL写锁最好,即使拿不到也不要阻塞后面的业务查询语句,先放弃。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
总结
全局锁主要用在逻辑备份过程中。对于是InnoDB引擎的库,建议选择使用
-single-transaction
参数,对应用更友好。表锁一般是在不支持行锁的时候才被用到。如果在应用程序中有
lock tables
语句,要么书库还在用不支持事务的引擎,建议升级更换为支持事务的引擎;要么引擎升级了,代码还没升级,建议把lock tables
和unlock tables
改为begin
和commit
。MDL会直到事务提交才释放,所以在做表结构变更时,一定要小心不要导致锁住线上查询和更新。
参考资料
《高性能MySQL》
《MySQL实战45讲》 作者:丁奇
写在后面
之前学习了大神丁奇的《MySQL实战45讲》,目前在看《高性能高MySQL》,也想自己整理一下MySQL知识点,发现力不从心,也发现大神之所以是大神,那是因为真的牛。
推荐大家还是去学习丁奇的《MySQL实战45讲》,条理清晰,循序渐进,深入浅出,通俗易懂。而且每一讲后面都有高质量的留言评论, 从中能获益良多。感谢!
- 如有 错误之处 还请多多指正。希望能给您带来帮助。
- MySQL基础架构 ↩︎