【mysql性能优化】事务中隔离性的实现

梦里梦外; 2024-02-19 20:33 32阅读 0赞

#### 文章目录 ####

*  1 事务——what
 *   *  1-1 事务的含义：
     *  1-2 事务的四大特性（ACID）
 *  2 事务——how
 *   *  2-1 如何操作事务
     *   *  2-1-1 手工开启
         *  2-1-2 事务的提交和回滚
         *  2-1-3 设定事务是否自动开启
 *  3 事务——where
 *   *  3-1 脏读
     *  3-2 不可重复读取
     *  3-3 幻读
 *  4 隔离性——隔离性级别
 *  5 锁——what
 *   *  5-1 锁 ——innodb中锁的类型
     *   *  5-1-1 共享锁（行锁）：shared locks
         *  5-1-2 排它锁（行锁）：exclusive locks
         *  5-1-3意向共享锁（表锁）：intention shared locks
         *  5-1-4意向排它锁（表锁）：intention exclusive locks
         *  5-1-5 自增锁：auto——inc locks
     *  5-2 锁的算法
     *   *  5-2-1 记录锁（record）
         *  5-2-2 临键锁（next-key）——innodb的默认行锁算法
         *  5-2-3 间隙锁（gap）
 *  总结：
 *  疑问：

## 1 事务——what ##

### 1-1 事务的含义： ###

数据库操作的最小单元，事务是一组不可在分割的操作集合。

### 1-2 事务的四大特性（ACID） ###

> 原子性（Atomicity）：  
> 最小的工作单元，整个工作单元要么一起提交成功，要不一起失败回滚

> 一致性：（Consistency）  
> 事务中操作的数据以及状态改变是一致性的，即写入资料的结果必须完全符合预设的规则，不会应该出现系统意外等原因导致状态不一致。

> 隔离性(Isolation)：  
> 一个事务所操作的数据在提交之前，对其他的事务的可见性的设定（一般设置为不可见）

> 持久性(Durability):  
> 事务所做的修改会永久保存，不会因为系统意外到导致数据丢失。

其他的三条比较好理解，今天我们来讲解一下如何保证隔离性的。

## 2 事务——how ##

### 2-1 如何操作事务 ###

#### 2-1-1 手工开启 ####

begin
    或者
    start transaction

#### 2-1-2 事务的提交和回滚 ####

commit # 提交
    rollback # 回滚

#### 2-1-3 设定事务是否自动开启 ####

set session autocommit = on/off

## 3 事务——where ##

我们先来看一下，如果没有事务隔离性的情况下，生产环境中，经常因为高并发遇到的读写问题。

### 3-1 脏读 ###

在下图中，有两个事务同时操作一个数据记录。  
首先事务B对记录进行了修改，让age变成了18 但是事务B还没有提交 ，  
此时，事务A来读取记录，读取的记录就是age18，  
此时，事务B发现修改有问题，直接就回滚刚才了操作。  
那么此时，事务A就脏读了数据，本来应该读取age=16，却读到了age=18。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70]

### 3-2 不可重复读取 ###

不可重复读是指A事务读取了B事务已经提交的更改数据。假如A在取款事务的过程中，B往该账户转账100，A两次读取的余额发生不一致。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 1]

### 3-3 幻读 ###

A事务读取B事务提交的新增数据,会引发幻读问题。幻读一般发生在计算统计数据的事务中，例如银行系统在同一个事务中两次统计存款账户的总金额，在两次统计中，刚好新增了一个存款账户，存入了100，这时候两次统计的总金额不一致。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 2]  
不可重复读和幻读的区别是：  
**前者是指读到了已经提交的事务的更改数据（修改或删除），后者是指读到了其他已经提交事务的新增数据。**  
对于这两种问题解决采用不同的办法，防止读到更改数据，只需对操作的数据添加行级锁，防止操作中的数据发生变化；二防止读到新增数据，往往需要添加表级锁，将整张表锁定，防止新增数据（oracle采用多版本数据的方式实现）。

## 4 隔离性——隔离性级别 ##

