MySQL05-全局锁&表锁&行锁

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

本文主要介绍的是碰到锁时的现象和其背后的原理

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局锁的方法，命令是：

Flush tables with read lock (FTWRL)

当我们需要让整个数据库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：

• 数据更新语句（数据的增删改）
• 数据定义语句（建表、修改表）
• 更新类事务提交语句

而当数据库出现异常的时候会自动解锁

全局锁的典型使用场景

全局锁的典型使用场景是做全库逻辑备份，也就是把整个库的所有表都select出来成文本。

让这个数据库都只读，貌似很危险

• 如果你在主库上备份， 那么在备份期间都不能执行更新， 业务基本上就得停摆；
• 如果你在从库上备份， 那么备份期间从库不能执行主库同步过来的binlog， 会导致主从延迟。

数据库实现备份的方式

• 什么是冷备份？
  所谓的冷备份，说白了就是在数据库停止运行的情况下，直接备份磁盘中MySQL用来存储数据的那些数据文件。
• 什么是逻辑备份？
  逻辑备份指的是使用 mysqldump 工具去备份数据。使用mysqldump去备份最终得到的参数其实是一堆sql，再通过回放sql的形式完成数据的恢复。
• 什么是热备份？
  所谓热备份其实是指：直接对运行中的数据库进行备份。相对于冷备份，热备份还是比较复杂的。你想啊，对处于运行过程中的数据库进行备份，肯定就得将一些增量的数据也备份进去。通常人们会使用一款叫：xtraback 的工具完成数据库的热备份。
• 什么是快照备份？
  快照备份不是数据库本身提供的能力，本质上它是借助于文件系统的快照功能来实现的对数据库的备份。
  我们知道的Linux服务器本质上也是电脑的，它会有自己的磁盘，无论是固态硬盘，还是机械磁盘。反正会有这种固态存储。还需要进一步对磁盘进行分区。然后才有将Linux文件系统中的目录都会挂载在不同的分区上。这么做的目的，简单来说就像你的window有C盘、D盘、E盘。D盘中的出问题后不会影响E盘一样。
  快照备份要求：数据库的所有数据文件都要放在一个数据分区中。
  常见的支持快照工具的文件系统和设备有：FreeBSD、UFS文件系统、Solaris的ZFS文件系统。GNU/Linux的LVM（Logical Volume Manager）

看来加全局锁不太好。 但是细想一下， 备份为什么要加锁呢？ 我们来看一下不加锁会有什么问题
假设你现在要维护“极客时间”的购买系统， 关注的是用户账户余额表和用户课程表。现在发起一个逻辑备份。 假设备份期间， 有一个用户， 他购买了一门课程， 业务逻辑里就要扣掉他的余额， 然后往已购课程里面加上一门课。

• 如果先备份用户账户余额表，然后用户点击购买，再备份用户的课程表，显然这个备份结果是用户余额表没有发生变化，而用户的课程表却多了一门课程
• 但若先备份用户的课程（还未购买课程），然后用户在购买课程，所以余额表的金额肯定减少，最后再备份用户的余额表，那这个备份结果就是用户的余额少了，但是课程还没购买。

也就是说，不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的，就像上面的余额表和课程表不一致的情况。

表级锁

MySQL里面表级别的锁有两种： 一种是表锁， 一种是元数据锁（meta data lock， MDL)。

表锁的语法是 lock tables …read/write。 与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。

举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句， 则其他线程写t1、 读写t2的语句都会被阻塞。 同时， 线程A在执行unlock tables之前， 也只能执行读t1、 读写t2的操作。 连写t1都不允许， 自然也不能访问其他表。

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

另一类表级的锁是MDL（ metadata lock)。 MDL不需要显式使用， 在访问一个表的时候会被自动加上。

对于引入MDL，其主要解决了2个问题，一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求；另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。
在MySQL 5.5版本中引入了MDL， 当对一个表做增删改查操作的时候， 加MDL读锁； 当要对表做结构变更操作的时候， 加MDL写锁。

