Mysql Innodb事务管理

以你之姓@ 2023-02-18 08:04 1阅读 0赞

### 事务的基本要素（ACID） ###

*  原子性（Atomicity）：事务开始后的所有操作，要么全部做完，要么全部不做。事务执行过程中出错，会回滚到事务开始前的状态
 *  一致性（Consistency）：数据开始前和结束后，数据库的完整性约束没有被破坏。比如：A向B转账，不可能A扣了钱，B却没收到
 *  隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
 *  持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

### 事务的并发问题 ###

###### 脏读 ######

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的就是脏数据。

这种情况常发生于转账和取款操作中：

<table> 
 <thead> 
  <tr> 
   <th>时间顺序</th> 
   <th>转账事务</th> 
   <th>取款事务</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td></td> 
   <td>开始事务</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>开始事务</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>查询账户余额为2000元</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td></td> 
   <td>取款1000元，余额被更改为1000元</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>查询账户余额为1000元（产生脏读）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td></td> 
   <td>取款操作发生未知错误，事务回滚余额变更为2000元</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>转入2000元，余额变更3000元（脏读的1000+2000）</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>提交事务</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

备注：按照正常逻辑，此时账户余额应该为4000元

###### 不可重复读 ######

事务A在执行读取操作中，需要查询两次数据，前一次查询账户余额1000元，此时事务B更改账户余额为2000并提交后，这个时候事务A查询账户余额为2000元，和之前查询的不一样，成为不可重复读

<table> 
 <thead> 
  <tr> 
   <th>时间顺序</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>开始事务</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>第一次查询，账户余额1000</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>开始事务</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td></td> 
   <td>更改账户余额2000</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td>提交事务</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>第二次查询，账户余额2000</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

###### 幻读 ######

事务A在执行读取操作，需要两次统计数据的总量，前一次读取数据总量后，此时事务B执行了新增事务的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就想产生了幻觉一样，平白无故多了几条数据，成为幻读

<table> 
 <thead> 
  <tr> 
   <th>时间顺序</th> 
   <th>事务A</th> 
   <th>事务B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>开始事务</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>第一次查询，数据总量100条</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td></td> 
   <td>开始事务</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td></td> 
   <td>新增100条数据</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td></td> 
   <td>提交事务</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>第二次查询，数据总量200条</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

不可重复读和幻读的区别  
：  
（1）、不可重复读是读取了其他事务更改的数据，针对update操作

解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据

（2）、幻读是读取了其他事务新增的数据，针对insert操作

解决：使用标记锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

### SQL92标准（SQL隔离级别） ###

###### Read Uncommitted（读取未提交内容） ######

在该隔离级别，所有事务都可以看到其他未提交事物的执行结果。本隔离级别很少用于实际应用，因为它的性能不比其他级别好多少。读取未提交的数据，也称之为脏读

###### Read Committed（读取提交内容） ######

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。解决了脏读

###### Repeatable Read（可重读） ######

这是Mysql的默认事务隔离级别。解决了不可重复读

###### Serializable（可串行化） ######

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Read Uncommitted（读取未提交内容）</td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>Read Committed（读取提交内容）</td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
  </tr> 
  <tr> 
   <td>Repeatable Read（可重读）</td> 
   <td>×</td> 
   <td>×</td> 
   <td>√(Innodb解决了幻读)</td> 
  </tr> 
  <tr> 
   <td>Serializable（可串行化）</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
  </tr> 
 </tbody> 
</table>

### 锁 ###

InnoDB引擎的锁机制：Innodb支持事务，支持行锁和表锁用的比较多，Myisam不支持事务，只支持表锁。

Mysql官方文档锁的种类

This section describes lock types used by InnoDB.
    
    Shared and Exclusive Locks//共享锁和排它锁
    
    Intention Locks//意向锁（意向共享锁和意向排它锁）
    
    Record Locks//记录锁
    
    Gap Locks//间隙锁
    
    Next-Key Locks//临界锁
    
    Insert Intention Locks
    
    AUTO-INC Locks
    
    Predicate Locks for Spatial Indexes

##### 1、 Shared and Exclusive Locks（共享锁和排它锁） #####

共享锁（S）：允许一个事务去读一行，阻止其他事务获取相同数据集的排它锁。

排它锁（X）：允许获得排它锁的事务更新数据，阻止其他事务获取相同数据集的共享锁和排它锁

手动加锁

共享锁（S）：
    SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
    排他锁（X)
    ：SELECT * FROM table_name WHERE ... FOR UPDATE。

##### 2、Intention Locks（意向锁） #####

意向锁又分为意向共享锁（IS）和意向排他锁（IX）

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁不需要用户干预

对于UPDATE、DELETE和INSERT语句，Innodb会自动给涉及的数据集加排它锁，对于普通SELECT语句，Innodb不会加任何锁。

##### 3、Record Locks #####

单条索引记录加锁，Record Lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚合主键索引，那么锁住的就是这个隐藏的聚合主键索引。所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X锁。

##### 4、Gap Locks #####

在索引记录之间的间隙加锁，或者在某一条索引记录的之前或之后加锁，不包含改索引记录本身，Gap Lock的机制主要是是解决了幻读问题。  
Repeatable Read隔离级别才有的

##### 5、Next-key Locks #####

Next-key Locks是在Innodb和Repeatable Read的情况下使用的数据库锁，官网有介绍，Next-key Locks是行锁和Gap Locks的组合。

#### Gap Locks介绍 ####

假如是for update级别的操作，先看看几条总结的何时加锁的规则

