MySQL分析与整理 — 事务

拼搏现实的明天。 2023-02-25 15:21 3阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 MySQL 5.7 版本下进行的

讨论 MySQL 数据库的事务，一般情况下都是有讨论基础的。MySQL 的存储引擎中 InnoDB，NDB 是支持事务的，而且 InnoDB 引擎对事务的支持最全面，所以我们在讨论 MySQL 数据库的事务都是基于 InnoDB 存储引擎，包括示例的表也是 InnoDB 的表。

### 1 什么是事务 ###

谈到事务了，就得说说事务的基本特性：原子性，一致性，隔离性，持久性。至于它们各有什么含义或者特性自行搜索即可，这里不阐述了，毕竟作者的宗旨是以实用为主，理论性的东西就不太涉及了。

**事务的类型大概分以下几种**：

*  自动提交事务，系统默认每一个 T-SQL 命令都可以是一个事务处理，由系统自动开始并提交。
 *  隐式事务，没有开启和结束事务的标志，insert、update、delete 语句本身就是一个事务。默认情况下autocommit 的开关是打开的“1”，也就是ON，就是开启自动提交，每输入一行语句并执行实质上就是完成了一次事务，因为它自动 commit 了。查看当前autocommit的命令是“select @@\[global/session\].autocommit;”，global代表的是全局，session代表的是当前，可以省略不写，默认当前。可以通过“set autocommit = 0，关闭自动提交功能。”
 *  显示事务，具有明显的开启和结束事务的标志，多数为用户自定义的事务。以 start transaction（事务开始，begin也可以）开头，以 commit（事务提交，commit work也可以）或者 rollback（回滚事务，rollback work）语句结束。
 *  分布式事务，跨越多个服务器的事务称为分布式事务。从 MySQL 5+ 开始支持分布式事务。

**事务的控制大概是以下几条命令**：

*  开始事务，标记一个显式事务的开始点，语法是 begin 或者 start transaction 都可以，
 *  提交事务，标记一个成功的隐性事务或显式事务的结束，即事务提交，语法是 commit 或者 commit work。
 *  回滚事务，将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点，语法是 rollback 或者 rollback work。
 *  事务设置，set autocommit 可修改当前连接事务的提交方式。

**事务的并发会出现几个问题**：

*  脏读，事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
 *  不可重复读，事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
 *  幻读，事务 A 读取事务 B 提交的新增数据，这时事务 A 将出现幻读的问题。事务 A 撤销时，把已经提交的事务 B 的更新数据覆盖或者事务 A 覆盖事务 B 已经提交的数据，造成事务 B 所做操作丢失。就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表，我们后续讨论这个锁问题。

### 2 事务的隔离级别 ###

SQL 标准定义了 4 类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

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

本隔离级别，事务可以读取其他未提交事务的执行结果。读取未提交的数据，也被称之为脏读。

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

大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。事务只能读取其他事务已经提交的执行结果。本隔离级别支持所谓的不可重复读，因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。

**Repeatable Read（可重复读）**

MySQL 默认的事务隔离级别，会给查询的记录做快照，直到事务结束。确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，会导致幻读。InnoDB 和 Falcon 存储引擎通过多版本并发控制机制解决了幻读问题。

**Serializable（可串行化）**

最高的隔离级别，对同一条记录读和修改的多个事务只能结束一个，才能开始下一个。通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。在每个读的数据行上加上共享锁，可能导致大量的超时现象和锁竞争。

#### 2.1 设置事务隔离级别 ####

/*
    查询全局和会话事务隔离级别
    */
    select @@global.tx_isolation;
    select @@session.tx_isolation;
    select @@tx_isolation;

我们可以通过修改 MySQL 的配置文件修改全局的事务隔离级别，设置全局会话默认事务隔离级别。在 my.ini（Linux 下对应 my.cnf）文件中，最后加入隔离级别配置，比如加入“transaction-isolation=read-committed”，记得重启 MySQL 数据库服务。

还可以通过命令方式改变单个会话或者所有新进连接的隔离级别，语法如下：

set [session | global] transaction isolation level {
       read uncommitted | read committed | repeatable read | serializable};
    /*
    默认不带session和global是为下一个未开始的事务设置隔离级别。
    如果使用global，语句在全局对新开始创建的所有新连接设置默认事务级别，需要SUPER权限。
    使用session为将来在当前连接上执行的事务设置默认事务级别。
    任何客户端都能自由改变会话隔离级别，或者为下一个事务设置隔离级别。
    */

#### 2.2 隔离级别示例 ####

-- 增加示例表
    drop table if exists tbl_account;
    create table tbl_account(
    	id int primary key,
        aname varchar(10),
        balance double
    )ENGINE=InnoDB default charset=utf8;
    -- 插入示例数据
    insert into tbl_account values 
    (1001, '老孙', 1000),(1002, '老唐', 2300),(1003, '老猪', 20000);

演示为了区分客户端窗口，这里用灰色窗口代表 A 客户端，黑色窗口代表 B 客户端。

##### 2.2.1 读取未提交内容 #####

将“客户端A”和“客户端B”均设置当前事务模式为“read uncommitted”模式。

![在这里插入图片描述][20200713132135260.png_pic_center]  
![在这里插入图片描述][20200713132150374.png_pic_center]  
“客户端A”查询 tbl\_account 表数据，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 1]  
在“客户端A”的事务提交之前，“客户端B”这个时候更新了一条数据信息，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 2]  
这个时候“客户端B”并没有提交数据，但是“客户端A”可以查看到“客户端B”已更新的数据，如下图：

