【MySQL学习】事务管理

求知.

已于 2023-05-18 23:06:05 修改

阅读量1.4k

点赞数 9

分类专栏： MySQL学习文章标签： mysql 数据库事务

于 2023-05-17 18:17:04 首次发布

本文链接：https://blog.csdn.net/qq_61635026/article/details/130611507

版权

MySQL学习专栏收录该内容

12 篇文章 1 订阅

订阅专栏

一、事务的基本认识

1.1 事务的基本概念

简单来说，事务可以看作是一组数据操作语言（DML）语句的组成，这些语句在逻辑上具有强烈的相关性。事务的目的是确保这组操作要么全部成功执行，要么全部失败回滚，以保持数据库的一致性。
在事务中，如果其中任何一条语句执行失败，整个事务将会被回滚，即之前的操作都会被撤销，数据库状态回到事务开始之前的状态。只有当所有的操作都成功执行时，事务才会被提交，使得之前的操作生效并永久保存在数据库中。
事务的使用可以确保数据库操作的完整性和可靠性，特别是在需要多个操作同时生效或者需要对数据进行一系列关联性修改时非常有用。通过将相关的操作组合在一个事务中，可以确保数据在多个操作之间保持一致，同时提供了对数据库的回滚和提交的控制能力。

1.2 事务的基本属性

事务具有以下四个基本属性，通常被称为 ACID 特性：

原子性（Atomicity）：事务被视为一个不可分割的原子操作。要么所有的操作都成功执行，将更改永久保存到数据库中，即事务被提交；要么如果任何一个操作失败，所有的操作都将被回滚，撤销对数据库的修改，即事务被中止。原子性确保了事务的完整性，即要么所有的操作都生效，要么都不生效。
一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。这意味着事务开始之前的数据库状态应该满足所有的约束条件、完整性规则和触发器，事务结束后，数据库状态也应该满足这些规则。如果事务执行过程中违反了一致性规则，事务将会被回滚，以保持数据库的一致性。
隔离性（Isolation）：事务的隔离性指的是每个事务的执行都应该与其他事务相互隔离，使得每个事务感觉不到其他事务的存在。事务的隔离性可以防止并发执行的事务相互干扰，避免数据的不一致性和并发问题，保证事务的独立性和正确性。
持久性（Durability）：一旦事务提交，其所做的修改将会永久保存在数据库中，即使在系统故障或重启后也不会丢失。持久性确保了事务的持久效果，保证数据的可靠性和持久性。

这些事务的基本属性一起确保了数据库操作的可靠性、一致性和持久性。当所有的属性都满足时，可以说事务是正确执行的。数据库管理系统提供了机制来确保事务的ACID属性，以支持可靠的数据处理和并发控制。

1.3 支持事务的存储引擎

在MySQL数据库中，以下存储引擎支持事务：

InnoDB：InnoDB 是MySQL的默认和最常用的存储引擎。它提供了完整的事务支持，支持ACID属性，以及多版本并发控制（MVCC）来实现并发控制和隔离性。
NDB（也称为MySQL Cluster）：NDB是一个基于内存的存储引擎，主要用于MySQL集群环境。它支持完整的事务支持，并提供高可用性和可伸缩性。
XtraDB：XtraDB是InnoDB存储引擎的一个改进版本，它提供了更好的性能和可扩展性，并保持了InnoDB的事务支持。

除了这些常见的存储引擎，MySQL还支持其他存储引擎，如MyISAM、Memory和Archive等，但它们在事务支持方面有限或不支持事务。因此，如果需要使用事务，请确保选择支持事务的存储引擎，如InnoDB。

查看数据库的引擎：show engines\G

二、为什么要有事务

事务的存在有以下几个主要原因和重要性：

数据一致性：事务提供了一种机制，确保数据库操作的一致性。在一个事务中，要么所有的操作都成功执行，要么全部回滚。这样可以避免数据在不同操作之间出现不一致的情况，保持数据库的完整性。
并发控制：事务可以管理并发访问数据库的能力。通过并发控制机制，事务可以保证多个并发执行的事务不会相互干扰或产生不一致的结果。它提供了隔离性，确保每个事务的操作在逻辑上相互隔离，避免了数据竞争和冲突。
错误恢复：事务可以提供错误恢复的能力。如果在事务执行过程中发生错误，事务可以被回滚，撤销之前的操作，将数据库状态恢复到事务开始之前的状态。这样可以避免数据的损坏或不一致，确保系统的可靠性和可恢复性。
原子性：事务具有原子性，即事务被视为一个不可分割的原子操作。这意味着要么所有的操作都成功执行，要么全部失败回滚。原子性确保了事务的完整性，避免了只执行了一部分操作导致的数据不一致问题。
高效性：通过将多个相关的操作组合在一个事务中，可以减少与数据库的交互次数，提高效率。事务允许批量执行一组操作，减少了数据库的开销和资源消耗，提升了系统性能。