*  唯一、主键索引
    
     *  精确等值查询：Next-key Locks就退化为Record Locks锁，不加Gap Locks锁
     *  范围查询：会锁住where条件中的响应的范围，范围中的记录以及间隙，换言之就是加上Record Locks锁和Gap Locks
 *  非唯一索引
    
     *  精确等值查询：Next-key Lock会加Record Locks和Gap Locks
     *  范围查询：会锁住where条件中的响应的范围，范围中的记录以及间隙，换言之就是加上Record Locks锁和Gap Locks
 *  非索引查询：全表记录Record Locks锁，全表间隙加Gap Locks锁

#### Gap Locks机制解决幻读演示： ####

Innodb+Repeatable Read

表结构 ID主键索引 NAME唯一索引 age普通索引

Gap Locks只会对阻塞insert操作，因为Gap区间不存在任何记录，除了insert操作，其他的操作等价于空操作，所以mysql不去阻塞它

CREATE TABLE `user` (
      `ID_` bigint(20) NOT NULL AUTO_INCREMENT,
      `AGE_` int(11) DEFAULT NULL,
      `HEIGTH_` int(11) DEFAULT NULL,
      PRIMARY KEY (`ID_`),
      KEY `age` (`AGE_`)
    ) ENGINE=InnoDB AUTO_INCREMENT=372 DEFAULT CHARSET=utf8mb4;

##### 演示实例1（主键索引+精确等值读） #####

#Session1
    BEGIN;
    
    select * from user where ID_=4 for update;
    select * from user where ID_=3 for update;

Session2
    BEGIN;
    
    insert user(ID_,AGE_)  VALUES(4,1)
    insert user(ID_,AGE_)  VALUES(3,1)
    insert user(ID_,AGE_)  VALUES(5,1)

session1中4有值，3没有值，都会加Record Locks  
session2中插入ID=4、3都会阻塞，ID=5不会阻塞

##### 演示实例2（主键索引+范围读） #####

#Session1
    BEGIN;
    
    select * from user where ID_<4 for update;

#Session2
    BEGIN;
    insert user VALUES(1,'test',1)
    insert user VALUES(4,'test',1)
    insert user VALUES(5,'test',1)

session1范围读ID<4的，Gap区间是(-∞,4\];  
session2插入ID=1、4会阻塞，但是ID=5不会阻塞。

##### 演示实例3（非唯一索引+精确等值读） #####

#Session1
    BEGIN;
    
    select * from user where AGE_=4 for update;

#Session2
    BEGIN;
    
    insert user(AGE_) VALUES(1)
    insert user(AGE_) VALUES(4)
    
    insert user(AGE_) VALUES(0)
    insert user(AGE_) VALUES(5)
    ROLLBACK;

Session1中查询age=4，区间是\[1，4\]  
sesison2中age=0、5不会阻塞，age=1、4会阻塞

##### 演示实例4（非唯一索引+范围读） #####

#Session1
    BEGIN;
    
    select * from user where AGE_>7 for update;

#Session2
    BEGIN;
    insert user(AGE_) VALUES('test',6)
    
    insert user(AGE_) VALUES('test',7)
    insert user(AGE_) VALUES('test',8)

Session1中查询age>7；区间是\[7，∞+\]  
Session2中插入age=6成功，age=7、8阻塞

##### 演示实例5（非索引条件读） #####

#Session1
    BEGIN;
    select * from user where HEIGTH_=4 for update;
    
    select * from user where HEIGTH_>4 for update;

#Session2
    BEGIN;
    
    insert user(HEIGTH_) VALUES(1)
    insert user(HEIGTH_) VALUES(4)
    
    insert user(HEIGTH_) VALUES(7)
    ROLLBACK;

session1中不管是精确等值读还是范围读，session2中插入任何值都会阻塞

从上面五个演示可以看出来，innodb搜索引擎中，通过索引查询锁的是行，非索引查询使用的锁定的整张表

回头可以看脏读、不可重复度、幻读。可以通过加锁的方式解决

<table> 
 <thead> 
  <tr> 
   <th>隔离级别</th> 
   <th>脏读</th> 
   <th>不可重复读</th> 
   <th>幻读</th> 
   <th>解决锁</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Read Uncommitted（读取未提交内容）</td> 
   <td>√</td> 
   <td>√</td> 
   <td>√</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>Read Committed（读取提交内容）</td> 
   <td>×</td> 
   <td>√</td> 
   <td>√</td> 
   <td>写事务加入X锁</td> 
  </tr> 
  <tr> 
   <td>Repeatable Read（可重读）</td> 
   <td>×</td> 
   <td>×</td> 
   <td>√(Innodb解决了幻读)</td> 
   <td>MVCC</td> 
  </tr> 
  <tr> 
   <td>Serializable（可串行化）</td> 
   <td>×</td> 
   <td>×</td> 
   <td>×</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

### MVCC ###

InnoDB的MVCC，是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。在REPEATABLE READ隔离级别下，MVCC具体的操作如下：

*  SELECT  
    InnoDB会根据以下两个条件检查每行纪录：
    
    InnoDB只查找版本早于当前事务版本的数据行，即，行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。行的删除版本，要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。  
    只有符合上述两个条件的纪录，才能作为查询结果返回。
 *  INSERT
    
    InnoDB为插入的每一行保存当前系统版本号作为行版本号。
 *  DELETE
    
    InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
 *  UPDATE
    
    InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为行删除标识。

优点：  
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好。

缺点：  
每行纪录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

[MVCC][]

[MVCC]: https://www.cnblogs.com/xibuhaohao/p/11065350.html