MySQL事务(脏读、不可重复读、幻读)

ゝ一纸荒年。 2022-04-02 05:41 323阅读 0赞

## 1. 什么是事务？ ##

是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合（工作逻辑单元）；

## 2.事务的四大特性ACID ##

一个事务往往具有一下特性：

**原子性(ATOMICITY): **  
　　一个事务要被完全的无二义性的做完或撤消。在任何操作出现一个错误的情况下，构成事务的所有操作的效果必须被撤消，数据应被回滚到以前的状态。

**一致性(CONSISTENCY): **

一个事务应该保护所有定义在数据上的不变的属性(例如完整性约束)。在完成了一个成功的事务时，数据应处于一致的状态。换句话说，一个事务应该把系统从一个一致-状态转换到另一个一致状态。举个例子，在关系数据库的情况下， 一个一致的事务将保护定义在数据上的所有完整性约束。

**隔离性(ISOLATION): **

在同一个环境中可能有多个事务并发执行，而每个事务都应表现为独立执行。串行的执行一系列事务的效果应该同于并发的执行它们。这要求两件事:

在一个事务执行过程中，数据的中间的(可能不一致)状态不应该被暴露给所有的其他事务。   
　　两个并发的事务应该不能操作同一项数据。数据库管理系统通常使用锁来实现这个特征。

**持久性(DURABILITY): **  
　　一个被完成的事务的效果应该是持久的。

## 3.创建事务的SQL语法 ##

**隐式事务：事务没有明显的开启或者结束的标志，在mysql中，默认是开启自动提交的**

SHOW @@autocommit;

![20210609221551752.png][]

所以针对SELECT、UPDATE、DELETE、INSERT等DQL及DML语句的执行，mysql会自动提交该事务，如果关闭就需要手动提交或者回滚来完成操作。

**显式事务：与隐式事务想反，有明显的开启或结束标志。**

SET autocommit=0
    [START TRANSACTION]  # 可选的语句
    [DELETE | UPDATE | INSERT | SELECT ]  # DML、DQL操作
    [COMMIT | ROLLBACK];  #提交或者回滚

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70][]

可以看出，**在事务未结束之前，操作是有效的且更改了数据实体**，那么试想一下，如果这是有多个事务参与， 肯定会出现各种各样的数据不统一的情况，这就类似多个线程在没有锁的情况下修改同一个全局变量。

**显式事务：回滚点的使用(setpoint)**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 1][]

可以发现在回滚点以前的数据实体被删除、之后的数据实体因为回滚而被撤销操作。

注意：在事务中使用truncate时，就算rollback也会清空整张表

## 4.多个事务在不同事务隔离级别下的并发问题 ##

在mysql下事务的隔离级别有四种且由低到高依次为**Read uncommitted** 、**Read committed** 、**Repeatable read** (默认)、**Serializable** ，这四个级别中的后三个级别可以逐个解决**脏读** 、**不可重复读** 、**幻读**这几类问题

1. **脏读的情况：对于两个事务T1与T2，T1读取了已经被T2更新但是还没有提交的字段之后，若此时T2回滚，T1读取的内容就是临时并且无效的**

开启两个mysql客户端，并创建一张测试表transaction

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 2][]

更改默认隔离级别REPEATABLE READ为READ UNCOMMITTED

SELECT @@tx_isolation; #查询隔离级别
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; # 更改隔离级别

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 3][]

两个客户端同时开启事务，其中一个事务做UPDATE操作，另一个事务做SELECT

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 4][]

若此时黑色背景的客户端进行回滚，则白色背景的客户端读取的数据就是临时并且无效的。即脏读。

**2. 不可重复读: 对于两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段并提交之后，T1再次提取同一个字段，值便不相等了。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 5][]

重复读取的结果不一致的情况发生。

**3. 幻读: 对于两个事务T1、T2，T1从表中读取数据，然后T2进行了INSERT操作并提交，当T1'再次读取的时候，结果不一致的情况发生。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 6][]

## 5. 不同隔离级别所解决的事务并发问题 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 7][]

**READ UNCOMMITTED级别不做演示，其隔离性最低，会出现脏读、不可重复读、幻读等所有情况。**

**READ COMMITTED级别能够避免脏读，下面来进行演示：**

**　1.避免脏读（一个事务读取到另一个事务未提交的数据）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 8][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 9][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 10][]

**2. 无法避免重复读（一个事务读取到另一个事务已经提交的数据）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 11][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 12][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 13][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 14][]

**REPEATABLE READ避免不可重复读的情况发生，下面来看演示：  
1. 避免不可重复读（一个事务读取到另一个事务已经提交的数据）**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 15][]

**2. 无法避免幻读（一个事务多次查询整表数据，由于其他事务新增（删除）记录造成多次查询的记录条数不同（一个事务读取到另一个事务已经提交的数据））**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 16][]

**SERIALIZABLE避免幻读情况，阻塞方式**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 17][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 18][]

可以看出，serializable级别就类似加锁的方式，同一时刻支持多个事务并发，但是针对DML(UPDATE\\INSERT\\DELETE)操作时，当前发起操作的事务会被阻塞，直到其他事务commit或者rollback才会继续执行事务语句。可见效率十分低下。

[20210609221551752.png]: /images/20220402/4eed5f214b2d4e0295087b95886060d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70]: /images/20220402/76dab9fe5d16410b82a2ca78ea16d829.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 1]: /images/20220402/c46cebad0c80406e9cd50f94df7a6318.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 2]: /images/20220402/cb823dcafa1f4a749f74a335d634b755.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 3]: /images/20220402/a8be6ed79f134c6f87fd71774de9c37c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 4]: /images/20220402/85c751e7700449dd9af3ef1e578da730.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 5]: /images/20220402/cd7faca88d204e119d280d5933a408cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 6]: /images/20220402/930ef49b190e4075a9e686d842c837ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 7]: /images/20220402/5e8b48d4e9d64b24acefa17b8352aa26.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 8]: /images/20220402/ffcb77c5ac89484c9518d7c636065648.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 9]: /images/20220402/7c3d5d11ba3c4978bf8531d65f6fa6d6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 10]: /images/20220402/e1ce666dec8342ab9194b2e1ddcaa609.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 11]: /images/20220402/cd7d0ec1fd024d388b09c996fef27796.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 12]: /images/20220402/9f5bc3399c3646a28a094cece34a4356.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 13]: /images/20220402/1ccb2a332f2340ac8ab382933cce3b8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 14]: /images/20220402/216448d6ecca41108ef395b00d454334.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 15]: /images/20220402/a6ae61a9680a420aa05a0d356943f726.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 16]: /images/20220402/e4a9aaf2a2914e6dad7419d2b95b064e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 17]: /images/20220402/539f2fc9bc0c4545b085e2909c5850d3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDY5MDU1_size_16_color_FFFFFF_t_70 18]: /images/20220402/40935fdabaf94fd68874d4c7b1a3234e.png