MySQL4_2锁

发表于 更新于 分类于
  • 锁 / 封锁
    • 锁的类型
      • 排他锁 / X锁
      • 共享锁 / S锁
    • 封锁协议
      • 一级封锁：对应读未提交
      • 二级封锁：对应读已提交
      • 三级封锁：对应可重复读
    • MySQL 并发安全
      • 行锁 + 间隙锁 + MVCC
      • 隔离级别实现
        • 读已提交：二级封锁协议（行锁）+MVCC
        • 可重复读：三级封锁协议+MVCC
        • 解决幻读：Next-Key Locks = 行锁 + 间隙锁
    • 死锁

封锁（Blockade）

封锁是数据库原理范畴内的概念，封锁分为排它锁、共享锁、活锁、死锁这4种，以及封锁协议定义了数据库如何使用这些锁来保证并发安全。
所谓封锁就是事务在对某个数据对象例如表、记录等操作之前，先向系统发出请求对其加锁。
加锁后事务 T 就对该数据对象有了一定的控制，在事务 T 释放它的锁之前，其他事务不能更新此数据对象。例如，事务 T1 要修改 A，若在读出 A 之前先锁住 A，其他事务就不能再读取和修改 A 了，直到 T1 修改并写回 A 后解除了对 A 的封锁为止。这样，就不会丢失 T1 的修改。
确切的控制由封锁的类型决定。基本的封锁类型有两种：排他锁(exclusive locks,简称 X 锁)和共享锁(share locks,简称 S 锁)

  • **X 锁(排他写锁)**：若事务 T1 对数据对象 A 加上 X 锁，则只允许 T 读取和修改 A，其他任何事物都不能再对 A 加任何类型的锁，直到 T 释放 A 上的锁为止。这就保证了其他事务在 T 释放 A 上的锁之前不能再读取和修改 A；
  • **S 锁(共享读锁)**：若事务 T 对数据 A 加上 S 锁，则事务 T 可以读 A 但是不能修改 A，其他事务只能对 A 加 S 锁而不能加 X 锁，直到 T 释放 A 上的 S 锁为止。这就保证了其他食物可以读 A，但在 T 释放 A 上的 S 锁之前不能对 A 进行任何修改。

封锁种类

  • 排它锁（X锁）
    可读可写，一个事务对表加了X锁，其他事务必须等该事务操作完这张表后，才可以对这张表操作。
    如果一个事务给表加了 X 锁（意味着该事务要独占这个表），那么：
    别的事务不可以继续获得该表的 S 锁
    别的事务不可以继续获得该表中的某些记录的 S 锁
    别的事务不可以继续获得该表的 X 锁
    别的事务不可以继续获得该表中的某些记录的 X 锁
  • 共享锁（S锁）
    只读，多个事务可以同时对都某一张表加共享锁
    如果一个事务给表加了 S 锁，那么：
    别的事务可以继续获得该表的 S 锁
    别的事务可以继续获得该表中的某些记录的 S 锁
    别的事务不可以继续获得该表的 X 锁
    别的事务不可以继续获得该表中的某些记录的 X 锁

封锁协议

封锁有 3 级的封锁协议：

  1. 一级封锁协议
    事务 T 在对数据对象 A 进行修改之前，必须对其加 X 锁，直至事务结束才释放。事务结束包括正常结束(COMMIT)和非正常结束(ROLLBACK);
    在一级加锁协议中，如果仅仅是对数据进行读操作而不进行修改，是不需要进行加锁的。所以只能避免修改丢失而不能避免不可重复读和脏读。
  2. 二级封锁协议
    在一级加锁协议的基础上增加事务 T 在读取数据 R 之前必须先对其加 S 锁，读完后即可释放 S 锁；
    二级加锁协议除防止了丢失修改，还可进一步防止读脏数据。例如：事务 T1 正在对数据对象 R 进行修改，此前已经对 R 加上了 X 锁，此时事务 T2 想读取 R，就必须对 R 加上 S 锁，但是 T2 发现 R 已经被 T1 加上了 X 锁，于是 T2 只能等待 T1 释放了在 R 上加的锁之后才能对 R 加 S 锁并读取。这能防止 T2 读取到 T1 未提交的数据，从而避免了脏读。
    但是在二级封锁协议中，由于读完数据后即可释放 S 锁，所以它不能保证可重复读。
  3. 三级封锁协议
    三级封锁协议是指，在一级封锁协议的基础上增加事务 T 在读取数据 R 之前对其加 S 锁直至事务结束才释放。
    三级封锁协议除了防止丢失修改和读“脏”数据之外，还进一步防止了不可重复读。
    上述三级协议的主要区别在于什么操作需要申请加锁，以及何时释放锁(即锁的持有时间)。不同的封锁协议使事务达到的一致性是不同的，封锁协议越高，一致性程度越强。

封锁粒度

