mysql高级-事务的隐式提交

一、概念：

当开启一个事务后，在没提交或回滚的情况下，执行了某些操作，会触发mysql的隐式提交（自动提交）；

-- 语法：savepoint  指定保持点名称
set autocommitted = 0;                                 关闭自动提交
begin;                                                 开启事务
insert into  admin(id,name,age)value(1,'张三',20);     执行sql
savepoint s1;                                          设置保存点
insert into  admin(id,name,age)value(2,'李四',30);     再执行sql
savepoint s2;                                          设置保存点
insert into  admin(id,name,age)value(3,'王五',40);     再执行sql
savepoint s3;                                          设置保存点
rollback to savepoint  s2;                             回滚到某个保存点；

二、哪些操作会触发隐式提交？

1、DDL语句(创建表、修改表、删除表等)；

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

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t_account;
+----+-------------+----------+------------------+-------------+--------+---------+---------------------+---------------------+
| id | user_name   | password | email            | mobile      | enable | deleted | created_time        | updated_time        |
+----+-------------+----------+------------------+-------------+--------+---------+---------------------+---------------------+
|  1 | Zhucl123123 | 123456   | 250596243@qq.com | 18980440320 | 1      | 0       | 2024-06-18 16:08:50 | 2024-06-18 16:08:47 |
+----+-------------+----------+------------------+-------------+--------+---------+---------------------+---------------------+
1 row in set (0.03 sec)

mysql> UPDATE t_account SET user_name='Zhucl' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> DROP TABLE IF EXISTS `t_order`;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM t_account;
+----+-----------+----------+------------------+-------------+--------+---------+---------------------+---------------------+
| id | user_name | password | email            | mobile      | enable | deleted | created_time        | updated_time        |
+----+-----------+----------+------------------+-------------+--------+---------+---------------------+---------------------+
|  1 | Zhucl     | 123456   | 250596243@qq.com | 18980440320 | 1      | 0       | 2024-06-18 16:08:50 | 2024-06-18 16:08:47 |
+----+-----------+----------+------------------+-------------+--------+---------+---------------------+---------------------+
1 row in set (0.04 sec)

注意：
当一个会话设置自动提交=false，开启事务，执行update语句，不提交事务；
另外再开启一个会话，设置自动提交，开启事务，执行DDL语句，也会触发前面一个会话的隐式提交；

会话一：

会话二、

2、在事务里面嵌套事务，如
 

set autocommit=0;
begin;
UPDATE t_account SET user_name='Zhucl' WHERE id=1;

...
begin;        -- 会触发隐式提交，将前面的所有事务进行提交

3、加载数据的语句：
load data这种

4、触发隐式提交的语句有很多