[Mysql从入门到精通] 一篇文章让你了解Mysql--事务管理

我们以售票的情景举例，买票分为两个步骤，先检查是否还有票，在买票，现在假设从西安到兰州的火车票还存在一张，当一个客户端检测完还有票，准备买票但是还没有买时（准备执行票数--的操作），另一个客户端此时也想买票，由于上一个客户端此时还没有让票数减为0，他一可以买票，这样就出问题了，一张票被卖给了两个人，这就造成了数据库数据不一致的问题，这个问题类似于多线程同时访问临界资源。由于Mysql的本质就是一个网络服务，他可能会被多个客户端同时访问，就有可能会出现类似上述导致数据不一致的问题，为了解决上述的问题，Mysql就设计出了事物的机制。

1.2 什么是事务？

事务就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体。MySQL提供一种机制，保证我们达到这样的效果。事务还规定不同的客户端看到的数据是不相同的。

事务就是要做的或所做的事情，主要用于处理操作量大，复杂度高的数据。假设一种场景：你毕业了，学校的教务系统后台 MySQL 中，不在需要你的数据，要删除你的所有信息, 那么要删除你的基本信息(姓名，电话，籍贯等)的同时，也删除和你有关的其他信息，比如：你的各科成绩，你在校表现，甚至你在论坛发过的文章等。这样，就需要多条 MySQL 语句构成，那么所有这些操作合起来，就构成了一个事务。

正如我们上面所说，一个 MySQL 数据库，可不止你一个事务在运行，同一时刻，甚至有大量的请求被包装成事务，在向 MySQL 服务器发起事务处理请求。而每条事务至少一条 SQL ，最多很多 SQL ,这样如果大家都访问同样的表数据，在不加保护的情况，就绝对会出现问题。甚至，因为事务由多条 SQL 构成，那么，也会存在执行到一半出错或者不想再执行的情况，那么已经执行的怎么办呢？

所有，一个完整的事务，绝对不是简单的 sql 集合，还需要满足如下四个属性：

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable ）
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

上面四个属性，可以简称为 ACID

原子性（Atomicity，或称不可分割性）

一致性（Consistency）

隔离性（Isolation，又称独立性）

持久性（Durability）。

1.3 为什么会出现事务

事务被 MySQL 编写者设计出来,本质是为了当应用程序访问数据库的时候,事务能够简化我们的编程模型, 不需要我们去考虑各种各样的潜在错误和并发问题.可以想一下当我们使用事务时,要么提交,要么回滚,我们不会去考虑网络异常了,服务器宕机了,同时更改一个数据怎么办对吧?因此事务本质上是为了应用层服务的.而不是伴随着数据库系统天生就有的.

注意：在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务， MyISAM 不支持。

二、了解事务

2.1 事务的提交方式

事务的提交方式常见的有两种：

自动提交
手动提交

查看事务提交方式：

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

我们可以用 SET 来改变 MySQL 的自动提交模式:

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

2.2 事务常见操作方式

为了便于后续实验的效果展示，我们先将隔离级别设置成最低，隔离级别在下面讲

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

//设置完后配置不会立即生效，我们需要重启一下mysql服务才会生效
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> quit;
Bye
root@iZ0jlcvs1yxxmicafyy3kbZ:~# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 394
Server version: 8.0.39-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

创建测试表

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;

正常演示 - 证明事务的开始与回滚

上面我们讲了事务具有原子性，要么成功要么失败，如果这个事务的SQL语句只执行了一半，就异常退出了，我们就要求事务有回滚的能力，接下来展示一下这个能力

客户端1：

mysql> show variables like 'autocommit';  -- 查看事务是否自动提交。我们故意设置成自
动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit   | ON   |
+---------------+-------+
1 row in set (0.00 sec)
//开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
//设置保存点
mysql> savepoint s1;
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 s2;
Query OK, 0 rows affected (0.00 sec)
//插入数据
mysql> insert into account values (2, '李四', 10000);
Query OK, 1 row affected (0.00 sec)
//回滚到s2的保存点
mysql> rollback to s2;
Query OK, 0 rows affected (0.00 sec)
//直接回滚到事务开始
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

客户端2：

//开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
//第一次插入时查询
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 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)
//回滚到s2时查询
mysql> select * from account;
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)
//回滚到开头查询
mysql> select * from account;
Empty set (0.00 sec)

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

-- 终端A
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;           --数据已经存在，但没有commit，此时同时查看
终端B
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)
mysql> Aborted                          -- ctrl + \ 异常终止MySQL 

--终端B
mysql> select * from account;           --终端A崩溃前
+----+--------+--------+
| id | name   | blance |
+----+--------+--------+
|  1 | 张三   | 100.00 |
+----+--------+--------+
1 row in set (0.00 sec)

mysql> select * from account;          --数据自动回滚
Empty set (0.00 sec)

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

--终端 A
mysql> show variables like 'autocommit'; -- 依旧自动提交
+---------------+-------+
| Variable_name | Value |
+---------------+-------