综上所述，事务是确保数据库操作的一致性、并发控制和错误恢复的关键机制。它提供了数据的一致性、可靠性和可恢复性，是建立可靠、高效的数据库应用的基础。

三、事务的基本操作

3.1 事务的提交方式

事务的提交方式主要有以下两种：

自动提交（Auto Commit）：在自动提交模式下，每个SQL语句都被视为一个独立的事务，并自动提交到数据库中。这意味着在执行每个SQL语句后，都会立即将其结果永久保存到数据库中，无需显式地执行提交语句。自动提交模式是一种默认的提交方式，在大多数数据库管理系统中，默认情况下处于自动提交模式。
手动提交（Manual Commit）：在手动提交模式下，事务的提交需要显式地执行提交语句。在手动提交模式下，通过执行提交语句（如COMMIT）将事务的操作结果永久保存到数据库中。在手动提交模式下，可以在事务执行过程中进行回滚（ROLLBACK）或提交（COMMIT）操作，具有更细粒度的事务控制。

查看事务的提交方式：

show variables like 'autocommit';

可以看到此时事务的提交方式是自动提交的。

用 SET 来改变 MySQL 的自动提交模式：

-- 关闭自动提交，及设置成手动提交
set autocommit = 0;

-- 设置事务为自动提交
set autocommit = 1;

3.2 事务的操作案例

为了便于演示，此时将 MySQL 的隔离级别设置为读未提交（Read Uncommitted），关于隔离级别，后文会有详解。

创建测试表

create table if not exists account(
	id int primary key,
	name varchar(50) not null default '',
	blance decimal(10,2) not null default 0.0
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

正常演示 — 验证事务的开始与回滚

首先使用start transaction开启事务，然后向表中插入数据，并在插入的数据之间设置savepoint保存点，通过语句rollback to point可以回滚到指定的保存点，如果直接rollback则会回滚到事务开始的状态。

非正常演示1 — 证明未commit，客户端崩溃，MySQL自动会回滚（隔离级别设置为读未提交）

终端A，使用begin开启事务，然后向表中插入一条数据，未提交事务就终止了该终端：

终端B的情况：

由于终端A的事务未提交就已经崩溃了，因此会自动回滚到事务开启前的状态。

非正常演示2 — 证明commit了，客户端崩溃，MySQL数据不会在受影响，已经持久化

终端A开启事务，插入一条数据后提交事务，然后ctrl + \终止该终端。

此时终端B可以看到该事务插入的数据，及commit提交事务了，即使客户端崩溃，MySQL数据不会在受影响，因为已经持久化了。

非正常演示3 — 对比试验。证明begin操作会自动更改提交方式，不会受MySQL是否自动提交影响

终端A关闭事务的自动提交，开启事务，插入一条数据，然后直接终止终端：

终端B的情况：

通过以上实验可以证明，手动使用begin开启事务，与是否开启了事务的自动提交没有关系。即只要显示的使用命令开启了事务，那么这就是手动事务。

非正常演示4 — 证明单条 SQL 与事务的关系

实验一：

终端A关闭事务的自动提交，执行单条SQL，然后终止终端：

此时，终端B的情况：

实验二：

终端A，关闭事务的自动提交，执行单条SQL，然后提交事务，最后终止终端：

终端B的情况：

此时，由于终端A在崩溃前已经提交事务了，终端B可以看到插入的记录，因为其已经持久化了。最后得出结论是：如果关闭事务的自动提交，那么即使执行单条SQL，也需要手动提交事务。

【总结】

只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关。
事务可以手动回滚，同时，当操作异常，MySQL会自动回滚
对于 InnoDB 每一条 SQL语言都默认封装成事务，自动提交。（ select有特殊情况，因为MySQL 有 MVCC多版本并发控制）
从上面的例子，我们能看到事务本身的原子性(回滚)，持久性(commit)
那么隔离性？一致性？
【事务操作的注意事项】
如果没有设置保存点，也可以回滚，只能回滚到事务的开始，即直接使用 rollback(前提是事务还没有提交)。
如果一个事务被提交了（commit），则不可以回退（rollback）
可以使用rollback to point选择回退到某个保存点。
InnoDB 支持事务， MyISAM 不支持事务。
开始事务可以使 start transaction 或者 begin。

四、事务的隔离级别

4.1 对事务隔离性的初步理解

简单来说，事务的隔离性就是指数据库管理系统（DBMS）可以确保在同时运行多个事务时，每个事务都能独立地执行，并且不会互相干扰。换句话说，一个事务的执行不应该影响到其他事务的执行，同时也不受其他事务的影响。

在事务隔离性中，每个事务都应该具有独立的执行空间和资源，如内存和CPU时间。这样可以避免一个事务修改了正在被其他事务使用的数据，从而导致数据的不一致性。DBMS通常通过锁定机制来实现隔离性，以确保事务之间不会互相干扰。这些锁可以是行级锁、页级锁或表级锁，具体实现取决于DBMS的架构和设计。

4.2 四种隔离级别

四种隔离级别指的是数据库管理系统（DBMS）中用于控制事务之间隔离性的四个级别，它们分别是读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

1. 读未提交（Read Uncommitted）

该隔离级别最低，允许一个事务读取其他事务尚未提交的数据。
由于没有隔离性，就会导致很多并发问题，如脏读，幻读，不可重复读等，上面为了做实验方便，用的就是这个隔离性。

2. 读提交（Read Committed）

该隔离级别要求一个事务只能读取其他已提交的事务所做的修改。
事务之间通过加锁来实现隔离，避免了脏读，但仍可能出现不可重复读（Non-repeatable Read），即多次读取同一数据得到不同的结果。

3. 可重复读（Repeatable Read）

该隔离级别要求一个事务在执行期间多次读取同一数据时，能够得到一致的结果。
事务在读取数据时会对所涉及的数据进行加锁，避免了脏读和不可重复读，但仍可能出现幻读（Phantom Read），即在同一事务内部执行相同的查询语句得到不同的结果。
该隔离级别是MySQL的默认隔离级别。

4. 串行化（Serializable）

该隔离级别要求事务串行执行，即每个事务都必须等待前一个事务完成后才能开始执行。
这是最高的隔离级别，可以避免脏读、不可重复读和幻读，但也降低了并发性能，因为事务需要串行执行。

每个隔离级别在提供隔离性的同时，也带来了不同的性能和并发控制开销。选择适当的隔离级别需要综合考虑应用的一致性需求、并发访问的频率和数据的可重复性等因素。

查看与设置隔离级别

查看隔离级别：

mysql> select @@global.tx_isolation; -- 查看全局隔离级别
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-UNCOMMITTED      |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@session.tx_isolation; -- 查看当前会话隔离级别
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@tx_isolation; -- 查看当前会话隔离级别
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)

