MySQL-锁学习笔记

mgs2002 2020年02月10日 279次浏览

根据加锁的范围,MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局锁的方法(FTWRL)。

Flush tables with read lock

执行语句之后整个库会处于只读状态,其他线程的如下语句会被阻塞:数据的增删改、数据定义语句(包括建表,修改表结构等)、更新类事务的提交语句

使用场景

全局锁典型的使用场景就是数据库的逻辑备份

逻辑备份是将数据库对象(用户,表,具体的数据,存储过程等)通过数据库工具导出,备份的文件可以被查看和编辑,恢复的时候通过执行备份文件将数据导入数据库,一般使用mysqldump 工具来完成逻辑备份。

如果在InnonDB引擎使用FTWRL语句加全局锁备份数据库的方式非常危险,因为会使得整库都处于只读状态。

  • 如果在主库(或者只有一个库)备份,那么期间数据库不能进行数据的操作(增删改),业务基本停摆。
  • 如果在从库备份,那么期间从库不能同步主库的binglog日志,会导致主从延迟。

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

那什么时候使用FTWRL呢?答案是在不支持事务的引擎里面,比如MyISAM。在MyISAM备份的时候如果不加全局锁,如果备份的时候有数据库更新,则只会读取到最新的数据,破坏了备份的数据一致性。

所以,single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎,那么备份就只能通过 FTWRL 方法。

表级锁

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

表锁

语法结构是lock tables … read/write。可以使用unlock tables 主动解锁,也可以等待客户端断开自动解锁。表锁不仅可以限制其他线程对表的操作,也会限制当前线程接下来的操作。

在还没有出现更细粒度的锁的时候,表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎,一般不使用 lock tables 命令来控制并发,毕竟锁住整个表的影响面还是太大。

元数据锁(MDL)

MDL不需要显示的调用,在对表结构进行修改或者操作(增删改查)的时候自动加上,以保证数据的正确性。举个例子,如果一个查询正在遍历查询表里的数据,期间另外一个线程修改了表的结构(比如删除了一个字段),那么查询线程拿到的结果跟遍历前的表结构不一致,这是不行的。

所以MySQL会在操作(增删改查)的时候加MDL读锁,在对表结构进行修改时加MDL写锁

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

事务中的 MDL 锁,在语句执行开始时申请,但是语句结束后并不会马上释放,而会等到整个事务提交后再释放。

元数据锁场景.png
如上图假设某个表t的查询请求非常频繁,在查询线程(sessionA)的MDL读锁还没有释放的时候另外一个线程(sessionB)对表结构进行修改(添加MDL写锁),这个时候后者就会被阻塞等待查询线程释放MDL读锁,同时后续(sessionC)所有对表的操作(增删改查)都会被阻塞(需申请MDL读锁),相当于这个表被锁住无法读写。所以一般上线的时候需修改表结构都会停止对数据库的请求。

行锁

行锁就是锁住数据库表中的行记录(一行或者多行)。比如线程A中通过for update锁住一行,线程B想要操作这一行就必须等待线程A操作完成提交事务(commit)以后。

并不是所有的引擎都支持行锁(比如MyISAM)。不支持行锁意味着并发控制只能使用表锁,对于这种引擎的表,同一张表上任何时刻只能有一个更新在执行,这会影响到业务并发度。

行锁在查询数据时可以通过for update主动添加,在添加/修改/删除数据时会自动添加。

两阶段锁协议

在 InnoDB 事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束时才释放。这个就是两阶段锁协议。
下面举个例子简单说明一下(假设表t,id为主键,k为字段)
两阶段锁例子.png

当sessionA执行update语句时会添加行锁,索引sessionB的update语句会被阻塞,直到sessionA执行commit后才可以继续执行。

如果你的事务中需要锁多个行,要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

死锁

当系统出现资源循环资源依赖,涉及的线程都在等待其他线程释放锁,就会导致这几个线程陷入无限等待的情况。
死锁例子.png

如上图所示,sessionA等待sessionB释放id=2的锁,sessionB等待sessionA释放id=1的锁,双方相互等待对方释放资源进入了无限的等待,这就是死锁。

当出现死锁以后,有两种策略:

  • 一种策略是,直接进入等待,直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
  • 另一种策略是,发起死锁检测,发现死锁后,主动回滚死锁链条中的某一个事务,让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on,表示开启这个逻辑。

InnoDB 中,innodb_lock_wait_timeout 的默认值是 50s,意味着如果采用第一个策略,当出现死锁以后,第一个被锁住的线程要过 50s 才会超时退出,然后其他线程才有可能继续执行。对于在线服务来说,这个等待时间往往是无法接受的。

所以一般使用第二种方案就是死锁检测。而且 InnoDBinnodb_deadlock_detect 的默认值本身就是 on,就是主动检测死锁并且快速处理。可以通过选项innodb_deadlock_detect来关闭死锁检测。

开启死锁检测后,MySQL会检测死锁并且回滚导致死锁的一个事务或者全部事务。具体是在导致死锁的事务中选择权重比较小的来回滚,权重可能是根据数据操作(增、删、改)的行数确定。

如果并发修改的次数太高,死锁检测过多会导致长时间的停顿(期间并没有执行多少事务),所以还是需要服务端控制并发数,尽量减少死锁检测的开销。