MySQL——深入理解事务与锁

逃离我推掉我的手 2022-02-28 07:08 333阅读 0赞

## 前言 ##

本文内容主要参考自《高性能MySQL》、《深入浅出MySQL》、《MySQL DBA 修炼之道》书中的关于事务与锁相关章节，其中《深入浅出MySQL》讲的最为深入，这篇博客算是几本书的综合提炼以及个人理解补充。 上次主要讲了MySQL的范式与设计，是MySQL中非常重要的一部分，这次将进入下一部分，有关数据库的事务与锁。

## I. 事务特性ACID ##

#### α. 事务定义 ####

事务是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。

事务作用包括：

*  为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法；
 *  当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，防止彼此的操作互相干扰。

#### β. ACID ####

<table> 
 <thead> 
  <tr> 
   <th align="center">隔离级别</th> 
   <th align="left">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">原子性</td> 
   <td align="left">一顿操作，要么全部成功要么全部失败。要提及的是InnoDB是支持事务，MyISAM是不支持事务的。</td> 
  </tr> 
  <tr> 
   <td align="center">一致性</td> 
   <td align="left">一致性指的是数据库中的数据从一个一致的状态，转换到另一个一致的状态。数据库中的数据其实是经常处于不一致的状态，这是不可避免的，因此我们提出了事务的概念，用于检测数据库中的数据是否处于一致性状态。如果数据库中有没执行完的事务，那就是不一致的，否则，就是一致的。 事务保证了数据的一致性，当然和CAP理论的一致性，那更多的是指集群节点数据同步的问题。</td> 
  </tr> 
  <tr> 
   <td align="center">隔离性</td> 
   <td align="left">并发事务之间是隔离的，执行时互不影响的。不会因为某个失败，另一个也失败。</td> 
  </tr> 
  <tr> 
   <td align="center">持久性</td> 
   <td align="left">已经提交的事务应当永久改变了数据，被持久化下来。</td> 
  </tr> 
 </tbody> 
</table>

## II. 事务隔离级别 ##

#### α. 一致性问题 ####

一致性的含义前面已经介绍，不一致性发生在事务的过程中，事务一旦提交，将变为一致的。那么，事务过程中不一致主要包括那些呢？

借用《互联网轻量级框架整合开发》13章中的示例来说明。场景为夫妻两用同一张银行卡进行消费。

**丢失更新**

<table> 
 <thead> 
  <tr> 
   <th>时间</th> 
   <th>事务A——老公消费</th> 
   <th>事务B——老婆消费</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>t1</td> 
   <td>查询余额为1000</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t2</td> 
   <td></td> 
   <td>查询余额为1000</td> 
  </tr> 
  <tr> 
   <td>t3</td> 
   <td>花了500</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t4</td> 
   <td></td> 
   <td>花了800</td> 
  </tr> 
  <tr> 
   <td>t5</td> 
   <td>更新余额1000-500=500，事务提交</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t6</td> 
   <td></td> 
   <td>更新余额1000-800=200，事务提交</td> 
  </tr> 
 </tbody> 
</table>

因为t1和t2两个事务读取的余额都是未实时更新的数据，所以后面更新出问题。那么对于事务A来说，更新的500操作相当于丢失了。问题出在哪呢，出在事务B能够读到A中的数据，B在A事务未结束前竟然也能进行数据的修改，所以导致了最后的丢失更新现象。

个人理解：丢失更新指的是最终看起来是丢失更新了，**是结果**。而出错的原因则是下面这三种，脏读、不可重复读和幻读，**是过程**。所以，为了解决结果，先研究出错的过程，也就对应设计了后文所说的隔离级别。

**脏读**

脏读是指事务能够**读取到其他事务中未提交**的数据。上面的示例可以作为脏读的一个例子，下面再举一个。

<table> 
 <thead> 
  <tr> 
   <th>时间</th> 
   <th>事务A——老公消费</th> 
   <th>事务B——老婆消费</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>t1</td> 
   <td>查询余额为1000</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t2</td> 
   <td></td> 
   <td>查询余额为1000</td> 
  </tr> 
  <tr> 
   <td>t3</td> 
   <td></td> 
   <td>花了800，余额200</td> 
  </tr> 
  <tr> 
   <td>t4</td> 
   <td>花了150，<strong>看到事务B中余额200</strong>，计算当前余额为50</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t5</td> 
   <td>事务提交</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t6</td> 
   <td></td> 
   <td>事务回滚，余额50/事务提交，余额200</td> 
  </tr> 
 </tbody> 
