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 tablesunlock tables改为begincommit

MDL会直到事务提交才释放,所以在做表结构变更时,一定要小心不要导致锁住线上查询和更新。

参考资料

《高性能MySQL》
《MySQL实战45讲》 作者:丁奇

写在后面

之前学习了大神丁奇的《MySQL实战45讲》,目前在看《高性能高MySQL》,也想自己整理一下MySQL知识点,发现力不从心,也发现大神之所以是大神,那是因为真的牛。

推荐大家还是去学习丁奇的《MySQL实战45讲》,条理清晰,循序渐进,深入浅出,通俗易懂。而且每一讲后面都有高质量的留言评论, 从中能获益良多。感谢!

  • 如有 错误之处 还请多多指正。希望能给您带来帮助。

  1. MySQL基础架构 ↩︎