MySQL事务管理

​ 本篇主要将介绍MySQL中有关事务的相关知识，其中主要分为事务的操作方法，事务的隔离级别两个方面。目录如下：

CURD操作不加以控制的后果

​ MySQL是支持多用户操作数据的，假若某个时刻多个用户对同一份数据发起操作，会出现什么样的后果呢？如下：

​ 假若现在是一个售票系统，当只剩最后一张票的时候，用户A发现还有票，于是将票卖掉，然而此时系统还未刷新，当用户B来查询票数时同样显示还剩一张票，也将票卖掉，这就出现了一张票卖出了两张的情况，这样的情况明显是错误的，一张票不能卖两次。（和线程安全问题基本一致）

​ 那么CURD操作需要满足什么属性，才能解决以上问题？类比于线程安全的解决方法，如下：

​ 买票的过程需要是原子的。

​ 买票互相不能影响。

​ 买完票需要永久有效。

​ 买前买后的状态应该是确定的。

​ MySQL中的事物管理其实就是以上这种情况的解决方法。

事务

什么是事务

​ **事物就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要不全部不成功。**这一组DML语句就是为了完成某一事物而形成的一个整体，对于这样的整体而言只有成功和不成功两种状态。 同时事务还规定不同的客户端看到的数据是不相同的。

​ **事务的组成最少由一条SQL语句组成，或者由很多的SQL语句组成。**同时在一个MySQL数据库中可不止我一个事务在运行，同一时刻甚至会有大量的请求被包装成事务，若大家都使用事务访问同样的表并且不加以保护的情况下，就绝对会出现问题。同时还有可能出现执行一半出错或者不想执行的情况，这些情况又将如何处理。所以一个完整的事务绝对不是简单的SQL语句集合，还需要满足以下四个属性：

• 原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在某个中间环节，事务在执行过程中发生错误，会被回滚（rollback）到事务最开始前的状态。
• 一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏，这表示写入的资料必须完全符合所有的预设规则。（可以理解为事务结束之后的情况和预期的情况一致）
• 隔离性：数据库运行多个并发事务同时进行读写和修改的能力，隔离性可以方式多个事务办法执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。
• 持久性：事务处理结束之后，对数据的修改是永久的，即便系统故障也不会丢失。

​ 如上介绍的四个属性，简称ACID，atomicity、consistency、Isolation、Durability。

事务的出现

​ 事务被MySQL的编写者设计出来，本质是为了当应用程序访问数据库的时候，事务能够简化我们的编程模型，不需要我们去考虑各种各样的潜在错误和并发问题（我们不需要去考虑网络异常，服务器宕机，同时更改一个数据怎么办），事务的本质是为了应用层服务的，不是便随着数据库系统天生就有的。

​ 注 ：MySQL中的一行信息，也被称为一条记录。

事务的版本支持

​ 查看MySQL中存在哪些搜索引擎支持事务，如下：

mysql> show engines\G
*************************** 1. row ***************************
      Engine: InnoDB-- 引擎名称
     Support: DEFAULT-- 默认引擎
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES-- 支持事务
          XA: YES
  Savepoints: YES-- 支持事务保持点
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO-- MyISAM不支持事务
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

​ 如上所示，MySQL中只有InnoDB数据库引擎或表才支持事务，MyISAM不支持。

事务提交方式

​ 事务的提交方式常见的有两种：手动提交和自动提交。

​ 查看事务提交方式：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |-- ON表示开启
+---------------+-------+
1 row in set (0.00 sec)

​ 用set改变MySQL的自动提交模式：

mysql> set autocommit=0; -- 0表示关闭 1表示开启
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |-- OFF表示关闭
+---------------+-------+
1 row in set (0.00 sec)

事务常见操作方式

​ 如下将演示MySQL事务中的常见操作方式，将会在注释中给出解释：

​ 正常情况下的事务：

mysql> 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;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like 'autocommit';-- 查看是否自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> start transaction;-- 开始一个事务，begin也可以开始一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint save1;-- 设置一个保存点
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (1, '张三', 100);-- 插入一条数据
Query OK, 1 row affected (0.00 sec)

mysql> savepoint save2;-- 另外设置一个保存点
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (2, '李四', 10000);-- 在插入一条数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;-- 查询数据，查到两条
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> rollback to save2;-- 回滚到save2保存点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;-- 查询数据，在save2之后插入的数据没了
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> rollback to save1;-- 回滚到save1保存点
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;-- 全部数据都无了
Empty set (0.00 sec)

​ 异常情况下的事务1：

mysql> select * from account;-- 表为空
Empty set (0.00 sec)

mysql> show variables like 'autocommit';-- 自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> begin;-- 开始一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (1, '张三', 100);-- 插入一条数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;-- 查询出一条数据
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> Aborted-- Ctrl + \ 终止mysql