封锁粒度指的是封锁对象的大小。
封锁粒度与系统的并发度和并发控制的开销有关：封锁粒度越大，数据库能封锁的数据单元越少，并发度越小，系统开销也变小。
一般来说，处理个别元组的事务以元组为封锁粒度；处理某个关系的大量元组的事务以关系为封锁粒度；处理多个关系的大量元组的事务以数据库为封锁粒度。

在一个系统中，提供多种封锁粒度给不同的事务选择，这种封锁方法称为多粒度封锁（ multiple granularity locking）。

  • 封锁在实现上，由一棵多粒度树组织，根结点是整个数据库，表示最大的封锁粒度，叶结点是最小的封锁粒度，如元组、属性值等。
  • 封锁协议中的给一个节点加锁的同时，该结点的所有后裔结点也会被加上同样的锁。对于该结点来说，这种加锁方式为显式封锁，而对于其后裔结点来说，这样的方式为隐式封锁。
    显然，这样的检查方法效率很低。为此人们引进了一种新型锁，称为意向锁（intention lock）。

意向锁

在具有意向锁的多粒度封锁方法中，对任何一个结点加锁时，必须先对它的上层结点加意向锁。
申请封锁时应按自上而下的次序进行，释放封锁时则应按自下而上的次序进行。（栈）

  1. 意向共享锁（IS锁）：
    对一个数据对象加IS锁，表示它的后裔结点拟（意向）加S锁。
    事务T1对数据对象A加上IS锁后，事务T2可以继续加除X锁以外的锁。
  2. 意向排他锁（IX锁）：
    对一个数据对象加IX锁，表示它的后裔结点拟（意向）加X锁。
    事务T1对数据对象A加上IX锁后，事务T2只能继续加IS或IX锁。
  3. 共享意向排他锁（SIX = S+IX锁）：
    对一个数据对象先加S锁，再加IX锁。例如对某个表加SIX锁，则表示该事务要读（S）整个表，同时会更新（IX）个别元组。
    事务T1对数据对象A加上SIX锁后，事务T2只能加IS锁。

活锁和死锁

封锁可能引起活锁和死锁问题。

活锁

根据事务的优先级顺序，可能会出现某个事务永远在等待封锁的情况，即事务T1封锁了数据对象A后，T2、T3陆续请求封锁，但是T1释放锁后，系统优先批准了T3的请求，T2仍然在等待。
最简单的解决方法就是先来先服务（FCFS），不考虑事务的优先级。

死锁

事务T1封锁了数据A，事务T2封锁了数据B，然后T1请求封锁B，与此同时T2也请求封锁A，但因为两个事务的请求都需要等待对方释放锁，这样就出现了永远在等待对方的死锁。
在数据库中，解决死锁问题主要有两类方法：预防和诊断解除。

死锁预防

  1. 一次封锁法：每个事务一次将所有要使用的数据加锁，否则事务不继续执行
    一次性获取所有锁，锁粒度比较大，降低系统的并发度。
  2. 顺序封锁法：预先对数据规定一个封锁顺序，所有事务都按照这个顺序加锁
    需要预处理的信息太多，开销大，成本高。

死锁诊断和解除

  1. 超时法
    时间过短可能误判死锁，时间过长可能无法及时发现死锁
  2. 等待图法
    一般是撤销一个处理代价最小的事务，下面MySQL中的死锁解除算法也是基于这种方法。

两段锁协议

可串行化调度

可串行性是并发事务正确调度的准则。
当且仅当多个事务的并发执行结果，与按某一次序的串行执行结果相同，这种并发调度策略才是可串行化调度，即具有可串行性。
在一个调度策略中，交换两个事务的不冲突操作的次序，得到另一个调度策略，如果另一个调度策略的执行结果与原来的调度相同，则称原来的调度为冲突可串行化调度，冲突可串行化调度是可串行化调度的充分条件，但不是必要条件。
冲突操作是指不同事务对同一个数据的读写和写写操作，其他操作都属于不冲突操作。

两段锁协议

为了保证并发调度的正确性，DBMS的并发控制机制必须提供一定的手段来保证调度是可串行化的。
目前DBMS普遍采用两段锁协议（TwoPhase Locking，2PL）来实现，所有事务遵守两段锁协议是可串行化调度的充分条件，但不是必要条件。
两段锁的两个阶段：

  • 第一阶段（扩展阶段）：所有事务对数据加锁，但不能解锁；
  • 第二阶段（收缩阶段）：所有事务对数据解锁，但不能加锁。

预防死锁的一次封锁法遵守两段锁协议；但是两段锁协议并不要求事务必须一次将所有要使用的数据全部加锁，因此遵守两段锁协议的事务可能发生死锁。

由于两阶段锁协议的存在，如果我们的事务中需要锁住多个行，最好把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

MySQL 如何保证并发安全

latch 和 lock

MySQL 中的锁主要分为闩锁（latch）和锁（lock）。
latch对象是除了数据库对象外的其他对象，包括操作缓冲池汇总的LRU列表、删除、添加、移动LRU列表中的元素，为了保证一致性必须要有锁介入，因此引入了latch锁。latch是轻量级的锁，因为它要求锁定的时间必须非常短，主要用于保护临界资源的线程安全。
lock对象是事务，用来锁定数据库中的对象，如表、行、页。一般lock的对象仅在事务commit或rollback后进行释放。lock有死锁机制。

