一、概念:
当开启一个事务后,在没提交或回滚的情况下,执行了某些操作,会触发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、触发隐式提交的语句有很多