MySQL学习之全局锁和表锁

图片

排版会有些问题, 点击阅读原文链接

数据库锁设计的初衷是为了处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源访问的规则,锁就是用来实现这些访问规则的重要数据结构。

在MySQL里锁分为三类,全局锁,表级锁,行锁。

全局锁

全局锁就是对整个数据库实例加锁,MySQL提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL),使用了全局锁,会让整个库处于只读状态。

我们只要一听到整个库加锁,就感觉很危险。为什么这样讲呢?

1、如果在主库上进行备份的话,备份期间更新操作是无法执行的,业务基本上就处于停摆状态;

2、如果在从库上进行备份,备份期间从库不能执行主库同步过来的binlog,会导致主从延迟。

这时你可能会想,既然这么不好,为什么还要使用全局锁呢?

全局锁的典型使用场景是用来做全库备份使用。设想一下,不加全局锁会有什么问题?

比如:用户买东西,首先会从余额里扣除金额,然后在订单里添加商品。如果备份数据库,不加锁,并且备份顺序为先备份用余额,再备份订单商品,有可能备份了用户余额后,用户下订单买东西提交事务,然后再备份订单商品表, 此时订单商品已存在。最后备份出来的数据为。最后用户余额为买东西前的余额,没有减少,但是订单商品却多了。

也就是说,如果不加锁,不同表之间执行顺序不同,进而导致备份时间不同,此时备份系统可能会得到不是一个时间的点的数据,视图是不一致的。

看到这里,是不是会想到一个机制,我们让视图一致不就好了吗?是的,说的没错。保证视图一致的事务隔离级别是什么?可重复读。但是不要忘记了,不是所有的引擎都是支持事物的,所以也就是说,不支持事务的引擎没有办法使用可重复读隔离级别,来保证一致性读。

官方自带的逻辑备份工具是 MySQLdump, 当mysqldump 使用参数** –single-transaction 时候,会启用一个事务,来确保拿到一致性视图。**

思考

既然是全库只读,为什么不使用set global readonly=true的方式?

1、在有些系统中,readonly是被用作其他逻辑使用的, 比如判断一个库是否为主库还是备库, 修改 global 变量方式影响太大。

2、异常处理机制上有差异.如果FTWRL命令执行之后客户端发生异常断开, MySQL会自动释放这个全局锁, 整个库是可以正常更新的状态。但是如果设置了readonly,即使发生异常,数据库会一直保持只读状态,长时间处于不可写的状态,风险极大

表级锁

MySQL中,表级锁有两种,一种是表锁,一种是元数据锁。

表锁

表锁的语法是 lock tables 表名 read、write,可以使用unlock tables 进行主动释放锁,也可以在客户端断开时自动释放。

lock tables 语法除了会限制别的线程读写外,也会限制本线程的操作对象。

例子:

    在某个线程A 中执行 lock tables t1 read, t2 write;这个语句,其他线程写t1、读写t2的语句都会被阻塞。同时,线程A在执行unlock tables 之前,也只能执行读 t1, 读写t2的操作。写t1都是不允许的。

可以理解为写是排它锁,写锁意味着其他线程不能读也不能写。读锁是共享锁,加上后其他锁只能读,不能写,本线程也不能写。

2

元数据锁(metadata lock, MDL)

MDL 不需要显式使用, 在访问一个表时会自动加上。MDL的作用是,保证读写的正确性。

如果一个查询正在遍历一个表中的数据,而执行期间另一个线程对这个表结构做变更,删了一列,那么查询线程拿到的结果跟表结构对不上,肯定是不行的。

MDL 是server层的表级锁,也是表结构锁,主要是用于隔离 DML和DDL操作之间的干扰。对一个表进行正删改查操作的时候,加MDL读锁;对一个表做表结构变更的时候,加MDL写锁。(MDL加锁过程是系统自动控制,无法直接干预,读读共享,读写互斥,写写互斥)

  • 读锁之间不互斥,可以有多个线程同时对一张表进行增删改查

  • 读写锁之间,写锁之间是互斥的,用来保证变更表结构操作的安全性。如果有两个线程同时给一个表加字段,其中一个要等另一个完成后才能执行

思考

这里我会产生一个疑问,为什么读锁之间不互斥,其他线程还可以进行增删改查?

 因为在对一个表进行增删改查的时候,系统会自动加上一个MDL读锁,这个读锁是表结构的读锁,增删改查并不会改变表结构,读锁自然会不会进行互斥,多个线程可以同时进行增删改查操作;这个时候如果加了读锁,其它线程中有需要改变表结构的,这时改表结构的线程会加上一个MDL写锁,现在读锁和写锁就会进行互斥,所以读锁加了之后,写锁是需要等待的,同理,加了写锁,读锁也需要等待的,其他的写锁也是需要等待的。

为什么我给一个小表加个字段,导致整个库挂掉了?

 我们知道,给一个表加字段,修改索引,添加索引,都会进行全表扫描。举个例子:

图片

(事务 示意图)

  • session A 启动,这时会对表 t 加上一个 MDL 读锁

  • session B 进行查询,这时也会对表 t 加上一个 MDL 读锁,读锁与读锁之间并不互斥,因此可以正常执行。

  • session C 进行表结构修改,此时会对表** t 加上一个 MDL 写锁**,这时session A 的 MDL 读锁还未释放,写锁和读锁同时存在,造成互斥,目前只有等待 读锁释放,所以需要等待。

  • 这时session D 进行查询,申请MDL读锁,这时候也会被阻塞,处于等待状态。

    所有对表操作增删改查都需要申请MDL读锁,就都被锁住,此时的表完全不可读写了。

这里为什么C等待拿锁之后,D也会被阻塞呢?如果按照并发理解的话,C,D应该是同一等级,都有可能拿到锁,但C读写锁互斥,D为读读锁应该是共享的呀,并不互斥啊?

** 因为MDL锁在申请时会形成一个队列,队列中 写锁获取优先级高于读锁。一但写锁出现等待,不但当前事务会造成阻塞,同时还会阻塞后续该表的所有操作。**

** 事务一旦申请到MDL锁后,一直等到事务结束后才会进行释放锁。**

如何安全的给小表加字段

事务一旦申请到MDL锁后,在语句执行开始申请,语句结束并不会释放锁,**一直等到事务结束后才会进行释放锁。**

首先要解决长事务,事务不提交,就会一直站着MDL锁。如果要做DDL变更的表刚好有长事务在执行,要考虑暂停DDL,或者kill掉这个长事务。

思考

要变更的表是热点表,数据不大,但请求频繁,现在不得不加字段,该怎么办?

 这时候kill掉长事务未必可用了,因为请求很频繁。在 alter table 语句里面设置等待时间,如果在这个指定等待时间拿到 MDL 写锁最好, 拿不到就先放弃,之后等DBA重试命令重复这个过程。

** MariaDB10.3 增补AliSQL补丁-DDL FAST FAIL,让其DDL操作快速失败。**

ALTER TABLE tbl_name NOWAIT add column ...

ALTER TABLE tbl_name WAIT N add column ...
本作品采用《CC 协议》,转载必须注明作者和本文链接
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 3

如果对你有帮助, 点赞关注一波吧 :pray: :pray:

2周前 评论

继续加油

5天前 评论
thinkabel (楼主) 5天前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!