MySQL里其实有很多种锁,它们是被设计于处理并发问题的,数据库作为多用户并发资源,当出现并发访问时,数据库需要合理控制资源访问规则,锁就是实现的一种重要数据结构。

根据加锁的范围,可以分为全局锁、表锁和行锁

全局锁

MySQL 提供了一个加全局读锁的方法,命令是 Flush tables with read lock (FTWRL)

它的使用场景是做全库逻辑备份,也就是把整库每个表都select出来存成文本。

听上去,这样做很危险:

  • 在主库上备份,备份时不能执行更新,业务就停掉了
  • 在从库上备份,备份时不能执行主库同步过来的binlog,就导致主从延迟。

但是还是要加锁,要是不加,会出现数据不一致的问题。因为备份的得到的库不是同一个逻辑时间点,就是说这个视图是变化的,不一致的。可以想到事务隔离方面的知识,比如,在RR下开启一个事务

官方自带的逻辑备份工具mysqldump,可以使用参数-single-transaction,在导数据时启动一个事务,确保拿到一致性视图。由于MVCC的支持,这个过程中数据是可以正常更新的。

有这个功能了,为什么还要FTWRL?因为它需要引擎支持这个隔离级别,如果MyISAM这种不支持事务的引擎,那还是破坏了一致性。同时,备份的参数也是只适用于所有表使用事务引擎的库。


还有,似乎使用set global readonly=true也可以达到全库只读的效果,但是还是建议使用FTWRL。

  1. 修改global变量影响大。有些系统使用这个参数作其它的逻辑,比如判断主从库。
  2. 异常处理机制有差异。FTWRL由于客户端发生异常,MySQL会自动释放全局锁,整个库回到可更新的状态;设置readonly后,如果客户端发生异常,数据库还是readonly状态,导致全库长时间处理不可写状态,有风险。

表级锁

表级别的锁还分为两种:表锁元数据锁

表锁

它的语法是:lock tables ... read/write,与FTWRL类似,可以用 unlock tables主动释放,也可以在客户端断开连接时释放。

它会限制包括自己在内的其它线程的读写。

在没有出现更细粒度的锁之前,是用表锁处理并发的。但是对于有行锁的InnoDB引擎说,就不大建议这样用了,锁住整个表的影响还是很大的。

元数据锁(MDL metadata lock)

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

在MySQL5.5引入了MDL,当对一个表进行CRUD时,加读锁;当对表结构进行变更时,加写锁。

小案例

我们知道,读锁和读锁之间不互斥,而读锁与写锁之前会互斥。

那有这么一个坑,就是我需要对一个表的某个字段作修改,有可能导致整个库都挂掉了。

正常的业务查询数据,会自动加上读锁,可当你准备修改一个字段时,由于要加写锁,因此会进入阻塞状态,等待读锁释放。这会把接下来别的查询的请求也阻塞了,因为查询会自动加上读锁,而读锁要在写锁释放后才能获取。因此,后面的请求全部都会被阻塞,再加上客户端的重试操作,这个库的线程很快就会满了。

如何安全地给小表加字段 ?

  • 解决长事务:事务不提交,就会一直占着MDL锁。如果做DDL变更时恰好有长事务,可以考虑暂停,或者kill掉这个长事务。
  • 设置等待时间:指定时间内拿到写锁就执行,不行的话就放弃,这样不会阻塞后面的任务。

行锁

InnoDB支持行锁。

在InnoDB事务中,行锁是需要的时候才加上的,但是要等到事务结束之后才释放。这个是两阶段锁协议。

因此,在使用事务时,如果事务涉及多行,就要把尽可能造成锁冲突,最可能影响并发度的锁往后放。

比如,对于一个影院购票的系统,有这么个买票业务:

  1. 客户A的账户中扣电影票价
  2. 影院B的账户中增加电影票价的钱
  3. 记录交易日志

为了完成这条交易,有两个update,一个insert。根据上述的两阶段锁协议,无论怎样安排语句,所有的操作都是在事务提交后才释放的,因此可以把2放到最后,因为更可能的情况是别的客户也到影院B买票,然后也要对影院B的这行进行加锁。

但是它还是有可能“卡住”。

死锁

当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待状态,这种情况称为死锁。

事务A事务B
begin;begin;
update t set k = k + 1 where id = 1;
update t set k = k + 1 where id = 2;
update t set k = k + 1 where id = 2;
update t set k = k + 1 where id = 1;

此时,A在等待B释放id=2的行锁,而B也在等待A释放id=1的行锁,进入了死锁状态。怎么解决?

  1. 等待,直到超时。但一般不这么做,因为InnoDB默认超时时间是50s,等太久了!

  2. 死锁检测。检测,发现死锁后,主要回滚死锁锁中某一个事务,让其它事务能够继续执行。但是代价很高

  3. 控制并发度。把并发度控制住,死锁检测的成本就很低,2也可以接受。但是它需要在数据库服务端上做,基本的思路是:对于相同行的更新,在进入引擎前排队。

  4. 一行改多行。从业务上实现,比如影院的账户的总额,分成10个记录的和,结果是10个记录加起来。但起来很好,但是需要详细的设计。。

  5. 按顺序加锁,也可以避免。