看完MySQL全局锁和表锁,你废了吗?

根据加锁粒度,MySQL的锁:

  • 全局锁
  • 表级锁
  • 行锁

全局锁和表锁都实现在Server层。

1 全局锁

对整个DB实例加锁。

1.1 加全局的读锁

执行命令:

Flush tables with read lock (FTWRL)

让整个库只读,之后其他线程的如下语句会被阻塞:

  • 数据更新语句(数据的增删改)
  • 数据定义语句(建表、修改表结构等)
  • 更新类事务的提交语句

若FTWRL前有读写,FTWRL都会等待读写执行完毕后再执行。

FTWRL执行时,要刷脏页数据到磁盘,因为要保持数据一致性,所以执行FTWRL的时机是所有事务都提交完毕后。

1.2 适用场景 - 全库逻辑备份

把整库的每个表都select出来存成文本。历史有做法通过FTWRL确保不会有其他线程对DB更新,然后对整库备份。

备份过程中,整库都只读。但让整库只读:

  • 若你在主库备份,则备份期间,主库都不能再执行更新,业务直接停摆
  • 若你在从库备份,则备份期间,从库不能执行主库同步过来的binlog,主从延迟加剧

看来加全局锁不太好。不妨反思:

1.3 备份为何要加锁?

即不加锁会导致什么问题?假设现在维护购课系统,关注用户的:

  • 账户余额表(u_account)
  • 用户课程表(u_course)

现在发起一个逻辑备份。假设备份期间,有一用户,他购买了一课程,业务逻辑里就要扣掉他的余额,然后往已购课程里加上一本书。

若时间顺序:

  • 先备份 u_account
  • 然后用户购买
  • 然后备份 u_course

用户A的数据状态就是u_account没扣,但u_course里多了一门课。若后面用这个备份恢复数据,用户A就发现自己血赚!

不加锁时,备份系统备份得到的库不是同一逻辑时间点,这视图是逻辑不一致的。

1.4 mysqldump

有个办法能拿到一致性视图:在RR下开启一个事务。

官方的逻辑备份工具mysqldump。当mysqldump使用参数–single-transaction,导数据前会启动一个事务,确保得到一致性视图。 由于MVCC,该过程中数据依旧能正常更新。

若mysqldump备份的是整个schema,某个小表t1只是该schema上其中有一张表

① 情况1

master对小表t1的DDL传输到slave去应用时,mysqldump已备份完t1表数据,此时slave同步正常,没问题

② 情况2

master对小表t1的DDL传输到slave去应用时,mysqldump正在备份t1表数据,此时会发生MDL锁,从库上t1表的所有操作都会Hang住

③ 情况3

master对小表t1的DDL传输到slave去应用时,mysqldump还没对t1表进行备份,该DDL会在slave的t1表应用成功,但当导出到t1表时,会报“ERROR 1412 (HY000): Table definition has changed, please retry transaction” 错误,导致导出失败!

1.5 有这功能,何需FTWRL?

一致性读是好,那但也得引擎支持这隔离级别。 MyISAM不支持事务,备份过程中若有更新,只能取到最新数据,破坏了备份的一致性,就需要使用FTWRL。

所以single-transaction只适于所有的表使用支持事务引擎的库。一旦有表使用了不支持事务的引擎,则备份只能通过FTWRL。

这也是使用InnoDB替代MyISAM的主因之一。

1.6 那readonly不就够了?

既然要全库只读,何不使用:

set global readonly=true

readonly可让全库只读,但还是推荐FTWRL:

1.6.1 影响面太大

有些系统的readonly值会被用来做其他逻辑,比如判断一个库是主库or备库。因此,修改global变量的方式影响面太大!

1.6.2 异常处理差异

执行FTWRL后,由于客户端异常断开,MySQL会自动释放该全局锁,整库回到可正常更新的状态。

而将整库设为readonly后,若客户端异常,则数据库就一直保持readonly,导致整库长时间不可写。

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

即使没有被全局锁锁住,加字段也不是一帆风顺,还会碰到表级锁。

2 表级锁

表级锁有两种:

2.1 表锁

2.1.1 语法

lock tables … read/write

类似FTWRL:

  • 可用unlock tables主动释放锁
  • 也可在客户端断开时自动释放

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

表级别的write锁,对本线程可读、写。 若在线程A执行:

lock tables t1 read, t2 write;

则其他线程写t1、读写t2的语句都会被阻塞。