</table>

由于老公没等老婆事务提交，A“自作聪明”地要读取最新数据，结果看到B中未提交的数据，A直接计算出来余额提交，此时B提交事务还是回滚事务，账目都是不对的。这种丢失更新主要就是脏读引起了。

当然，针对脏读这种情况，只要限制必须读提交的数据即可，对应隔离级别：**读提交**。

**不可重复读**

<table> 
 <thead> 
  <tr> 
   <th>时间</th> 
   <th>事务A——老公消费</th> 
   <th>事务B——老婆消费</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>t1</td> 
   <td>查询余额为1000</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t2</td> 
   <td></td> 
   <td>查询余额为1000</td> 
  </tr> 
  <tr> 
   <td>t3</td> 
   <td></td> 
   <td>消费800，余额200</td> 
  </tr> 
  <tr> 
   <td>t4</td> 
   <td>消费500，余额500（读不到事务B）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t5</td> 
   <td></td> 
   <td>提交事务，余额200</td> 
  </tr> 
  <tr> 
   <td>t6</td> 
   <td>提交事务，此时读到B事务的余额200，钱不够了</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

对于老公来说，同一个事务中前后读到的余额不一致，也就称为不可重复读，这也就是读提交隔离级别存在的问题。读提交其实没有发生账目出错了，只是会让老公很尴尬，突然没钱付账了。所以针对这种情况，只需要限制老婆先进行消费，老公然后再消费，对于余额**这一条记录**而言，事务的进行变成了串行化。

针对不可重复读这种情况，只要限制对于单个记录的事务串行化即可，对应隔离级别：**可重复读**。

**幻读**

<table> 
 <thead> 
  <tr> 
   <th>时间</th> 
   <th>事务A——老公消费</th> 
   <th>事务B——老婆消费</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>t1</td> 
   <td>查询消费记录</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>t2</td> 
   <td></td> 
   <td>新增消费1笔</td> 
  </tr> 
  <tr> 
   <td>t3</td> 
   <td></td> 
   <td>提交事务</td> 
  </tr> 
  <tr> 
   <td>t4</td> 
   <td>查询到多了一条老婆的记录</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

利用可重复读，对于单条记录实现了访问的串行化。但是如果并不是一条记录，那么还是不能保证串行化。示例中，老公老婆其实是对消费表的操作，t1时老公查看了消费记录，然后期间老婆新增消费，之后t4就会多了一条多了的消费记录，t4和t1相隔其实很近，就会出现老公质疑新增的记录是不是幻读的。

针对幻读这种情况，只要限制对于**单个表**的事务串行化即可，对应隔离级别：**序列化**。

#### β. 隔离级别 ####

脏读、不可重复读都是因为并发事务在修改同一份数据的时候导致的问题，这些问题可以通过对同一个记录加锁的方式来解决。幻读则是并发事务对不同数据操作时导致的问题，此种情况只能通过表锁、事务的串行化来解决。

**读未提交/脏读**

该隔离级别下，并发事务对于同一数据的操作，读是没有加锁的，写是行级共享锁，也就是大家可以一起写，随意读取，并发事务同时操作，互相干扰，所以会出现脏读现象。

**读提交**

该隔离级别下，并发事务对于同一数据的操作，读是行级共享锁，读完立即释放锁；写则是行级互斥锁，直到事务提交才释放锁。这样保证了同一时间只有一个事务写，其他事务无法读，当事务提交释放锁后才可以读到，当然并发读是不影响的。所以，**互联网项目大部分场景下隔离级别常常选择读提交**，有利于并发，也抑制脏读。

**可重复读**

该隔离级别下，并发事务对于同一数据的操作，读写都是行级互斥锁，事务提交后才会释放锁，所以实现了对于同一数据的并发事务的串行化。只有一个事务操作完数据，其他事务才能继续操作。