针对上面的三种并发问题，提出了四种隔离性级别去应对问题。内容依次如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 3]  
innodb引擎对于隔离级别的支持程度：  
默认情况下，RR级别已经解决了三种问题情况。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 4]  
那么隔离级别到底是如何实现的呢。  
主要通过锁和mvcc来控制的。接下来我们来讲解一下锁和mvcc。

## 5 锁——what ##

锁，用于管理不同事务杜雨共享资源的并发管理。

> 表锁和行锁的区别：  
> 锁定粒度：表锁> 行锁  
> 加锁效率：表锁> 行锁  
> 冲突概率：表锁> 行锁  
> 并发性能：表锁< 行锁

innodb 支持行锁和表锁（但是表锁在innodb中属于另类的行锁。）

### 5-1 锁 ——innodb中锁的类型 ###

#### 5-1-1 共享锁（行锁）：shared locks ####

> 又称为读锁，简称s锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改；

加锁释锁方式：

select*from users WHERE id=1  LOCK IN SHARE MODE;
    commit/rollback

#### 5-1-2 排它锁（行锁）：exclusive locks ####

> 又称为写锁，简称×锁，排他锁不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的锁（共享锁、排他锁），只有获取了该排他锁的事务才可以对数据行进行读取和修改，（其他事务要读取数据可来自于快照）

加锁释锁方式：

delete/update/insert   --默认加上×锁
    SELECT* FROM table_name WHERE … FOR UPDATE
    commit/rollback

#### 5-1-3意向共享锁（表锁）：intention shared locks ####

表示事务准备给数据行加入共享锁，即一个数据行加共享锁前必须先给该表加上is锁，意向共享锁之间可以互相兼容。

#### 5-1-4意向排它锁（表锁）：intention exclusive locks ####

表示事务准备给数据行加入排他锁，即一个数据行加排他锁前必须先取得该表的ix锁，意向排他锁之间可以互相兼容。

#### 5-1-5 自增锁：auto——inc locks ####

针对自增列自增长的一个特殊的表级别的锁。默认取值为1，代表连续，事务未提交，id永久丢失。

### 5-2 锁的算法 ###

#### 5-2-1 记录锁（record） ####

**锁住具体的索引项**

当sq|执行按照唯一性(Primary key、Unique key)索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时SQL语句加上的锁即为记录锁Record locks，锁住具体的索引项。

--------------------

表结构：id为主键，name 为非空。  
数据内容如下：  
![在这里插入图片描述][20190709160202710.png]

select * from t where id =4 for update;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 5]  
此时，sql会命中id=4的记录，按照定义，innodb会锁定id=4的记录，  
如果在事务中执行以上sql语句，并且没有完成事务，这个情况下执行以下语句：

select * from t where id =4 for update;--阻塞执行
    select * from t where id =7 for update;--可以正常执行

#### 5-2-2 临键锁（next-key）——innodb的默认行锁算法 ####

**锁住记录+区间（左开右闭）**  
当sq|执行按照索引进行数据的检索时，查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为next-key locks，锁住索引的记录所在区间和next区间（左开右闭）

--------------------

表结构：id为主键，name 为非空。  
数据内容如下：  
![在这里插入图片描述][20190709160202710.png]  
为此，如果执行如下sql进行更新记录的时候：

select * from t where id >5 and id <9 for update;

innodb会将数据分为如下区间（左开右闭）：  
![在这里插入图片描述][20190709160809928.png]  
此时，sql会命中id=7的记录，按照定义，innodb会锁定两个区间，第一个是包含id=7的（4,7\]区间和next-key临界区间（7,10\]。  
如果在事务中执行以上sql语句，并且没有完成事务，这个情况下执行以下语句：

select * from t where id =4 for update;--可以正常执行
    select * from t where id =7 for update;--阻塞状态
    select * from t where id =10 for update;--阻塞状态
    insert into t (id,name)values('9','9') --阻塞状态

