【MySQL 保姆级教学】事务的自动提交和手动提交（重点）--上（13）

1. 什么是事务？

举一个抢火车票的例子：

场景一：
当火车票还剩下一张，三个人同时抢票，客户端1强到票了（数据库中的票数变为0），当客户端1进行付钱时突然系统崩溃了，客户端1再次进入到付钱的页面时付钱的时间已经结束了，但是票没有恢复到数据库中，数据库中的票数一直显示为0，系统崩溃一次导致一张车票消失了，三个人都买不了票了。

场景二：
当火车票还剩下一张，三个人同时抢票，客户端1强到票了（数据库中的票数变为没有变为0），当客户端1进行付钱时网络不好，此时客户端2抢到了票并付了钱，此时的数据库票数为0，但是客户端2的网络变好后在付款界面付了钱。这张票算谁的呢？

CURD满足什么属性，能解决上述问题？

1. 买票的过程是一个单独的整体
2. 个人买票的时候不能收其他人影响
3. 买完票应该要永久有效
4. 买前，和买后都要是确定的状态

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

事务就是要做的或所做的事情，主要用于处理操作量大，复杂度高的数据。假设一种场景：你正在注册一个银行账户，你需要在一个页面填入你的姓名、年龄、性别、身份证号等等，在数据库后台中，就需要多条MySQL 语句构成，那么所有这些操作合起来，就构成了一个事务。

正如我们上面所说，一个MySQL 数据库，可不止你一个事务在运行，同一时刻，甚至有大量的请求被包
装成事务，在向MySQL 服务器发起事务处理请求。而每条事务至少一条SQL ，最多很多SQL ,这样如果大
家都访问同样的表数据，在不加保护的情况，就绝对会出现问题。

甚至，因为事务由多条SQL 构成，那么，也会存在执行到一半出错或者不想再执行的情况，那么已经执行的怎么办呢？

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

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

为什么会出现事务 ？
事务被MySQL 编写者设计出来,本质是为了当应用程序访问数据库的时候,事务能够简化我们的编程，不需要我们去考虑各种各样的潜在错误和并发问题。可以想一下当我们使用事务时,要么提交,要么回滚,我们不会去考虑网络异常了，服务器宕机了，同时更改一个数据怎么办对吧?

因此事务本质上是为了应用层服务的，而不是伴随着数据库系统天生就有的。
备注：我们后面把MySQL 中的一行信息，称为一行记录

2. 事务的版本支持

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

查看数据看引擎：

# 查看数据库引擎，以表格的方式显示
show engines; # 查看数据库引擎，以行的方式显示
show engines\G

以表格的方式显示：

3. 事务提交的方式

3.1 事务提交方式的分类

1. 按照提交机制分类

• 显式提交：用户或应用程序显式地发出提交命令（如SQL语句COMMIT;），以结束事务并使事务所做的更改成为永久性的。
• 隐式提交：数据库系统在满足某些条件时自动提交事务。例如，在执行某些DDL（数据定义语言）语句（如CREATE TABLE, ALTER TABLE等）之后，数据库可能会自动提交当前事务。

2. 按照事务控制方式分类

• 自动提交（Auto-commit）：在自动提交模式下，每个单独的SQL语句都被视为一个独立的事务，执行完后会立即提交。这意味着每个SQL语句都会立即生效，无需显式地开始和结束事务。自动提交模式适合于那些不需要将多个操作组合成单个事务的情况，例如简单的查询和插入操作
• 手动提交（Manual commit）：手动提交模式要求应用程序显式地开始和结束事务。在这种模式下，用户需要使用BEGIN TRANSACTION;（或类似的命令）开始事务，使用COMMIT;提交事务，或使用ROLLBACK;回滚事务。这种方式提供了更大的灵活性，适用于需要将多个操作组合成一个逻辑单元的情况，确保这些操作要么全部成功，要么全部失败。

同学们可能此时有点晕，不用着急，经过下面的例子后思路会慢慢清晰。

3.2 演示的准备的工作

3.2.1 创建表

创建一个表：

create table students(id int primary key,name varchar(10),age tinyint(1)) engine = innodb;

3.2.2 MySQL的服务端和客户端

模拟多个用户同时访问数据库，这里我用两个命令行客户端与服务端建立连接。

MySQL由服务端（Server）和客户端（Client）两部分组成，这两者通过网络进行通信，共同完成数据库的各种操作。

建立连接：
客户端向服务端发起连接请求，指定服务端的 IP 地址和端口号。
服务端接收到请求后，进行身份验证，验证通过后建立连接。

发送请求：
客户端发送 SQL 语句或其他命令到服务端。
服务端解析收到的请求，执行相应的操作。

给同学们看看MySQL的客户端和服务端在哪：
命令：

# 查看MySQL客户端
ls /usr/bin/mysql# 查看MySQL服务端
ls /usr/sbin/mysqld

以上我们可知，MySQL其实是一套网络服务，底层采用tcp协议