**序列化**

该隔离级别下，并发事务对于同一表的操作，会通过表锁来实现并发事务的串行化，可以彻底解决一致性的所有问题。当然，带来的问题便是性能急剧下降，对于并发不大需要保证数据安全性的可以使用该隔离级别。

#### γ. 设置隔离级别 ####

查询 `tx_isolation` 环境变量：

mysql> show variables like 'tx_isolation';
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | tx_isolation  | REPEATABLE-READ |
    +---------------+-----------------+
    1 row in set, 1 warning (0.00 sec)
    
    # 默认级别为可重复读
    mysql> select @@global.tx_isolation, @@session.tx_isolation;
    +-----------------------+------------------------+
    | @@global.tx_isolation | @@session.tx_isolation |
    +-----------------------+------------------------+
    | REPEATABLE-READ       | REPEATABLE-READ        |
    +-----------------------+------------------------+
    1 row in set (0.00 sec)

设置事务隔离级别：

# 语法
    set [global|session] transaction isolation level [read uncommitted | read committed | repeatable read | serialization];
    
    # 设置
    mysql> set session transaction isolation level read committed;
    Query OK, 0 rows affected (0.00 sec)            
    
    # 查询
    mysql> select @@global.tx_isolation, @@session.tx_isolation;  
    +-----------------------+------------------------+ 
    | @@global.tx_isolation | @@session.tx_isolation |            
    +-----------------------+------------------------+ 
    | REPEATABLE-READ       | READ-COMMITTED         |            
    +-----------------------+------------------------+ 
    1 row in set (0.00 sec)

## III. 锁 ##

锁是为了解决并发而生，一般解决并发问题我们也会先了解整体的结构，CPU多级缓存、JMM内存模型都是如此。那么MySQL服务器的逻辑架构如何，可以参考下图。客户端要进行数据增删改查，首先连接服务器验证用户和权限，然后MySQL服务器层会对SQL语句进行分析优化，生成解析树，最后通过存储引擎层存储或提取数据的接口返回结果。

[MySQL服务器逻辑架构图][MySQL]  
![MySQL服务器逻辑架构图][MySQL 1]  
MySQL执行SQL细节  
![MySQL逻辑架构细节][MySQL 2]  
每个客户端的连接都会在服务器进程中拥有一个线程，该连接的查询仅仅在自己的线程中执行，多个连接对应多个线程，那么很容易想到，服务器层会利用线程池实现线程重复利用。

锁是用于多个线程或者进程访问共享资源的协调工具，对于数据库而言，除了传统的硬件资源争用外，数据也是共享资源，锁能够解决数据并发访问的一致性，当然一定程度上减轻了系统的并发能力。

#### α. 锁粒度 ####

关于锁粒度，Java中的 `HashTable` 和 `ConcurrentHashMap` 二者都为线程安全，那么无论是1.7还是1.8中的 `ConcurrentHashMap` 为了提高性能，都是采用细化锁粒度的方式实现的， `HashTable` 选择每次锁整个哈希表， `ConcurrentHashMap` 则用分段锁，甚至一个桶一个锁，这样粒度变细，一次锁的数据变少。

同样的，数据库中的表，表中的数据是共享资源，我们可以一次直接锁住整张表，这就是**表级锁**；也可以一次只锁住我们要操作的那些记录，一个记录是一行，这就是**行级锁**。每次锁住的级别不同，也就是锁粒度不同，粒度大小影响了并发性能，锁的系统开销也不一样，越细越大。当然还有**页级锁**，这和存储引擎的页有关，每次一锁就是一组数据，粒度介于前两者之间。

不同的存储引擎支持锁粒度不同，常用的MyISAM和Memory引擎用的是表级锁，InnoDB默认是行级锁，也支持表级锁。表级锁不会发生死锁，其他两种都可能会发生。

#### β. 读写锁/共享排它锁 ####

关于读写锁，和Java中的读写锁 `ReentrantReadWriteLock` 逻辑是一样的，对于读线程大家是共享的，对于写是阻塞的。同样，并发读取数据可以共享的、同时的，并发写入必须获取写锁独占，相当于实现了写串行化。如果已经有事务在写了（持有了锁），读也是要阻塞的。