通过以上的操作，我们可以验证当sq|执行按照索引进行数据的检索时，查询条件为范围查找（between and、<、>等）并有数据命中则此时SQL语句加上的锁为next-key locks，锁住索引的记录所在区间和next区间（左开右闭）。  
那么他为什么要这样做呢，也就是命名是修改id=7，为什么要将id>4 and id<=10的记录都锁定呢。  
这样做的主要原因就是为了防止幻读。

#### 5-2-3 间隙锁（gap） ####

**锁住数据不存在的区间（左开右开）**  
当sq|执行按照索引进行数据的检索时，查询条件的数据不存在，这时SQL语句加上的锁即为Gap locks，锁住索引不存在的区间（左开右开）

--------------------

表结构：id为主键，name 为非空。  
数据内容如下：  
![在这里插入图片描述][20190709160202710.png]  
为此，如果执行如下sql进行更新记录的时候：

select * from t where id >4 and id <6 for update;
    或者
    select * from t where id =6 for update;

此时，sql的执行没有命中数据，即当记录不存在的时候，临键锁退化为gap锁。此时，innodb将数据划分为如下结果：这样之锁住了第三个Gap区间。  
![在这里插入图片描述][20190709163436254.png]  
此时，sql会没有命中记录，按照定义，innodb会锁定一个区间，（7,10）区间。  
如果在事务中执行以上sql语句，并且没有完成事务，这个情况下执行以下语句：

insert into t (id,name)values('9','9') --阻塞状态
    insert into t (id,name)values('6','6') --阻塞状态

## 总结： ##

1、innodb中，行锁锁的到底是什么呢？  
innodb的行锁是通过给索引上索引项加锁来实现的。  
只有通过索引条件来检索的数据，innodb才能使用行级锁，否则innodb将使用表锁。  
可以通过以下几个例子来检测：

--例子一：通过非索引字段phoneNum 来修改记录，锁定的是整个表。
    set session autocommit = off；
    update user set lastUpdate=now（） where phoneNum =‘1234567’ --（这一个操作，只会修改数据库一条记录，本条记录id=1）
    
    --此时不进行commit操作，直接进行以下操作。
    update users set lastUpdate=now（） where id = 2 --提示等待执行，需要等上一个事务完成后才能进行
    update users set lastUpdate=now（） where id = 1 --提示等待执行，需要等上一个事务完成后才能进行
    
    
    --例子二：通过索引字段id 来修改记录，锁定的是一行记录
    update user set lastUpdate=now（） where id = 1
    
    --此时不进行commit操作，直接进行以下操作。
    update users set lastUpdate=now（） where id = 2 --可以直接执行完成
    update users set lastUpdate=now（） where id = 1 --提示等待执行，需要等上一个事务完成后才能进行

2、意向锁的意义：  
意向锁（is，ix）是innodb数据库操作前自动加的，不需要用户干预。  
其目的是：当事务想去进行锁表的时候，可以先判断意向说是否存储，存储的时候可以快速返回该表不能启用表锁。这样便可以提高效率，不用去判断表中的某一行是否加锁了。  
3、Gap锁，只在RR（可重复读）级别的锁中出现。  
4、死锁的避免：  
1）类似的业务逻辑以固定的顺序访问表和行。  
2）大事务拆小。大事务更亻顷向于死锁，如果业务允许，将大事务拆小。  
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。  
4）降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择  
5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁（或者说是表锁）

## 疑问： ##

1、如何测试是否用了意向共享锁。  
2、如何解释意向排它锁和行锁的关系。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/0913ee525d3a4ee1a02c23d2ade1ce2e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/79b9aa23f26e4de9a92573e176f88f19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/4ae5e11b2f8e404999f42a0ac4c8a678.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bc74e25341fa4624b689907ddfc95884.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bccb8794251c45af8604b53629209ee1.png
[20190709160202710.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f1634affe9554f6181ef31fac19b1fc0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWluZzY2_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/706aef317f6948979d629084222fedcb.png
[20190709160809928.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c45cd3a7274e4bb8bcaea3cd5b2ccb39.png
[20190709163436254.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a6efd80e68c342babe0ea8c9a90f7d37.png