mysql> select * from account;-- 重新进入之后查询不到数据
Empty set (0.00 sec)

​ 如上操作证明了未commit，客户端崩溃，MySQL数据会回到事务开始前。

​ 异常情况下的事务2：

mysql> show variables like 'autocommit';-- 依旧自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from account;-- 查询空表格
Empty set (0.00 sec)

mysql> begin;-- 开始一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (1, '张三', 100);-- 插入数据
Query OK, 1 row affected (0.00 sec)

mysql> commit;-- 提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> Aborted-- Ctrl + \异常退出

mysql> select * from account;-- 重新进入依旧能够找到数据
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

​ 如上证明了，只要commit提交了，即使MySQL异常退出了，也不会影响到数据库中存入的数据，体现了事务的持久性的特性。

​ 异常情况下的事务3：

-- 对照实现一
mysql> show variables like 'autocommit';-- 当前自动提交开启
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set autocommit = 0;-- 关闭自动提交
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account values (2, '李四', 10000);-- 插入一条数据
Query OK, 1 row affected (0.00 sec)

mysql> select *from account;-- 数据插入成功
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> Aborted-- 异常退出

mysql> select *from account;-- 重新进入，发现回滚到了插入数据前
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

-- 对照实验二
mysql> select *from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> show variables like 'autocommit';-- 自动提交开启
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> insert into account values (2, '李四', 10000);-- 插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select *from account;-- 查询数据
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

mysql> Aborted-- 异常退出

mysql> select *from account;-- 重新进入，没有回滚，这是因为自动commit了
+----+--------+----------+
| id | name   | blance   |
+----+--------+----------+
|  1 | 张三   |   100.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

​ 如上所示的两个对照实验我们可以看出，一条SQL语句也可以是事务，当我们将自动commit关闭之后，一条SQL语句执行之后且MySQL异常退出导致SQL语句执行失败，自动commit开启之后，一条的SQL语句就可以执行成功。（所以可以得出一条SQL语句也是一个事务，若将自动commit关闭，只要异常退出，单条插入的SQL语句都会失败）

​ 通过以上的事务距离，我们可以得出：

• 只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关。
• 事务可以手动回滚，同时，当操作异常，MySQL会自动回滚。
• 对于InnoDB每一条SQL语言都默认封装成事务，自动提交（select较为特殊，之后会介绍其原因）。

​ 事务操作的注意事项：

• 若没有设置保存点，也可以回滚，但是只能回滚到事务的开始，直接使用rollback回到事务开始。
• 若事务被提交了，则不可以回滚。
• 可以选择回退到哪个保存点。
• InnoDB支持事务，MyISAM不知此。
• 开始事务可以使用begin或start transaction。

事务隔离级别

​ MySQL服务可能会同时被多个客户端进程（线程）访问，访问的方式是以事务的方式进行，一个事务又是由多条SQL语句构成，也就意味着事务的会存在执行前，执行中，执行后三种阶段，然而对于事务的要求就是只能存在执行前和执行后两种状态，这也就要求事务的执行是原子性的。

​ 即使在原子性的，也会出现多个事务访问同一张表甚至同一行数据的情况，也就是执行事务的时候受到了干扰，所以为了保证事务执行过程中尽量不受干扰，就需要存在一个重要的特征：隔离性。同时MySQL允许事务受不同程度的干扰，就出现了隔离级别。

​ 隔离级别：

• 读未提交（read uncommitted）：在该隔离级别，所有的事物都可以看到其他事务没有提交的执行结果。（实际开发常见中不可能使用这种隔离级别）相当于没有使用任何的隔离措施。
• 读提交（read committed）：该隔离级别是大多数数据库的默认隔离级别（MySQL不是这个隔离级别），它满足了隔离的简单定义：一个事务只能看到其他的已经提交的事务所做的改变，这种隔离级别会引起不可以重复读问题（一个事务执行时，多次使用select可能得到不同的结果，之后将会解释出现这种问题的原因）
• 可重复读（repeatable read）：MySQL使用的默认隔离级别，确保同一个事务在执行中，多次读取操作数据时，看到的数据行都是一样的，但是也会出现幻读的现象（下文会解释）。
• 串行化（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}

-- session表示设置的是当前会话的隔离级别
-- global表示设置的是全局会话的隔离级别

​ 如下，将当前会话设置为serializable隔离级别的会话：

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@session.tx_isolation;                     
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE           |-- 显示SERIALIZABLE的隔离级别
+------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set, 1 warning (0.00 sec)

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

​ 若只修改当前会话的隔离性不会影响到全局的隔离性，但是假若我们修改了全局的隔离性，那么之后重新开启的MySQL客户端的隔离性都会和设置的全局隔离性一样，当前会话的隔离性不会改变（关闭后重新开启则改变）。如下：