读锁是共享锁，写锁是排它锁。共享锁不阻塞，多个用户可以同时读一个资源，互不干扰，但或阻塞写锁。排他锁则是一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。

#### γ. 乐观锁/悲观锁 ####

**悲观锁是一副“总有刁民想害朕”的态度**，指的是对数据被外界修改持保守态度，数据修改包括本系统当前的**其他**事务，以及来自外部系统的事务处理。因此，在整个数据处理过程中，将数据处于锁定状态。

悲观锁的实现，往往依靠数据库提供的锁机制，比如上面说的共享排它锁。也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据。在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

**乐观锁是很佛系的态度，总认为应该没什么问题。乐观锁，大多是基于数据版本机制实现的，即为数据增加一个版本标识。在基于数据库表的版本解决方案中，一般是通过为数据库表人为增加**一个 `version` 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新；否则，数据库表当前版本后肯定是被别的事务给更新过，版本号还比自己更新的高，则可以认为自己的更新是过期数据，放弃更新。

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。 乐观锁机制在一定程度上解决了这个问题。

## IV. MyISAM的表锁 ##

MyISAM存储引擎只支持表级锁，不支持事务。

#### α. 查询表锁争用 ####

通过检查 `table_lock_wait` 和 `table_lock_immediate` 状态变量可以分析表级锁争用情况，`table_lock_wait` 表示需要等待的表级锁数，`table_lock_immediate` 表示立即释放表级锁数， `table_lock_wait` 越大代表了表级锁争用越激烈。

#### β. 显式添加表锁 ####

MyISAM存储引擎对于 `select` 会自动给涉及到的所有表添加读锁，注意是所有涉及到的表；在执行 `update/insert/delete` 会自动给涉及到的所有表添加写锁，加锁过程是自动的，**隐式**的。这点注意区别InnoDB引擎，InnoDB给普通的 `select` 是不会上锁的。

因为MyISAM不支持事务，所以显式的通过 `lock table` 命令加锁一般是为了一定程度的模拟事务。通过手动一次性加锁多个表，可以保证对多个表操作时不会受到其他用户操作干扰。比如先查询订单表，再查询订单明细表，由于一开始就对两张表加锁了，所以确保不会有其他用户操作来中途更新了订单明细表，进而导致当前查询数据不一致。

需要注意的是，通过 `lock table` 命令加表锁时，需要一次性把需要的表锁全部获得，之后不能访问没加锁的表，如果获取的是读锁则不能对表进行更新。MyISAM总是一次性获取SQL语句需要的全部锁，所以MyISAM表不会出现死锁问题。当然，隐式加锁也遵从这个原则。

# 获取读锁
    mysql> lock tables student read;
    Query OK, 0 rows affected (0.00 sec)
    
    # 能够读
    mysql> select * from student;
    +----+-------+-------+-------+
    | id | name  | class | score |
    +----+-------+-------+-------+
    |  1 | wwi   |     1 |    12 |
    |  2 | sesed |     1 |    32 |
    |  3 | few   |     2 |     8 |
    |  4 | se    |     2 |    31 |
    +----+-------+-------+-------+
    4 rows in set (0.00 sec)
    
    # 获取的是读锁，插入失败
    mysql> insert into student (id, name, class, score) values (5, 'xxx', 3, 43);
    ERROR 1099 (HY000): Table 'student' was locked with a READ lock and can't be updated # 释放锁 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) # 释放锁，插入成功 mysql> insert into student (id, name, class, score) values (5, 'xxx', 3, 43);
    Query OK, 1 row affected (0.00 sec)

#### γ. 并发插入 ####

总体来说，MyISAM的读写是串行的，但MyISAM也支持并发读写。MyISAM存储引擎有一个系统变量 `concurrent_insert`，专门用于控制其并发插入的行为，值可以为NEVER/0、AUTO/1 和 Always/2。

*  NEVER/0：不允许并发插入功能；
 *  AUTO/1（默认值）：如果MyISAM表没有空洞，也就是中间没有被删除，MyISAM允许在一个进程读表的同时另一个进程在表尾端插入数据；
 *  Always/2：无论有无空洞，都允许尾端插入。