- lock latch
对象 事务 线程
保护 数据库内容 内存数据结构
持续时间 整个事务过程 临界资源
模式 行锁、表锁、意向锁 读写锁、互斥量
死锁 通过 waits-for graph、time out 等机制进行死锁检测与处理 无死锁检测与处理机制，仅通过控制应用程序加锁顺序（lock leveling）来保证无死锁发生
存在位置 Lock Manager 的哈希表中 每个数据结构的对象中

latch 不能显式添加，而是线程在获取行锁时前，先对行所在的页面添加 latch，然后再对行添加 lock，添加完行 lock 后再释放页面的 latch。
如果行被其他线程占有，则线程会先释放页面 latch，等待行锁，待获取行锁后会再次对页面添加 latch，查看页面数据是否有改动，再次获取改动后的行。
这种机制主要是为了保证线程获取的行数据的一致性和完整性。

MVCC

MySQL 的特色之一是提供了 MVCC（多版本并发控制）机制，MVCC 给每行数据增加了版本号，事务在执行读操作时只能读到数据的历史版本，因此可以避免脏读等问题。
MVCC 与事务紧密关联，因此我放到事务小节中去论述了。

封锁与 MVCC 之间的关系

封锁与 MVCC 并不是互斥的，MySQL 实现隔离级别时结合了这二者，比如：

  • 读已提交：二级封锁协议+MVCC，二级封锁协议在读之前加 S 锁，读完之后就释放 S 锁，所以不能保证不可重复读与幻读；
  • 可重复读：三级封锁协议+MVCC，读完之后不会立刻释放 S 锁，直到事务提交时才会释放，可以解决可重复读。

封锁协议+MVCC 并不能解决幻读问题，在 MVCC 中是通过 Next-Key Lock 解决的。

Record Locks（行锁）

该锁的官方类型名为 LOCK_REC_NOT_GAP。
和前面提到的表锁一样，分 S 锁和 X 锁，只是作用粒度精确到行了。

Gap Locks（间隙锁）

该锁的官方类型名为 LOCK_GAP。
MySQL 解决幻读问题有两种方案：
第一种是 MVCC，因为新插入的数据事务 ID 必然不在 ReadView 内，因此读取这些记录后会被直接忽略，但是快照读只在普通读操作中生效，如果发生了当前读仍然会有幻读问题；
第二种是加锁，但是加锁有一个问题，就是事务没法给尚不存在的记录加锁。
如果我们希望为 number 值为 8 的记录加 gap 锁，则该记录的前后间隙都不允许别的事务立即插入记录：
MySQL-gap锁
如图中为 number 值为 8 的记录加了 gap 锁，意味着不允许别的事务在 number 值为 8 的记录前边的间隙插入新记录，其实就是 number 列的值(3, 8)这个区间的新记录是不允许立即插入的。比方说有另外一个事务再想插入一条 number 值为 4 的新记录，它定位到该条新记录的下一条记录的 number 值为 8，而这条记录上又有一个 gap 锁，所以就会阻塞插入操作，直到拥有这个 gap 锁的事务提交了之后，number 列的值在区间(3, 8)中的新记录才可以被插入。
另外，如何为(20, +∞)这个区间加 gap 锁？其实是为数据页中的 Infimum 和 Supremum 记录加上了 gap 锁。
比如假设此时表里有 5、25 这两条数据，则SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE查询 10 到 20 范围内的记录，并加上范围(5, 10)、[10, 20]、(20, 25]以内的 gap 锁。
比如假设此时表里有 102、105、107 三个值，则select * from test where n = 105 for update;这个语句会对(102, 105)、(105, 107]这两个区间加 gap 锁。

间隙锁可以通过innodb_locks_unsafe_for_binlog参数来开启关闭：

  • 设置为ON，表示关闭区间锁，此时一致性会被破坏（所以是unsafe）
  • 设置为OFF，表示开启区间锁

Next-Key Locks

该锁的官方类型名为 LOCK_ORDINARY。
Next-Key Lock 其实是Record Lock 和 Gap Lock 的组合，它既会保护该条记录，又能阻止别的事务将新记录插入被保护记录的前后间隙。

意向锁

加表锁时怎么知道该表上有没有行锁？InnoDB 通过意向锁（Intention Locks）来解决这个问题：
1、意向共享锁，英文名：Intention Shared Lock，简称IS 锁。当事务准备在某条记录上加 S 锁时，需要先在表级别加一个 IS 锁。
2、意向独占锁，英文名：Intention Exclusive Lock，简称IX 锁。当事务准备在某条记录上加 X 锁时，需要先在表级别加一个 IX 锁。

IS、IX 锁是表级锁，它们的提出仅仅为了在之后加表级别的 S 锁和 X 锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实 IS 锁和 IX 锁是兼容的，IX 锁和 IX 锁是兼容的。