mysql> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

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

-- 当重新连接一个MySQL客户端，其隔离级别就会和全局隔离级别一致

读未提交[Read Uncommitted]

​ 该隔离级别几乎没有加锁，效率虽然高，但是问题太多，不建议使用该隔离级别。如下：

​ 当一个事务在执行中，读到另一个执行中事务的CURD操作且未commit的数据，这种现象叫做脏读。

读提交[Read Committed]

​ 读提交也就是之后当某个连接MySQL的客户端对事务进行commit之后，其他客户才可以读到该客户端修改的数据，如下：

​ 如上所示，在read committed隔离级别下，想要读取别人修改的数据，只有等待他commit之后才能读到。

​ 但是同时我们也发现一个问题，在右边的MySQL终端下，同样开启了一个事务，该事务在commit前既可以读取出别人commit前的数据，也可以读取出别人commit后的数据，也就意味着，同样的读取，在不同的时间段，读到了不同的值，这种现象叫做不可以重复读。

​ 既然出现了不可重复读现象，也就是不同时段读到不同的值，那么很可能会因为读到不同的值而导致决策出现问题，导致事故的产生。所以对于一种更加严格的读取方式应该为：当一个客户端commit数据之后，其他客户端（与这个客户端并行执行的客户端）未commit之前，是不能看到客户端commit之后的数据的。

​ 一种常见因为读提交出现的事故：依据当前查看到的数据进行后续的决策（但同时其他客户端也在对数据进行修改，且修改的是同一个数据，其他客户端还在修改时未commit时），根据查出的数据进行决策，当决策完毕之后让员工依据数据去执行的时候（其他客户端commit了），执行的数据就不一致了，也就很可能与预期的执行结果不同。

可重复读[Repeatable Read]

​ 这种隔离级别就是读提交隔离级别对应的解决方法，可重复读也就是当在执行一个事务中的时候，只能看到执行事务前的信息，在执行事务中的这段事件中发送的数据改变，什么也看不见。可重复读隔离级别简称RR隔离级别。如下：

​ 如上图这样的形式，在可重复读的隔离性级别下，安全性提高了不少。但即使是这样，也存在一些特殊的情况，那就是幻读（由于我使用的MySQL5.7已经解决了幻读的问题，所以便不给出示例了）：

​ 当我们在RR隔离级别下，无法屏蔽其他事务insert的数据，这是因为隔离性的实现是对数据加锁完成的，而insert待插入的数据因为并不存在，那么一般加锁无法屏蔽这类问题。

​ 这也就造成了大部分数据是可重复读的，但是insert的数据在可重复读情况被读取出来，导致多次查找时，会查找出新的记录，就如同产生了幻觉，这种现象被称为幻读。

串行化[Serializable]

​ 串行化的隔离级别为最高的隔离级别，同时也是最安全的隔离级别，因为事务的执行是串行化的，效率很低，所以这样的隔离级别和Read Uncommitted级别一样，基本不会在开发中使用。

​ 如上所示，在Serializable隔离级别下，同时开启一个事务之后，使用select语句不会出现阻塞，但是当我们使用update、insert、delete语句的时候，就会出现阻塞，因为这个时候只允许一个事务在运行，这时却有两个事务，必须等待右边的事务commit之后左边的update语句才能执行。

隔离级别总结

​ 对于隔离级别的总结如下：

• 其中隔离级别越严格，安全性越高，但数据库的并发性能也就越低，往往需要在两者之间找到一个平衡点。
• 不可重复读的重点是修改和删除：同样的条件下，读取过的数据，再次读取出来发现值不一样了。
• 幻读的重点在于新增：同样条件下，第一次和第二次读取出来的记录数据不一样。
• MySQL默认的隔离级别是可重复读，一般情况下不要修改。
• 事务也有长短之分，事务间互相影响，指的是事务在并发执行的时候，即都没有commit的时候，影响会比较大。

隔离级别	脏读	不可重复读	幻读	加锁读
Read Uncommitted	√	√	√	不加锁
Read Committed	×	×	√	不加锁
Repeatable Read	×	×	×	不加锁
Serializable	×	×	×	加锁

一致性

​ 事务执行的结果，必须使得数据库从一个一致性状态转变到另一个一致性状态。当数据库只包含事务成功提交的结果时，数据库处于一致性状态，若系统运行发生中断，某个事务尚未完成而被迫中断，而改未完成的事务对数据库所做的的修改已经被写入数据库，此时数据库就处于一种不正确（不一致）的状态。因此一致性是通过原子性来保证的。

​ 一致性与用户的业务逻辑相关，MySQL只是提供相关的技术支持，所以一致性其实是由原子性保证的。

原文地址：https://blog.csdn.net/m0_74830524/article/details/143533321

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！