• 读锁之间不互斥， 因此你可以有多个线程同时对一张表增删改查。由于会出现不同事务访问同一个记录的现象，这个问题就用行锁来解决。
• 读写锁之间、 写锁之间是互斥的， 用来保证变更表结构操作的安全性。 因此， 如果有两个线程要同时给一个表加字段， 其中一个要等另一个执行完才能开始执行。
  

当一个长事务还没提交，进行表结构变更操作，会导致后面的事务block。当客户端有重试机制时，新起session请求，会导致库的线程很快就会爆满。

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

• 避免长事务。
• 在 alter table 语句里面设定等待时间。
  MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。

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

行锁 怎么减少行锁对性能的影响？

行锁

MySQL的行锁是在引擎层由各个引擎自己实现的。 但并不是所有的引擎都支持行锁， 比如MyISAM引擎就不支持行锁。InnoDB是支持行锁的，这也是MyISAM被InnoDB替的重要原因之一。

概念：锁就是针对数据表中行记录的锁。 这很好理解， 比如事务A更新了一行， 而这时候事务B也要更新同一行， 则必须等事务A的操作完成后才能进行更新。

两阶段锁协议

我们先看一下两个事务的操作

从图中我们可以看出，事务A拥有两个行锁，id=1和id=2的两个记录，这两个锁都是在事务Acommit的时候才释放，也就是说，在InnoDB事务中，行锁是在需要的时候（SQL语句执行中）才加上的，但并不是不需要（SQL语句执行完）了就立刻释放，而是要等到这个事务结束时才释放。这个就是两阶段锁协议：执行SQL时加锁，提交事务时解锁。
知道了两阶段锁，如果我们事务中需要锁多个行， 要把最可能造成锁冲突、 最可能影响并发度的锁尽量往后放，因为一旦出现了锁冲突，那么造成锁冲突的SQL语句的下面的SQL语句将无法执行，阻塞在那里。

两阶段锁协议例子：

假设你负责实现一个电影票在线交易业务， 顾客A要在影院B购买电影票。 我们简化一点， 这业务需要涉及到以下操作：

1. 从顾客A账户余额中扣除电影票价；
2. 给影院B的账户余额增加这张电影票价；
3. 记录一条交易日志。

也就是说， 要完成这个交易， 我们需要update两条记录， 并insert一条记录。 当然， 为了保证交易的原子性， 我们要把这三个操作放在一个事务中。 那么， 你会怎样安排这三个语句在事务中的顺序呢？

试想如果同时有另外一个顾客C要在影院B买票， 那么这两个事务冲突的部分就是语句2了。 因为它们要更新同一个影院账户的余额， 需要修改同一行数据。

根据两阶段锁协议， 不论你怎样安排语句顺序， 所有的操作需要的行锁都是在事务提交的时候才释放的。 所以， 如果你把语句2安排在最后， 比如按照3、 1、 2这样的顺序， 那么影院账户余额这一行的锁时间就最少。 这就最大程度地减少了事务之间的锁等待， 提升了并发度。

死锁与死锁检测

怎么发现出现了死锁

如果这个影院做活动， 可以低价预售一年内所有的电影票， 而且这个活动只做一天。 于是在活动时间开始的时候， 你的MySQL就挂了。 你登上服务器一看， CPU消耗接100%， 但整个数据库每秒就执行不到100个事务。 这是什么原因呢？----出现了死锁

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

死锁的解决策略

• 一种策略是直接进入等待，知道超时。这个超时时间可议通过参数innodb_lock_wait_timeout来设置。 当出现死锁以后， 第一个被锁住的线程要过50s才会超时退出， 然后其他依赖线程能够继续执行
• 另一个策略是发起死锁检测，发现死锁后，主动回滚链条中行的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on， 表示开启这个逻辑。

怎么解决由这种热点行更新导致的性能问题呢？

• 如果你能确保这个业务一定不会出现死锁， 可以临时把死锁检测关掉
• 另一个思路是控制并发度
• 修改MySQL 源码，并发进入引擎之前排队。
• 将一行数据改为多行，如将一个余额账户分为多个，但在数据减少操作时需考虑小于0的情况