MySQL是一套网络服务，那么就相当于一个远程的服务器
我们可以使用很多台远端机器连接正在运行着的MySQL服务器。
比如，你同学的电脑上运行着MySQL，你可以使用自己的电脑连接他的MySQL进行操作。

这次示例中我用两个客户端连接一台MySQL服务器：
左侧位客户端1，右侧为客户端2.

3.2.3 调低事务的隔离级别

默认的隔离级别太高了，我们是为了研究事务，研究事务就要研究多个客户端并发的现象。把隔离级别调成最低，在一个客户端更改数据后，在另一个客户端就能观察到现象。这样能很清楚的看到双方的事务在交叉时带来的问题。

为什么要修改隔离级别的具体原因暂时不讲，当学到事务隔离级别的时候自然会明白。

修改隔离级别为 读不提交（read uncommit）：

set global transaction isolation level read uncommmitted;

创建隔离级别后重新登陆客户端1和客户端2。

查询隔离级别：

select @@tx_isolation;

查看客户端的登录状态

show processlist;

此时用两个客户端在登录。

4. 手动提交

4.1 手动提交的命令说明

1. 开始事务：

   # 命令1
   start transaction;#命令2
   begin;
   

   开始一个新的事务。在执行这条命令之前，如果有未提交的事务，MySQL 会自动提交当前事务。

2. 提交事务（commit）

   commit;
   

   提交当前事务，将所有对数据库的更改永久保存。

3. 保存点（savepoint）

   savepoint savepoint_name;
   

   在事务中设置一个保存点。保存点可以让你在事务中部分回滚到某个点，而不是回滚整个事务。

4. 回滚事务（rollback）

   # 回滚到事务开始
   rollback;# 回滚到某一点
   rollback to savepoint_name;
   

   回滚当前事务，撤销所有对数据库的更改。

5. 撤销保存点

   RELEASE SAVEPOINT savepoint_name;
   

   释放指定的保存点。一旦释放，就不能再回滚到该保存点。

4.2 示例一

场景一：

开始事务，
执行命令，创建节点，
执行命令，创建节点，
rollback to 回滚到某一保存点
提交事务

示例：

1. 在客户端1开始事务，并插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(1, '李明', 18);
   # 创建保存点
   savepoint p1;# 客户端2
   select * from students;
   

   

   当一个客户端操作数据后，在另一个客户端可以立即看到。

2. 在客户端1插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(2, '诸葛亮', 20);
   # 创建保存点
   savepoint p2;# 客户端2
   select * from students;
   

   

3. 在客户端1回滚到保存点p2；然后在另一个客户端查询数据

   # 客户端1
   rollback to p1;# 客户端2
   select * from students;
   

   
   同学们可以发现，查询出的数据变少了，在p1以下执行的命令被撤销了。

4. 在客户端1提交事务；然后在客户端2查询数据

   # 客户端1
   commit;# 客户端2
   select * from students;
   

   
   同学们可以发现，当客户端1提交事务后，客户端2查询的数据没有发生改变，当客户端2提交事务后，查询的数据还是没发生改变。

总之，事务是单个命令的的集合体；回滚到某一保存点时保留该保存点以上的数据，撤销该保存点以下的数据；提交事务后，对数据的改变已做永久性保存。

4.3 示例二

场景二：
开始事务，
执行命令，创建节点，
执行命令，创建节点，
rollback；
提交事务

1. 在客户端1开始事务，并插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(3, '李刚', 18);
   # 创建保存点
   savepoint p1;# 客户端2
   select * from students;
   

2. 在客户端1开始事务，并插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(4, '白居易', 19);
   # 创建保存点
   savepoint p2;# 客户端2
   select * from students;
   

3. 在客户端1回滚；然后在客户端2查询数据

   # 客户端1
   rollback;# 客户端2
   select * from students;
   

同学们可以发现，使用rolback;命令直接回滚到事务开始出，撤销所有的事务操作。

那么，提交事务后仍然是这样吗？
答：是的，回滚后撤销的操作不可以恢复。

# 客户端1
commit;# 客户端2
select * from students; 

4.4 示例三

场景三：
开始事务，
执行命令，创建节点，
执行命令，创建节点，
提交事务
rollback；

1. 在客户端1开始事务，并插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(3, '李刚', 18);
   # 创建保存点
   savepoint p1;# 客户端2
   select * from students;
   

   

2. 在客户端1开始事务，并插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(4, '白居易', 19);
   # 创建保存点
   savepoint p2;# 客户端2
   select * from students;
   

   

3. 在客户端1提交事务；然后在客户端2查询数据

   # 客户端1
   commit;# 客户端2
   select * from students;
   

   

4. 在客户端1回滚；然后在客户端2查询数据

   # 客户端1
   rollback;# 客户端2
   select * from students;
   

   
   同学们可以发现，提交事务后再回滚是没有作用的，提交事务后，对数据的改变已做永久性保存。

4.5 示例四