在显式对表进行加读锁时，需要用 `lock tables 表名 read local` 命令，`local` 就是允许当前自己持有读锁的同时，允许其他用户并发的插入数据，没有 `local` 则其他用户需要等待自己释放读锁才能插入成功。需要强调的是，并发插入允许的是其他用户尾插数据，而不是自己也能插入数据，自己仅仅还只是读锁，只能读不能写。牺牲自己，奉献别人。

如下演示了这种情形，左右分别两个客户端连接。  
![read local][]

#### δ. 表锁调度 ####

前面说过MyISAM的读锁与写锁是互斥的，读写操作串行。当其中一个进程先请求获取读锁，另一个后请求获取写锁，默认的锁调度机制会让写锁插队，优先级更高。这是因为MySQL认为写请求更重要，所以MyISAM存储引擎的表不适合有大量频繁更新操作，这样查询请求会被滞后，一直“饥饿”得不到响应。当然可以通过参数 `low-priority-updates` 降低增删改操作的优先级，也可以设置 `max_write_lock_count` 系统参数，表示当一个表的写锁到达这个值后，允许读锁请求获得锁的机会。

当然，除了读会发生“饥饿”，当某一个查询很慢，写锁一直获取不到，也会发生“饥饿”。

## V. InnoDB行锁 ##

InnoDB和MyISAM两个显著区别，一个就是事务，还有一个锁粒度是行级锁。

#### α. 查询行锁争用 ####

通过检查 `Innodb_row_lock_waits` 和 `Innodb_row_lock_time_avg` 状态变量可以分析行级锁争用情况，如果两者偏高说明竞争较为激烈。

可以进一步查看MySQL的 `information_schema` 数据库中的 `innodb_locks` 和 `innodb_lock_waits` 两张表查看锁信息。

mysql> use information_schema;            
    Database changed                          
    mysql> show tables;                       
    +---------------------------------------+ 
    | Tables_in_information_schema          | 
    +---------------------------------------+ 
    | ··········                            | 
    | INNODB_LOCKS                          | 
    | ··········                            | 
    | INNODB_LOCK_WAITS                     | 
    | ··········                            | 
    +---------------------------------------+ 
    61 rows in set (0.00 sec)

#### β. 显式添加行锁 ####

对于 `update/insert/delete` 语句，InnoDB会自动给涉及到的数据集加上排他锁，对普通 `select` 是不会加上任何锁的，这是隐式加锁。至于事务想要显式加锁，可以用如下格式命令：

# 加上共享锁，意味着其他进程也能加上一把锁
    select * from 表名 where ... lock in share mode；
    # 加上排他锁，意味着其他进程无法再加上一把锁
    select * from 表名 where ... lock for update；

两种锁的区别在于，当前事务加了一把锁之后，其他事务还能不能加上锁。两种锁加哪个与加不加，都是不影响其他事务读取该记录的，只是在于更新的时候区别。这一点需要看清楚下面的示例以及注释。

**共享锁**

# 设置不自动提交事务，需手动提交。默认自动提交是指一条语句就是一个事务
    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    # 显式获取共享锁，其他进程也能获取一把共享锁。
    mysql> select * from user_info where userid = 1 lock in share mode;
    +--------+-------+
    | userid | name  |
    +--------+-------+
    |      1 | hello |
    +--------+-------+
    1 row in set (0.00 sec)
    
    # 获取共享锁还能更新该记录。当其他进程也对该记录获取共享锁，则自己更新时会等待，其他进程也更新时则出现死锁
    mysql> update user_info set name = 'kitty' where userid = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from user_info;
    +--------+-------+
    | userid | name  |
    +--------+-------+
    |      1 | kitty |
    |      2 | world |
    |      3 | java  |
    |      4 | mysql |
    |      6 | wqw   |
    +--------+-------+
    5 rows in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

**排它锁**