![在这里插入图片描述][202007131322472.png_pic_center]  
如果“客户端B”这个时候由于某种原因回滚了刚才更新的数据，那“客户端A”查询到的数据就是脏数据，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 3]  
这个时候紧接着如果“客户端A”也执行了更新了这条数据，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 4]  
在“客户端A”执行更新语句发现“id=1001”的“balance”还是之前查到的“950”，并不想的应该是“900”。要想解决这个问题可以采用“读已提交内容”的隔离级别。

##### 2.2.2 读取提交内容 #####

将“客户端A”和“客户端B”均设置当前事务模式为“read committed”模式。

![在这里插入图片描述][20200713132459163.png_pic_center]  
![在这里插入图片描述][20200713132514368.png_pic_center]

“客户端A”查询 tbl\_account 表数据，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 5]

在“客户端A”的事务提交之前，“客户端B”这个时候更新了一条数据信息，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 6]

这个时候“客户端B”并没有提交数据，“客户端A”也是无法查询到“客户端B”已更新的数据，解决了“脏读”，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 7]

“客户端B”的事务提交了，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 8]

“客户端A”再执行一个查询发现结果并不一致，即是产生了“不可重复读”的问题。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 9]

##### 2.2.3 可重复读 #####

将“客户端A”和“客户端B”均设置当前事务模式为“repeatable read”模式。

![在这里插入图片描述][2020071313275579.png_pic_center]  
![在这里插入图片描述][20200713132804214.png_pic_center]  
“客户端A”查询 tbl\_account 表数据，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 10]

在“客户端A”的事务提交之前，“客户端B”这个时候更新了一条数据信息，并提交事务，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 11]

这个时候“客户端A”查询数据，和之前的查询数据是一致的，没有出现“不可重复读”的问题，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 12]

继续在“客户端A”操作，也对“id=1001”的数据进行更新，会发现不是想象的“900”而是“850”。它是因为从“客户端B”提交之后的数据进行计算的，数据的一致性没有被破坏，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 13]

切换“客户端B”继续添一条数据，并提交，如下图：

![在这里插入图片描述][20200713132926890.png_pic_center]

“客户端A”此时查询之后，没有查出新增的数据，所以没有出现“幻读”，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 14]

##### 2.2.4 串行化 #####

将“客户端A”和“客户端B”均设置当前事务模式为“serializable”模式。

![在这里插入图片描述][20200713133004837.png_pic_center]  
![在这里插入图片描述][20200713133018331.png_pic_center]

“客户端A”查询 tbl\_account 表数据，如下图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 15]

“客户端B”此时插入一条记录，因为事务隔离级别为 serializable 时会锁表，因此不会出现“幻读”的情况，这种隔离级别并发性极低，开发中很少会用到，如下图：

![在这里插入图片描述][20200713133041744.png_pic_center]

### 结语 ###

事务的应用，只是在数据库层面上的，现实可能还得需要和实际项目开发情况相结合，各种事务的隔离级别不也不太相同，应用的时候还得多多需要注意。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/b11bf44298864e588a20660a70cfaa93.png
[20200713132135260.png_pic_center]: /images/20230209/25e4482cb3524cd4968e55998c6ca318.png
[20200713132150374.png_pic_center]: /images/20230209/0e40e868f0a84dbf8a1c3ad86b0d9111.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20230209/fbe2cd8e77fa4b348a4685a1575e0cea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20230209/946ee8b647214cfc94f5ad761c1901f3.png
[202007131322472.png_pic_center]: /images/20230209/3705ce272b90407a87d3baf7d10dfdb0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20230209/e74e3e72d7444350a3dfc0ed79f3a431.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20230209/f2799787e9a342be82a5d294f1603ccd.png
[20200713132459163.png_pic_center]: /images/20230209/53ff73ae2289445cbff6a3d3e1ce2ab8.png
[20200713132514368.png_pic_center]: /images/20230209/08694168f56e431aac5388e29fcc1e54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20230209/c48c3cb0975b475c89986a70f13a4ea8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20230209/0a9d89b1ee424f10ab230410410a4e72.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20230209/358e6c2ad9c74faa912ae6ab577efd13.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20230209/86cde6f928224f5cb9b6b6975326ad1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20230209/dcfc54b1a96e4dbca5dc9d41627739f5.png
[2020071313275579.png_pic_center]: /images/20230209/0945c991968c495f9a4fb9ea85c09e7c.png
[20200713132804214.png_pic_center]: /images/20230209/478fea1fe61f409fbc700d29ad03d589.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20230209/563febb6a4504c62a9e18c6e2366ffe4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20230209/8cce8a171d1b41d3a9ee9e8979e8c1c4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20230209/2eda8852951742f8ab666f89ed5205ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20230209/f848a8a4cdb34e48af03a0740b3a764c.png
[20200713132926890.png_pic_center]: /images/20230209/8f0766233a6e4f9e91d2735bae823759.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 14]: /images/20230209/c7d0cefe1f1f4fbfbe7e94194dbcb113.png
[20200713133004837.png_pic_center]: /images/20230209/91430f79211f4b958fd04dd9d48a45be.png
[20200713133018331.png_pic_center]: /images/20230209/b13688cf4e454dd185459fdb218d35e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center 15]: /images/20230209/0d3cbde1872444e6a2cb77ff09ae3753.png
[20200713133041744.png_pic_center]: /images/20230209/d6457dd40c414aaa8b85f18c9db7509b.png