兼容性 X IX S IS
X 不兼容 不兼容 不兼容 不兼容
IX 不兼容 兼容 不兼容 兼容
S 不兼容 不兼容 兼容 兼容
IS 不兼容 兼容 兼容 兼容

Insert Intention Locks（插入意向锁）

该锁的官方类型名为 LOCK_INSERT_INTENTION。
InnoDB 中事务在等待gap 锁的释放时，还需要在内存里生成一个锁结构，表示事务现在正想往某个间隙中插入记录，但是现在正在等待。

如上图所示，有 3 个事务，其中 T1 持有 gap 锁，所以 T2 和 T3 需要生成一个插入意向锁的锁结构，等待 T1 释放后才能获取到插入意向锁（本质上是将 is_waiting 属性改成了 false），然后再继续执行插入操作。

隐式锁

一般来说间隙锁可以避免 gap 锁锁住的区间被其他事务修改（当要插入的记录所在的区间有 gap 锁，事务会先再该间隙上加一个插入意向锁），但是还有一种情况正好是反过来的：如果一个事务首先插入了一条记录，别的记录如果直接读（SELECT … LOCK IN SHARE MODE 或 SELECT … FOR UPDATE）则会产生脏读问题，如果直接修改则又会产生脏写问题。
这个问题在 InnoDB 中是通过事务 ID 解决的：

  • 聚簇索引中有一个隐藏列 trx_id，存储的是最后改动该记录的事务 ID，新插入记录的 trx_id 当然就是当前事务的事务 ID，如果别的事务想对该记录添加 S 锁或 X 锁，会首先看一下该记录 trx_id 是否是当前正活跃的事务，如果是的话就会创建一个 X 锁然后进入等待状态；
  • 二级索引本身没有 trx_id 列，但是在二级索引页面的 Page Header 部分有一个 PAGE_MAX_TRX_ID 属性，该属性代表对该页面做改动的最大的事务 id，如果 PAGE_MAX_TRX_ID 属性值小于当前最小的活跃事务 id，那么说明对该页面做修改的事务都已经提交了，否则就需要在页面中定位到对应的二级索引记录，然后回表找到它对应的聚簇索引记录，然后再重复聚簇索引的做法。

表锁

表锁种类

  1. lock tables …… read/write
    锁住整个表会对数据库效率产生比较大的影响。
  2. MDL（metadata lock)
    MDL 不需要显式使用，在访问一个表的时候会被自动加上，表结构变更操作之间、表结构变更操作与读表操作之间都是互斥的，保证表结构变更的正确性。
    MDL 可能会导致表的锁死，比如一个 alter 语句正在等待一个长事务（该事务中有 select 语句）释放读 MDL，这时 alter 会加上写 MDL，因此之后的所有事务都需要等待该写 MDL 释放了，因此在变更表结构时最好先将长事务终止，或者给 alter 语句设置等待时间：
    1
    2
    ALTER TABLE tbl_name NOWAIT add column ...
    ALTER TABLE tbl_name WAIT N add column ...

不会加表锁的情况

1、在对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，InnoDB 存储引擎是不会为这个表添加表级别的 S 锁或者 X 锁的；
2、执行 DDL 语句时（ALTER TABLE、DROP TABLE）时，使用的是 Server 层的元数据锁（Metadata Locks）。

手动获取表级 S 锁和 X 锁的方式：
1、LOCK TABLES t READ：InnoDB 存储引擎会对表 t 加表级别的 S 锁。
2、LOCK TABLES t WRITE：InnoDB 存储引擎会对表 t 加表级别的 X 锁。

不过一般表锁不会用到，只会在崩溃恢复之类的场景下会用到。

表级 IS 锁、IX 锁，和之前的描述一致。

如果实现自增列：
1、表级别 AUTO-INC 锁：当表中某列设置了 auto_increment 属性，那么该列的值是会自动生成的，插入时会在表级加一个 AUTO-INC 锁，保证这个字段是严格递增的；当插入语句执行完毕后，该锁会自动释放，而不是在事务结束后再释放。
2、一个轻量级锁：生成 auto_increment 列的值后马上释放。

InnoDB 提供了一个称之为 innodb_autoinc_lock_mode 的系统变量来控制到底使用上述两种方式中的哪种来为 AUTO_INCREMENT 修饰的列进行赋值，当 innodb_autoinc_lock_mode 值为 0 时，一律采用 AUTO-INC 锁；当 innodb_autoinc_lock_mode 值为 2 时，一律采用轻量级锁；当 innodb_autoinc_lock_mode 值为 1 时，两种方式混着来（也就是在插入记录数量确定时采用轻量级锁，不确定时使用 AUTO-INC 锁）。不过当 innodb_autoinc_lock_mode 值为 2 时，可能会造成不同事务中的插入语句为 AUTO_INCREMENT 修饰的列生成的值是交叉的，在有主从复制的场景中是不安全的。