线程A在执行unlock tables前,也只能执行读t1、读写t2的操作。写t1不允许,也不能访问其它表。

对InnoDB这支持行锁引擎,一般不推荐使用lock tables命令控制并发,毕竟锁的粒度过大。

2.2 元数据锁(meta data lock,MDL)

访问一个表时会被【自动加上】,以保证读写的正确性。

若一个查询正在遍历一个表数据,执行期间另一个线程在变更该表结构,删了一列,则查询线程拿到的结果就跟表结构对不上了,这肯定不行啊!

2.2.1 加锁规则

于是MySQL 5.5引入MDL:

  • 对一个表做CRUD(DML),加MDL读锁
  • 对表做结构变更操作(DDL),加MDL写锁

2.2.2 互斥规则

读锁之间不互斥,因此可多线程同时对一张表CRUD。

读、写锁之间,写锁之间互斥,以保证变更表结构操作的安全性。

所以MDL是为防止DDL和DML的并发冲突,而非解决select和update间的并发。

虽然MDL锁默认会加,但也不能轻视。比如给一个表加字段或修改字段或加索引,需扫描全表数据。即使是个小表,操作不慎也有问题。

2.2.3 案例

假设表t是个小表。

MySQL 5.6。

S1

S2

S3

S4

begin
select * from t limit 1

select * from t limit 1

alter table t add f int;
(blocked)

select * from t limit 1;
(blocked)

  • S1先启动,对表t加个MDL读锁
  • S2加的也是MDL读锁,可正常执行
  • S3会被阻塞,因S1的MDL读锁还没释放,而S3需MDL写锁

若只有S3被阻塞还没啥,但之后所有要在表t上新申请MDL读锁的请求也会被S3阻塞。 所有对表的CRUD操作都要先申请MDL读锁,就都被锁住,等于该表此时完全不可读写!

为何被未完成执行的S3阻塞

为确保事务的可序列化,MySQL不允许一个会话对在另一会话中未完成的显式/隐式启动的事务中使用的表执行DDL。

服务器通过获取事务中使用的表上的MDL,并将这些锁的释放推迟到事务结束之前来实现。表上的MDL可防止更改表的结构。这种锁定方法的含义是,一个会话中事务正在使用的表在事务结束前不能被其他会话在DDL语句中使用。

MySQL对申请MDL锁的操作会形成一个队列,队列中的写锁获取优先级高于读锁。一旦出现写锁等待,不但当前操作会被阻塞,同时还会阻塞后续该表的所有操作。当事务一旦申请到MDL后,直到事务执行完才会将锁释放,当长事务或未提交的事务未提交完成时,执行DDL语句会等待MDL写锁而阻塞,继而阻塞该表的后续其他操作。

若某表的查询语句频繁,且客户端还有重试机制,即超时后会再起一个新session再请求,该库的线程很快就会爆开(全部都在等待)。

事务中的MDL锁,在语句执行开始时申请,会一直等到整个事务提交了再释放。

2.3 Online DDL

MySQL5.6支持Online DDL,对表操作增加字段等功能时,不会阻塞读写,那为啥还会出现上述案例的结果?

2.3.1 Online DDL过程

  1. 拿MDL写锁
  2. 降级成MDL读锁
  3. 真正做DDL
  4. 升级成MDL写锁
  5. 释放MDL锁

1、2、4、5若无锁冲突,执行时间很短。第3步占用了DDL绝大部分时间,这期间该表可正常读写数据,因此称为Online。

但案例中,第1步就阻塞了。所以必须等待之前的事务全部提交后,才能开始执行。

2.4 如何正确加字段?

先要解决长事务,事务不提交,就会一直占MDL锁。

在MySQL的information_schema 库的 innodb_trx 表中,可查到当前执行中的事务。

若你要做DDL变更的表刚好有长事务在执行,要考虑:

  • 原地等待,先暂停DDL
  • 或kill掉这长事务

但若变更的表是一个热点表,虽数据量不大,但对表请求频繁,又不得不加字段,咋办? 在alter table语句设定等待时间,若在这个指定的等待时间里面能够拿到MDL写锁最好,拿不到也不要阻塞后面的业务语句,先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

MariaDB已经合并了AliSQL的这个功能,所以这两个开源分支目前都支持DDL NOWAIT/WAIT n语法。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

主库上的一个小表做了一个 DDL,同步给slave,由于这时有了先前的single-transaction,所以slave就会出现该表的锁等待,并且slave出现延迟。