# 设置不自动提交事务，需手动提交。默认自动提交是指一条语句就是一个事务
    mysql> set autocommit = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    # 显式获取排它锁，其他进程无法再获取该记录的锁，但是可以查看该记录。
    mysql> select * from user_info where userid = 1 for update;
    +--------+-------+
    | userid | name  |
    +--------+-------+
    |      1 | kitty |
    +--------+-------+
    1 row in set (0.00 sec)
    
    # 获取排他锁主要是为了更新该记录不出现死锁，更新完提交事务即可释放锁
    mysql> update user_info set name = 'hello' where userid = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from user_info;
    +--------+-------+
    | userid | name  |
    +--------+-------+
    |      1 | hello |
    |      2 | world |
    |      3 | java  |
    |      4 | mysql |
    |      6 | wqw   |
    +--------+-------+
    5 rows in set (0.00 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

#### γ. 三种行锁形式 ####

InnoDB的行级锁是通过给索引上的索引项进行加锁实现的，所以能粒度到行不是！InnoDB的行锁分为3种：

*  记录锁：对索引项加锁，也就是**对该索引项对应的所有记录**加锁；
 *  间隙锁：对索引项之间的间隙加锁、第一条记录前的间隙或最后一条记录后的间隙加锁；
 *  next-key锁：前两种的组合，对记录及前面的空隙加锁。

InnoDB行锁的实现机制意味着如果不利用索引检索数据，则会对所有记录加锁，相当于表锁，所以要多利用索引，防止锁冲突严重，影响性能。

解释一下上面记录锁加粗的话，什么是 **对索引项加锁，也就是对该索引项对应的所有记录加锁？** 比如一个学生表中有两个学生同名了，都叫 `张三`，相当于表中 `name` 字段有重复，那么该字段建有索引时，对应 `张三` 一个索引段上锁时，两条 `张三` 记录都会上锁。

next-key锁的设计可以解决幻读的问题，next-key锁可以锁定一段记录，防止新增记录造成幻读。特别注意的是，当使用相等的条件（非范围条件）给一个不存在的记录加锁，InnoDB也会用next-key锁。

#### δ. 使用表锁 ####

对于InnoDB引擎而言，绝大多数情况下应当都是使用行级锁，但是在个别情况下也可以使用表级锁。

*  事务需要更新大部分或者全部数据，表又比较大，可以考虑直接使用表锁降低锁开销，加速事务处理速度；
 *  事务涉及多个表，逻辑复杂，容易死锁，引起事务回滚，此时可以参考MyISAM的做法，一次性锁住所有涉及的表，避免死锁，减少数据库事务回滚影响。

`lock tables` 仍然可以给InnoDB添加表级锁，但是这个表级锁不是InnoDB存储引擎层面管理的，而是上一层MySQL Server负责的。使用 `lock tables` 给InnoDB添加表级锁时必须将 `autocommit` 设置为 `0`，否则MySQL不会给表加锁，InnoDB存储引擎也无法得知MySQL加的锁；事务未结束前，不要 `unlock tables` 释放锁，因为该释放动作会隐含提交事务，最好还是手动决定 `commit` 还是 `rollback`，当然手动提交和回滚并不会释放锁，所以还是最后要 `unlock tables` 释放锁。所以总结起来操作的范式如下：

set autocommit = 0;
    lock tables 表名1 read, 表名2 write, ....;
    # 对着表1，2一顿操作
    commit/rollback;
    unlock tables;

#### ε. 死锁 ####

前面说过，MyISAM一次性获取全部表锁的方式是不会发生死锁的。而InnoDB不一样，除了单个语句组成的事务外，锁是逐步获得的，这也决定了会发生死锁现象。

比如事务A中先锁住表a，事务B先锁住表b，然后A又想操作表b则需要等待，而事务B接下来想操作表a则也要等待，互相等就发生死锁。

发生死锁，InnoDB一般会检测到，并让锁住记录少的那个事务回滚让出锁。如果不能检测到死锁，也可以通过超时参数 `innodb_lock_wait_timeout` 来防止锁等待太久。

绝大部分死锁都是可以避免的，避免死锁的措施主要有：

*  尽量大家都按相同的顺序操作表，这对刚才的死锁示例就很有效；
 *  批处理数据时，能够先对数据排序，保证每个线程按照固定顺序处理记录。比如大家都从id=1,2,3,4,5…有序的处理；
 *  尽量一开始就获取事务需要的足够的锁级别，比如「β. 显式添加锁」部分示例，如果选择排他锁则不会出现死锁；
 *  尽量使用较低的隔离级别，低级别释放快，锁竞争小；
 *  尽量使用索引查询数据，让锁更精细，减少锁冲突机会；
 *  合理限定事务大小，小事务处理快，锁冲突几率小；
 *  对于特定的情形，直接使用表锁，不会出现死锁，这一点具体参考「δ. 使用表锁」部分。

#### ζ. 多版本并发控制（MVCC） ####

**MVCC**

MySQL的大多数支持事务的存储引擎实现的都不是简单的行级锁，为了提高并发能力，还同时实现了多版本并发控制，简称MVCC。可以认为MVCC是行级锁的一种变种，但很多情况下它能避免加锁，非阻塞的读，写操作也只锁定必要的行。

MVCC的实现，是通过保存数据某个时间点的快照来实现的，每个事务从开始到结束看到的都是同一个快照，不同的事务可能看到的是不同时间点的快照，这种机制也可以解决“幻读”问题，在常用的读提交、可重复读隔离级别都应用了MVCC。

**InnoDB的MVCC**

InnoDB存储引擎的MVCC实现方式，是通过在每行记录后保存两个隐藏的列来实现的，分别是该行创建的时间、行过期（删除）的时间 ，这里的时间并不是传统意义上的时间，而是**系统版本号** 。每开始一个新的事务，系统版本号自增，事务开始的时候此时系统版本号就定义成该事务版本号，用于和数据库中记录的版本号进行对比。

下面是**可重复读**隔离级别下MVCC的具体操作：

<table> 
 <thead> 
  <tr> 
   <th>操作</th> 
   <th align="left">行为</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>insert</td> 
   <td align="left">InnoDB以当前系统版本号作为新插入每一行的行创建版本号（第一列）</td> 
  </tr> 
  <tr> 
   <td>delete</td> 
   <td align="left">InnoDB以当前系统版本号作为删除的每一行的行过期版本号（第二列）</td> 
  </tr> 
  <tr> 
   <td>update</td> 
   <td align="left">InnoDB将更新后的列作为新的行<strong>插入</strong>数据库，<strong>并不是覆盖</strong>；并以当前系统版本号作为<strong>新行</strong>的行创建版本号（新行第一列），同时以当前系统版本号作为<strong>旧行</strong>的行过期版本号（旧行第二列）。</td> 
  </tr> 
  <tr> 
   <td>select</td> 
   <td align="left">1). InnoDB只查找早于当前事务版本的数据行（记录的行创建版本号≤当前事务版本号），这样可以确保事务读取到的行，要么是在事务开始之前已经存在的，要么是事务自身插入或者修改过的。 <br>2). 行过期版本号要么未定义，要么大于当前事务版本号。可以确保事务读取到的行，在事务开启之前未被删除。</td> 
  </tr> 
 </tbody> 