MySQL 全局锁

一般只有全库逻辑备份时才会使用到全局锁

  1. Flush tables with read lock（FTWRL）
    这个命令不保证备份时数据库是否处于一个一致性视图，可能有的事务刚执行一半。
  2. mysqldump
    在导数据前会启动事务，确保可以拿到一致性视图，前提是数据库中所有表都使用了支持事务的引擎，否则就只能使用FTWRL来备份了。

其他存储引擎中的锁

MyISAM、MEMORY、MERGE 这些引擎不支持事务，因此加锁一般都是针对当前会话来说的，比如 Session1 先对表加 S 锁，之后 Session2 再对该表执行 UPDATE 操作时，获取 X 锁的过程就会被阻塞了。
相当于这些存储引擎同一时刻只允许一个会话对表执行写操作，因此这些存储引擎最好用于读多写少的场景下。

常见操作利用锁方式

有以下几种方式：
1、一致性读 - 读操作利用 MVCC（多版本并发控制）
读操作只能读取记录的历史版本：读操作时生成一个 ReadView，记录当时正在执行的事务 ID，记录的每个版本都有事务 ID，查询数据时只能读到在生成 ReadView 之前已提交事务所做的修改，在生成 ReadView 之前未提交的事务或之前才开启的事务所做的修改都看不到。
2、锁定读 - 显式加锁
利用 MVCC 的方式，读写操作彼此并不冲突，性能更高。而加锁的方式读写操作之间都是互斥的，需要排队执行，比较影响性能。
3、当前读 - 隐式加锁
写操作只能针对最新版本的记录，因此写操作前需要加锁。

一致性读（Consistent Reads、一致性无锁读、快照读）

事务利用 MVCC 进行的读取操作称之为一致性读，所有普通的 SELECT 语句在 READ COMMITTED、REPEATABLE READ 隔离级别下都算是一致性读。
也就是上面提到的“读操作利用 MVCC”。

锁定读（Locking Reads）

读-读的情况并不会引起并发冲突，我们不希望对这种情况造成影响，因此 MySQL 给锁分了几个类：
1、共享锁（Shared Locks、S 锁）：在事务要读取一条记录时，需要先获取该记录的 S 锁。
2、独占锁（排他锁、Exclusive Locks、X 锁）：在事务要改动一条记录时，需要先获取该记录的 X 锁。

兼容性 X S
X 不兼容 不兼容
S 不兼容 兼容

一般读取一条记录时我们会获取这条记录的 S 锁，但是如果我们想在读取记录时就获取记录的 X 锁，来禁止别的事务读写该记录，则需要使用一些特殊的 SELECT 语句格式：
1、对读取的记录加 S 锁

1
SELECT ... LOCK IN SHARE MODE;

上面语句为记录加 S 锁，允许多个事务同时发起读请求，但是当别的事务尝试获取 X 锁（SELECT … FOR UPDATE 或修改这些记录）则会阻塞，直到当前事务提交之后将这些记录上的 S 锁释放掉。
2、对读取的记录加 X 锁

1
SELECT ... FOR UPDATE;

上面语句为记录加 X 锁，之后别的事务来获取该记录的 S 锁或 X 锁时都会被阻塞，直到当前事务提交之后将这些记录上的 X 锁释放掉。

写操作（write）如何利用锁

DELETE

DELETE 操作会先在 B+树中定位到这条记录，然后获取这条记录的 X 锁，并执行 delete mark 操作（逻辑删除）。

UPDATE

如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在 B+树种定位到这条记录然后获取该记录的 X 锁，最后在原记录的位置执行修改操作。
如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在 B+树种定位到这条记录后获取 X 锁，将这条记录彻底删除（移入垃圾链表），最后插入一条记录。
如果修改了该记录的键值，则相当于在原记录上 DELETE 后 INSERT。

INSERT

一般 INSERT 操作并不加锁，MySQL 引入了一种称为隐式锁的技术来保护这条新插入的记录在本事务提交之前不被其他事务访问。

显式锁即 select .. for update 语句，从语法上就能看出这个语句加了锁。

让我们分几种情况来分析 insert 语句的锁。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t

insert … select 语句

1
insert into t2(c,d) select c,d from t;

上面Session A语句需要对表 t 的所有行和间隙加锁。原因见下面的例子：

session A session B
insert into t values(-1, -1, -1); insert into t2(c, d) select c, d from t;

上面的两个session，如果过session B先执行，由于对表t的主键索引加了(-∞, 1]这个next-key lock，会在语句执行完毕后，才允许session A的insert语句执行。
但如果没有锁，就有可能出现session B的insert语句先执行，但是后写入binlog的情况，于是，在 binlog_format=statement 的情况下，binlog 里面就记录了这样的语句序列：

1
2
insert into t values(-1,-1,-1);
insert into t2(c,d) select c,d from t;

这个语句到了备库执行，就会把 id=-1 这一行也写到表 t2 中，出现主备不一致。

insert 唯一键冲突