设置隔离级别：

-- 设置当前会话 或者 全局隔离级别语法
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL 
	{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

例如：

设置当前会话隔离性，另开启一个终端登录MySQL，发现只影响当前会话。

设置全局隔离性，另开启一个终端登录MySQL，发现会影响新会话，而当前会话需要重启才会变成设置的全局隔离级别。

【注意】
设置的全局隔离级别，会在mysqld服务重启后，恢复到可重复读（Repeatable Read）级别。

以下是对各种隔离级别的实验验证

4.3 读未提交（Read Uncommitted）

终端A设置全局隔离级别为Read Uncommitted，然后重启，并开启终端B。终端A开启事务，并插入一条数据。由于隔离级别设置为了Read Uncommitted，因此开启了事务的终端B可以看到其他终端未提交的事务。

一个事务在执行中，读到另一个执行中事务的更新(或其他操作)但是未commit的数据，这种现象叫做脏读(dirty read)。

4.4 读提交（Read Committed）

设置隔离级别为Read Committed，终端A和B先后分别开启事务，可以发现在A修改数据但未提交事务之前，终端B的事务看不到修改的数据，而当终端A提交事务后，终端B的事务就能看到刚刚另一个事务修改的数据。

但是，此时终端B的事务还未提交，那么就造成了在同一个事务内进行同样的读取，在不同的时间段读取到的数据不一致，这种现象叫做不可重复读(non-reapeatable read)。

4.5 可重复读（Repeatable Read）

实验一：

设置隔离级别为Repeatable Read，终端A和B先后分别开启事务，然后终端A修改一条数据，然后再提交事务。

终端B的事务在终止A的事务修改数据的前后，以及终端A提交了事务之后分别查询一次表中的数据，发现都不能看到终端A的事务对数据的修改。而当终端B提交了自己的事务之后，就能看到了。

实验二：

设置隔离级别为Repeatable Read，终端A和B先后分别开启事务，然后终端A插入一条数据，然后提交事务。此时终端B的事务查询account表，却发现能看到终端A事务新增的数据。

因为，一般的数据库在可重复读情况的时候，无法屏蔽其他事务insert的数据。因为隔离性实现是对数据加锁完成的，而insert待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题。就会造成虽然大部分内容是可重复读的，但是insert的数据在可重复读情况被读取出来，导致多次查找时，会多查找出来新的记录，就如同产生了幻觉。这种现象，叫做幻读(phantom read)。