场景四：
开始事务，
执行命令，创建节点，
执行命令，创建节点，
客户端崩溃（CTRL + D 模拟崩溃）

1. 在客户端1开始事务，并插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(5, '李世民', 19);
   # 创建保存点
   savepoint p1;# 客户端2
   select * from students;
   

   

2. 在客户端1开始事务，并插入数据，创建保存点；然后在客户端2查询数据

   # 客户端1
   insert into students values(6, '程咬金', 20);
   # 创建保存点
   savepoint p2;# 客户端2
   select * from students;
   

3. 在客户端1模拟崩溃；然后在客户端2查询数据

   # 客户端1
   `CRRL + D`# 客户端2
   select * from students;
   

   
   步骤3模拟的是客户1端崩溃，同学们可以发现，当客户端1在事务中崩溃后，客户端2查询不到该事务操作的数据。也就是说，当客户端在事务操作中崩溃后，会撤销该事务的所有操作，即回滚到该事务的开始。

5. 自动提交

5.1 开启自动提交

自动提交不需要任何的手动，最开始学习时写的SQL命令就是自动提交的。当我们不手动写begin/commit时，写的每一个SQL命令也都是事务，只不过写什么就提交什么。

1. 开启自动提交（MySQL数据库默认开启）

   set autocommit = 1;
   

2. 查询自动提交是否开启

   select @@autocommit;
   

   
   1表示开启，0表示关闭。

示例：

1. 客户端1正常的往表格中插入数据，然后客户端2查询数据

   # 客户端1
   insert into students values(1,'李明', 18);# 客户端2
   select * from students; 
   

   
   当你正常使用 MySQL 并且不手动使用事务时，MySQL 会默认处于自动提交模式。这意味着每个单独的 SQL 语句都会立即提交，而不是等待显式的提交命令。

2. 客户端1正常的往表格中插入数据，然后客户端2查询数据，当客户端1崩溃后，客户端再次查询数据

   # 客户端1
   insert into students values(5,'李世民', 19);# 客户端2
   select * from students; # 客户端1,模拟崩溃
   `CTRL + D` 
   

   
   我们可以发现，使用自动提交的时候，当客户端1崩溃后，进行操作的数据是不会丢失的，在客户端2依然能查询到已经操作的数据。

5.2 关闭自动提交

当关闭自动提交（autocommit）后，你需要显式地使用 COMMIT 命令来提交事务。这样可以确保你的更改被永久保存到数据库中。如果没有显式地提交事务，那么在事务中的所有更改都不会被保存，直到你显式地提交或回滚事务。

1. 关闭自动提交（MySQL数据库默认开启）

   set autocommit = 0;
   

2. 查询自动提交是否开启

   select @@autocommit;
   

   

示例：

客户端1正常的往表格中插入数据，然后客户端2查询数据

# 客户端1
insert into students values(6,'程咬金', 20);# 客户端2
select * from students; 

此时插入的数据在客户端2可以查询到，但是真的是永久保存下来了吗？
答：不是

模拟客户端1崩溃，然后用客户端2查询

# 客户端1
`CTRL + D`# 客户端2
select * from students;

同学们可以发现，当客户端1崩溃后，在客户端2就查询不到操作的数据了。
这是因为关闭自动提交后，每次的操作都不会是永久的保存，只用使用commit才可以永久的保存下来。

插入刚才没有成功插入的数据：

# 客户端1
insert into students values(6,'程咬金', 20);
commit;
`CTRL + D`# 客户端2
select * from students; 

当客户端1插入数据后并执行commit后，客户端1崩溃，但是在客户端2依然可以查询到插入的操作。事务提交commit让数据永久的保存了下来。

6. 手动提交和自动提交的关系

我在演示手动提交的实验时开启了自动提交，

但是，

自动提交 autocommit=1/0 不会影响手动事务提交（begin/commit），自动提交 与 手动事务提交没有任何关系。

换言之：
如果不手动使用begin，当autocommit=1时自动提交就会起作用，执行一个语句就会提交；当autocommit=0时自动提交就会关闭，执行一个语句需要显示提交，使用commit提交事务。

如果手动使用begin，自动提交就会不起作用，当执行命令begin时，未手动执行commit之前，事务就不会提交。

总之，系统遵循手动优先，自动为辅。

当设置 set autocommit = 0，重复上述手动事务的实验，可以发现实验结果和autocommit=1的结果完全一致。

那么autocommit开启和关闭作用是什么呢？

答：当关闭autocommit时，执行命令后必须显示使用commit提交，否则操作的数据不会永久性保存到数据库，即当客户崩溃后之前执行的所有命令都会失效；当开启autocommit时，执行命令后不必手动commit，因为每当执行命令后都会默认自动提交事务。

其实，我们执行的所有命令（包括不使用begin/commit的命令）都是事务，人为创建的事务需要使用命令begin/commit，平常的命令只不过没有显示出命令begin/commit，但也是事务。

总结：
系统遵循手动优先，自动为辅。自动提交执行的前提条件是没有手动执行事务的开始。