2.5 哪些操作会加表级锁?

  • 执行增删改时,默认加行锁
  • 然后执行DDL语句时,如alter table,会默认在表级别加表锁

这么说也不太正确,但也有一定道理,因为确实你执行DDL时,会阻塞所有增删改操作; 执行增删改时,会阻塞DDL操作。

InnoDB提供了自己的表级锁,跟这里DDL语句用的元数据锁不是一个概念。只不过DDL语句和增删改操作,确实互斥。

2.6 再谈表锁

MySQL的表锁很鸡肋,几乎很少用,表锁分为:

  • 表锁
  • 表级的意向锁

2.6.1 语法

# 加表级共享锁
LOCK TABLES xxx READ
​
# 加表级独占锁
LOCK TABLES xxx WRITE

几乎没人会用这两个语法加表锁。

另外两个情况会加表级锁

  • 若有事务在表里执行增删改操作,那在行级会加独占锁,此时同时会在表级加一个意向独占锁
  • 若有事务在表执行查询操作,会在表级加一个意向共享锁

平时操作DB常见的两种表锁:

  • 更新操作加的意向独占锁
  • 查询操作加的和意向共享锁

但这意向独占锁和意向共享锁暂时可当是透明的,因为两种意向锁不互斥。

因为假设有个事务在表里更新id=10的一行数据,在表上加个意向独占锁,此时另外一个事务要在表里更新id=20的一行数据,也会在表上加一个意向独占锁,明显不应互斥,因为他们俩更新表里不同数据,让他们俩在表上加的意向独占锁互斥有何意义? 所以意向锁间不会互斥。

同理,假设一个事务要更新表里的数据,在表级加个意向独占锁,另外一个事务要在表里读数据,在表级加个意向共享锁,此时表级的意向独占锁和意向共享锁应互斥吗?不应该!一个人要更新数据,一个人要读取数据,俩人在表上加的意向锁,凭什么互斥?

因此,所谓的表级的意向独占锁和意向共享锁,似乎是脱了裤子放屁?

但:

  • 手动加表级【共享锁】和【独占锁】
  • 及更新和查询时自动在表级加的【意向共享锁】和【意向独占锁】

之间反而有一定互斥关系:

锁类型

独占锁

意向独占锁

共享锁

意向共享锁

独占锁

互斥

互斥

互斥

互斥

意向独占锁

互斥

不互斥

互斥

不互斥

共享锁

互斥

互斥

不互斥

不互斥

意向共享锁

互斥

不互斥

不互斥

不互斥

在表上面:

  • 手动加的独占锁、共享锁
  • 更新数据和查询返数回据默认自动加的意向独占锁、意向共享锁

锁是互斥的,所以更新数据自动加的表级【意向独占锁】,和你用

# 手动加了表级独占锁
lock tables xxx write

此时任何人都不能执行更新操作。

或你用

# 手动加【表级共享锁】
lock tables xxx read

则任何人也不能执行更新操作,因为更新就会默认加意向独占锁,和手动的表级共享锁互斥。

【手动】加表级的共享锁或独占锁,此时会阻塞其他事务的一些正常读、写操作,因为和他们自动加的【意向锁】都互斥。然而一般都不会去手动加表级锁,所以一般读写操作自动加的表级意向锁,互相之间不会因为互斥而导致阻塞。

一般都是:

  • 对同一行数据的更新操作加的行级独占锁之间互斥
  • 跟读操作都不互斥,读操作默认都走MVCC读快照版本

3 总结

全局锁主要用于全库逻辑备份。对于全部是InnoDB引擎的库,推荐–single-transaction参数。

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock tables这样的语句,你需要追查一下,比较可能的情况是:

  • 要么是你的系统现在还在用MyISAM这类不支持事务的引擎,那要安排升级换引擎
  • 要么是你的引擎升级了,但是代码还没升级。我见过这样的情况,最后业务开发就是把lock tables 和 unlock tables 改成 begin 和 commit,问题就解决了

MDL会直到事务提交才释放。

参考

版权声明:
作者:JavaEdge
链接:https://jkboy.com/archives/10239.html
来源:随风的博客
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
打赏
海报
看完MySQL全局锁和表锁,你废了吗?
FTWRL执行时,要刷脏页数据到磁盘,因为要保持数据一致性,所以执行FTWRL的时机是所有事务都提交完毕后。
<<上一篇
下一篇>>