insert 发生唯一键冲突时可能引起死锁，例：

T session A session B session C
T1 begin; insert into t values(null, 5, 5)
T2 insert into t values(null, 5, 5) insert into t values(null, 5, 5)
T3 rollback; (Deadlock fount)
  • 正如前面的《加锁规则》所述，session A执行insert语句时，会在所以c的c=5这行上加记录锁，由于这个索引是唯一索引，因此会退化为记录锁；
  • session B要执行相同的insert语句，发现了唯一键冲突，加上读锁，同时session C也会在同一个记录上加上读锁；

    为什么加读锁？应该是为了保证不被删掉的同时，可以不影响读操作。

  • T3时刻，session A 回滚。这时候，session B 和 session C 都试图继续执行插入操作，都要加上写锁。两个 session 都要等待对方的行锁，所以就出现了死锁。

insert into … on duplicate key update

上面的例子是主键冲突后直接报错，如果写成如下形式：

1
insert into t values(11,10,10) on duplicate key update d=100;

则会给索引c上(5, 10]加一个排他的next-key lock（写锁）。

加锁场景例举（InnoDB）

  1. 原则 1：加锁的基本单位是 next-key lock。

    next-key lock 是前开后闭区间。

  2. 原则 2：查找过程中访问到的对象才会加锁。
  3. 原则 3：只有明确指定主键时 InnoDB 才会使用行锁，否则会使用表锁
  4. 原则 4：命中一个索引并加锁后，执行更新删除操作时还需要对其他索引中对应记录加X锁。
    这个规则涉及加锁顺序，可能引起死锁。
  5. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
  6. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
  7. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

例 1、等值查询间隙锁

  1. 根据原则 1，通过 next-key lock 加锁范围(5, 10]；
  2. 因为第一个查询是等值查询（id = 7），而 id = 10 不满足查询条件，因此 next-key lock 退化为间隙锁，因此最终加锁范围是(5, 10)。

因此 Session B 要往这个间隙中插入 id = 8 会被锁住，但是 Session C 修改 id = 10 这行是可以的。

例 2、主键不明确导致锁表

下面的情况不会锁表：

1
2
3
4
5
6
明确指定主键，并且有此行数据，row lock
SELECT * FROM products WHERE id = '3' FOR UPDATE;
SELECT * FROM products WHERE id = '3' and type = 1 FOR UPDATE;
明确指定主键，若查无此行数据，无lock
SELECT * FROM products WHERE id = '-1' FOR UPDATE;

下面的情况会锁表：

1
2
3
4
5
6
非索引字段，table lock
SELECT * FROM products WHERE name = 'Mouse' FOR UPDATE;
主键不明确，table lock
SELECT * FROM products WHERE id <> '3' FOR UPDATE;
主键不明确，table lock
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

例 3、update未命中行触发间隙锁

