mysql全局读写怎么锁_MySQL 学习笔记 ----- 全局锁和表锁

川长思鸟来 2023-01-11 01:25 156阅读 0赞

全局锁

全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)，之后其它线程的所有语句都会被阻塞。全局锁很少使用，因为它要对整个库加锁，典型使用场景是，做全库逻辑备份。

那么最直观的全库备份的做法，就是直接执行 FTWRL，使库处于只读状态，然后进行备份，这样就有两个缺点：

1、如果对主库备份，期间都不能对主库执行增删改操作，业务几乎处于暂停状态。

2、如果对从库备份，从库不能接受主库传过来的 binlong，导致主从延迟高。

其实我们可以让数据库在执行备份的时候，同时又可以对其进行增删改操作。但是这样有一个问题，假如银行系统要维护，需要将数据库备份，假设备份期间，有一个用户 A 用系统给用户 B 转账了 3000 元，如果时间顺序上是先备份 A 的余额，然后用户 A 先转钱，B 收到钱，再备份 B 的余额，那么当用备份表恢复数据时，A 用户的余额没有减少！用户 B 白白多了 3000 块！

这样银行可就亏大发了，所以显然是不行的。所以官方自带的逻辑备份工具 mysqldump，当 mysqldump 使用参数--single-transaction 的时候，会启动一个事务，确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。参数--single-transaction 导数据之前就会启动一个事务(隔离级别为可重复读)，来确保拿到一致性视图。

解释：因为备份库，就是把库中数据都查一遍，所以在查询前，开启事务，保证查询的数据都是在一个视图里面，这就是一致性视图，主要应用了 MVCC 机制，查询事务开启后会生成一个事务 id，之后查询的数据行的事务 id，都要小于等于这个事务 id，保证数据的一致性。所以备份期间的增删改的数据，不会对备份造成影响。(备份期间的增删改的数据不会在备份的库中)。

业务的更新不只是增删改数据(DML)，还有可能是加字段等修改表结构的操作(DDL)。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁(meta data lock，MDL)。

\*\*表锁的语法是 lock tables … read/write。\*\*与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

另一类表级的锁是 MDL(metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。这意味着，多个线程可以对同一张表进行增删改查。如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

所以在更改表结构时要格外小心，如果是一个热门表，更改表结构要加 MDL 写锁，这时对热门表的增删改查就要阻塞，，这个库的线程很快就会爆满，对用户体验也不好。而且一旦完成更改，释放掉 MDL 写锁，阻塞的线程同时运行，也容易造成行锁死锁。