欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL 笔记整理(6) --全局锁和表锁:给表加个字段怎么有这么多阻碍

程序员文章站 2022-05-17 16:14:05
笔记记录自林晓斌(丁奇)老师的《MySQL实战45讲》 6) --全局锁和表锁:给表加个字段怎么有这么多阻碍 数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要结构。根据加锁的范围,MySQL里面的锁大 ......

笔记记录自林晓斌(丁奇)老师的《mysql实战45讲》

6) --全局锁和表锁:给表加个字段怎么有这么多阻碍

  数据库锁设计的初衷是处理并发问题。作为多用户共享的资源,当出现并发访问的时候,数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要结构。根据加锁的范围,mysql里面的锁大致可以分为全局锁,表级锁和行锁三类。这篇笔记主要包含全局锁和表级锁。行锁的内容会在之后再进行分享。

全局锁:

  全局锁就是对整个数据库实例加锁。可以通过命令 flush tables with read lock (ftwrl)对全局进行加锁。使用全局锁后,其他线程的以下语句会被阻塞:数据更新语句(增删改),数据定义语句(包括建表,修改表结构等)和更新类事务的提交语句。

  全局锁的典型使用场景是,做全库逻辑备份。但如果你直接用上面的ftwrl去进行备份,会让整库都处于只读状态,这光想想就觉得危险。如果你在主库上备份,那么在备份期间都不能执行更新,业务基本停摆;如果你在从库上备份,那么备份期间从库不能执行从主库同步过来的binlog,会导致主从延迟。那么不加锁备份数据库可以吗?答案是不可以的,比如你有两张表,余额表和商品表。如果不加锁,在备份期间时先备份余额表,再备份商品表会出现什么情况呢?假设在这两张表备份的间隔时间内进行了数据更新,则余额表数据在备份表中没有减少,但是商品表在备份表中的数据却减少了,造成了逻辑上的数据不一致问题。当然这两张表的备份顺序颠倒过来也是一样会有这个问题。

  官方自带的逻辑备份工具是mysqldump.当mysqldump使用参数-single-transaction时,备份数据之前会启动一个事务,来确保拿到一致性视图。而由于mvcc(多版本并发控制)的支持,这个过程中数据是可以正常更新的。

  至此,你可能会有个疑问,为什么有了上述这个功能,还需要ftwrl呢?此处有个细节是 可重复读是很好,但并不是所有的mysql引擎支持这个隔离级别的。(如myisam这种不支持事务的引擎,就需要执行ftwrl命令了。)所以,single-transaction方法只适用于所有的表使用事务引擎的库。

  既然要保证备份时全库只读,为什么不使用set global readonly = true的方式呢? 不建议使用这种方式有以下两方面原因:

  • 在有些系统中,readonly的值会被用来处理其他逻辑。
  • 在异常处理机制上的差异。ftwrl命令后如果客户端发生异常断开连接,那么mysql会自动释放这个全局锁。而readonly=true会保持数据库长时间处于只读状态。

表级锁:

  mysql里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,mdl)

  表锁的语法是 lock tables ... read/write。与ftwrl类似,可以用unlock tables主动释放锁,也可以在客户端断开的时候自动释放。需要注意的是,lock tables语法除了会限制别的现成的读写外,也限定了本线程接下来的操作对象。在没有更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于innodb这种支持行锁的引擎,一般不使用lock tables命令来控制并发,毕竟锁住整个表的影响面还是太大。

  另一类表级锁mdl不要显示使用,在访问一个表的时候会自动加上。它的作用是保证读写的正确性。如一个查询正在遍历一个表中的数据,而同时另一个线程对这个表结构做了变更,删了一列,那么查询线程拿到的结果就会和表结构对不上,肯定是不行的。因此,在mysql5.5引入了mdl,当对一个表做增删改查操作的时候,加mdl读锁;当要对表结构变更时,加mdl写锁。

  • 读锁之间不互斥,因此可以有多个线程同时对一张表增删改查。
  • 读写锁之间,写锁之间是互斥的。以此来保证变更表结构操作的安全性。因此,如果有两个线程要同时给一个表加字段,其中一个要等另一个执行完才能开始执行。

  另外,mdl会知道事务提交才释放,在做表结构变更的时候,一定要小心不要导致锁住线上查询和更新。

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

  • 你的系统还在使用myisam这类不支持事务的引擎,需要安排升级更换引擎了
  • 你的引擎升级了,但是代码还没有升级。这样把lock tables和unlock tables更换成begin和commit,问题就解决了。

上篇问题答案:

  表结构如下所示:

create table `geek` (
  `a` int(11) not null,
  `b` int(11) not null,
  `c` int(11) not null,
  `d` int(11) not null,
  primary key (`a`,`b`),
  key `c` (`c`),
  key `ca` (`c`,`a`),
  key `cb` (`c`,`b`)
) engine=innodb;

由于历史原因, a和b需要做联合主键。那么既然主键包括了a,b这两个字段,又单独在c上创建了一个索引,索引就已经包含了三个字段了,为什么还要创建ca,cb索引呢?有人给出的理由是业务里有这样两个查询:

select * from geek where c=n order by a limit 1;
select * from geek where c=n order by b limit 1;

 这个理由对吗?为了这两个查询,这两个索引是否都必须呢?为什么呢?

  我们以一组记录来回答这个问题。

a b c d
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d

  主键a,b的聚簇索引组织顺序相当于order by a,b。也就是先按a排序,再按b排序,c无序。

  索引ca的组织顺序如下(先按c排序,再按a排序),同时记录主键部分b的值

c a b
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

  你可能会发现,ca的组织顺序和索引c的顺序是一模一样的。

  索引cb的组织顺序如下(先按c排序,再按b排序,同时记录主键a)

c b a
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

  所以,结论是ca可以去掉,cb索引仍要保留。ca去掉的原因是组织顺序与索引c相比完全相同。

问题:

  备份一般都会在备库上执行,你在用-single-transaction方法做逻辑备份的过程中,如果主库上的一个小表做了一个ddl,比如给一个表上加了一列。这时候,从备库上会看到什么现象呢?