1
2
3
4
5
CREATE TABLE `hgc_test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB;
insert into hgc_test values(1, 0), (5, 0), (10, 0);

事务S1执行：

1
2
begin;
update hgc_test set b = 10 where a = 7;

事务S2执行：

1
2
begin;
insert into hgc_test values(8, 1);

由于事务S1已经锁住了(5, 10]这个区间，因此S2执行插入时会被阻塞。

例 4、自增长与锁

InnoDB 中，对每个含有自增长值的表都有一个自增长计数器。

  • 初始化：当对这样的表进行插入操作时，这个计数器会被初始化。
  • 更新：插入操作根据这个自增长计数器值加 1 赋予自增长列，对该计数器的更新需要保证线程安全，这可以通过设置使用表锁还是互斥量来实现。

活锁、死锁与死锁检测

活锁

活锁：如果事务 T1 封锁了数据 R，事务 T2 又请求封锁 R，于是 T2 等待。T3 也请求封锁 R，当 T1 释放了 R 上的锁之后系统首先批准了 T3 的请求，T2 继续等待；然后 T4 又请求封锁 R，T3 在释放 R 上的锁之后系统又批准了 T4 的请求，T2 有可能永远等待，这就是活锁的情形。
避免活锁的简单方法就是采用先来先服务的策略。当多个事务请求封锁同一数据对象时，封锁子系统按请求锁的先后次序对事务进行排队，数据对象上的锁一旦释放就批准批准申请队列中第一个事务获得锁。

死锁

1
死锁在许多操作系统书中都有描述，简而言之，就是多个线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，都会导致这些线程都进入无限等待的状态，称为死锁。
在 InnoDB 中，也会出现两个事务互相等待对方释放某条记录的行锁的情况，从而导致进入死锁状态。死锁可以预防，也可以等发生死锁后再作处理。

如何处理死锁

MySQL 有两种死锁处理方式：

  1. 等待直到超时（show variables like 'innodb_lock_wait_timeout'）
  2. 发起死锁检测，主动回滚一条事务，让其他事务继续执行（show variables like 'innodb_deadlock_detect'）

死锁预防

在数据库中，产生死锁的原因是两个或多个事务都已经封锁了一些数据对象，然后又都请求对已被事务封锁的对象加锁，从而出现死锁。防止死锁的发生其实就是要破坏产生死锁的条件。预防死锁发生通常有以下两种方法。

  • 一次封锁法：一次封锁法要求每个事务必须一次将所有要使用的数据全部加锁，否则就不能继续执行下去。一次封锁法虽然可以有效防止死锁的发生，但是增加了锁的粒度，从而降低了系统的并发性。并且数据库是不断变化的，所以事先很难精确地确定每个事务所需进行加锁的对象，为此只能扩大封锁范围，将事务在执行过程中可能需要封锁的数据对象全部加锁，这就进一步降低了并发度；
  • 顺序封锁法：顺序封锁法是预先对数据对象规定一个封锁顺序，所有事务都按这个顺序实施封锁。例如在 B 树结构的索引中，可规定封锁的顺序必须是从根节点开始，然后是下一级的子节点，逐级封锁。顺序封锁法可以有效地避免死锁，但是要实现顺序封锁法十分的困难，因为很难事先确定每一个事务要封锁哪些对象，因此也就很难按规定的顺序去实施加锁。

由此可见数据库中不适合预防死锁，只适合进行死锁的诊断与解除。

死锁检测与解除

  • 设置最大等待时间，等待超过目标时间后自动释放之前获取到的锁，让别的事务先执行；
    可以通过参数innodb_lock_wait_timeout来设置
    超时法实现简单，但其不足也十分明显，一是有可能误判了死锁，如事务因为其他原因而使等待时间超过时限，系统就会误认为发生了死锁；二是若时限设置得太长，则不能及时发现死锁。
  • 发起死锁检测，发现死锁后，主动回滚死锁链条中的某个事务，让其他事务得以继续执行
    将参数 innodb_deadlock_detect 设置为 on 即表示开启死锁检测。
    死锁检测是一个耗时操作，因为每当一个事务被锁的时候，都要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。
    死锁检测的基础是事务等待图，事务等待图是一个有向图 G=(T,U),T 为结点的集合，每个结点表示正在运行的事务；U 为边的集合，每条边表示事务等待的情况。若 T1 等待 T2，则在 T1,T2 之间画一条有向边，从 T1 指向 T2。事务等待图动态地反应了所有事务的等待情况。并发控制子系统周期性(比如每隔数秒)生成事务等待图，并进行检测。如果发现图中存在回路，则表示系统中出现了死锁。

数据库管理系统的并发控制系统一旦检测到系统中存在死锁，就要设法解除死锁。通常采用的方法是选择一个处理死锁代价最小的事务，将其撤销，释放此事务持有的所有的锁，使其他事务得以继续运行下去。当然，对撤销的事务所进行的数据修改必须加以恢复。

死锁的检测会产生一定的性能损耗，因此解决热点行更新导致的性能问题需要结合业务来进行权衡：

  1. 如果能确保业务一定不会出现死锁，可以临时把死锁检测关掉。
    但是需要注意的是，死锁检测可以保证出现死锁后可以通过业务回滚然后重试来解决，这是业务无损的，而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
  2. 控制并发度
    保证对于相同行的更新，在进入引擎之前排队，这样就可以避免大量的死锁检测工作了。
  3. 将对同一行的操作改成多行
    比如，将库存分成多份，减库存时随机取出一份来操作，这样冲突的概率就会变成原本的 1/10 了，既减少了锁等待个数，又减少了死锁检测的 CPU 消耗。

可能发生死锁的情况

  1. 注意加锁顺序
    比如下面语句查3行数据，而且由于desc，该查询语句是倒序在索引树上遍历的，遍历过程中会给查到的记录和区间加行锁和间隙锁。
    1
    select id from t where c in(5,20,10) order by c desc for update;
    在上面这条语句执行期间，如果有另外一条语句是正序遍历并加锁的，就很有可能会导致死锁，比如如下语句：
    1
    select id from t where c in(5,20,10) lock in share mode;
    因此对同一组咨询，要尽量按照相同的顺序访问。

如何查看死锁

出现死锁后，执行 show engine innodb status 命令，这个命令会输出很多信息，有一节 LATESTDETECTED DEADLOCK。

死锁示例1 - 间隙锁互斥

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 设置使用区间锁（默认使用，因此不需要设置）
-- 设置事务隔离级别为RR（默认为RR，因此不需要设置）
-- 设置需要手动提交
set session autocommit=0;

create table t (
  id int(10) primary key
) engine = innodb;

start transaction;
insert into t values(1);
insert into t values(3);
insert into t values(10);
commit;

在开启区间锁且RR隔离级别的情况下，插入上面数据后会出现以下4个区间：

  • (-infinity, 1)
  • (1, 3)
  • (3, 10)
  • (10, infinity)

线程A：

1
2
3
set session autocommit=0;
start transaction;
delete from t where id=5;

线程B：

1
2
3
4
5
6
set session autocommit=0;
start transaction;
insert into t values(0);
insert into t values(2);
insert into t values(12);
insert into t values(7);

事务A删除某个区间内的一条不存在记录，获取到共享间隙锁，会阻止其他事务B在相应的区间插入数据，因为插入需要获取排他间隙锁，也就是在插入7的时候会发生阻塞。

使用show engine innodb status;命令可以查看锁的情况：
死锁示例1_间隙锁互斥
如果事务A一直不提交或回滚，则事务B会一直等待直到超时，并显示：
Error Code: 1205. Lock wait timeout exceeded; try restarting transaction

死锁示例2 - 共享排他锁死锁

线程A、B、C均执行以下SQL：

1
2
3
set session autocommit=0;
start transaction;
insert into t values(7);

  • A先执行，插入成功并获取到id=7的排他锁

  • B、C在执行时，需要进行PK校验，需要先获取id=7的共享锁，因此阻塞。

  • 如果此时A执行rollback;回滚了，此时会释放掉id=7的排他锁。

  • B、C继续进行主键校验，并同时获取到id=7的共享锁；

  • 如果B、C想要插入成功，必须获得id=7的排他锁，但由于双方都已经获取到共享锁，因此它们都无法获取到排他锁，死锁就出现了。

  • MySQL有死锁检测机制，因此B和C中有一个会插入成功，而另一个事务会自动放弃，并报错：Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction

死锁示例3 - 并发间隙锁的死锁

1
2
3
4
5
6
7
8
A：set session autocommit=0;
A：start transaction;
A：delete from t where id=6;
         B：set session autocommit=0;
         B：start transaction;
         B：delete from t where id=7;
A：insert into t values(5);
         B：insert into t values(8);
  • A、B线程都会在执行删除语句后获取到(3, 10)的共享间隙锁
  • A、B在执行insert语句时希望获得(3, 10)的排他间隙锁，于是出现死锁。
    可以使用show engine innodb status;命令来查看死锁情况。
    死锁示例3_并发间隙锁的死锁
  • 检测到死锁后，事务2自动回滚了，报错：insert into t values(8) Error Code: 1213. Deadlock found when trying to get lock; try restarting transaction
    同时，事务1将执行成功。

QA

如何安全地给表加字段

首先需要处理掉长事务，因为长事务不提交的话会一直占用 MDL 锁。

information_schema 库的 innodb_trx 表中可以看到当前执行中的长事务。
但是如果这样的事务比较多，kill 掉并不一定管用，因为新的请求总是会源源不断地到来，所以最好的方法是在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃：

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

MySQL 连接池被打满怎么办

这里的连接池指的是应用服务器里访问 MySQL 服务的连接池，比如 Druid，

以下哪些场景会导致语句Q1: select * from t1 limit 1被堵住？

  1. 另一个线程在Q1执行前，执行了alter table t1 add index(f1)，当前正处于拷贝数据到临时表阶段。

以下什么情况会发生”等待行锁”的状态？

RR隔离级别下，表t的建表结构和初始化数据如下：

1
2
create table t (id int primary key, c int) engine = innoDB;
insert into t values (1, 1), (11, 11), (21, 21);

在会话1中执行如下语句：

1
2
begin;
select * from t lock in share mode;

可见这条语句希望对表t加一个表级的读锁。

  1. 会进入”等待行锁”的情况1
    insert into t values (15, 15);
    插入时会先给表加IX意向锁，IX意向锁是和会话1中对表t加上的读锁互斥的，因此会导致阻塞。
  2. 不会进入”等待行锁”的情况1
    update t set c = c + 1 where id = 15;
    因为id = 15这条数据不存在，因此这条语句实际上不会加锁。
  3. 不会进入”等待行锁”的情况2
    delete from t where id = 15;
    因为找不到id = 15这条数据，因此也不会加锁。
  4. 不会进入”等待行锁”的情况3
    alter table t add d int;
    alter table 会加MDL，这并不是行锁。

DB的CPU打满怎么排查？

有很多情况都可能引起DB的CPU打满

  1. 业务量上涨了
    现象：如果是突发的业务上涨，业务监控（接口、API）应该会有报警，其次就是看有没有执行次数特别多的SQL
    解决：临时可以对SQL进行限流，后续可以对具体业务做下限流、或DB做扩容
  2. 慢SQL
    现象：有SQL执行速度特别慢，慢SQL一般只是表象，引起慢SQL背后的原因可能有很多，比如死锁、扫描行数过多/读取数据过多（没命中索引）等
    解决：如果有SQL一直在运行中，可以先把对应的线程kill掉，排查这个sql产生的原因，进行优化。
  3. 热点行更新导致行锁过多
    现象：用相关工具查看DB中的行锁数量是否出现异常上涨，行锁过多是有可能把CPU打满的
    解决：排查是否有具体业务场景会导致多线程同时更新同一行数据。

参考

  1. [1] Mysql造成死锁的原因有哪些呢？如何避免？
  2. [2] Mysql 死锁如何排查：insert on duplicate 死锁一次排查分析过程