</table>

定义这样的控制逻辑，可以保证大多数的读都不需要加锁。同样，InnoDB的MVCC也只兼容读提交、可重复读隔离级别

**MVCC和乐观锁**

这感觉，很像乐观锁啊，但还是不一样，乐观锁人为加入版本字段，MVCC是系统实现并隐藏的；其次乐观锁的版本自定义，和事务无关，MVCC的版本号依赖事务，仅当开启新事务才会增加。但不得不说，InnoDB的MVCC实现方式非常有乐观锁的意思，属于乐观并发控制。

## 参考文章 ##

*  [理解数据库的事务，ACID，CAP和一致性][ACID_CAP]
 *  《JavaEE 互联网轻量级框架整合开发》第13章
 *  《深入浅出MySQL》第20章
 *  《高性能MySQL》第1章
 *  《MySQL DBA 修炼之道》第4章

[MySQL]: http://www.cnblogs.com/yysdm/p/10316575.html
[MySQL 1]: /images/20220228/36bb4cd91306449d951f1e1dce465630.png
[MySQL 2]: /images/20220228/46a7f753cc0747bfb6a04c479a1d67fb.png
[read local]: /images/20220228/463c4009fd264d5881c416876c02f180.png
[ACID_CAP]: https://www.jianshu.com/p/2c30d1fe5c4e