MySQL8.0 SAVEPOINT

最新推荐文章于 2023-07-27 15:09:49 发布
最新推荐文章于 2023-07-27 15:09:49 发布
阅读量336 收藏
点赞数 1
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jiaona_chen123/article/details/115522214
版权

MySQL8.0 SAVEPOINT调研文档

1.概述

1.1 环境

系统环境ubuntu20.04
软件环境mysql8.0.23
用户名root
密码greatdb

1.2 savepoint定义

savepoint语法:

savepoint point_xxx;
rollback to point_xx;
release savepoint point_xxx;
  • savepoint

创建一个名为point_xxx的保存点,如果当前事务具有相同的保存点名称,则将删除旧的保存点并设置新的保存点.设置保存点是在当前事务,并不会提交事务.

  • rollback to xxx;

回滚到指定的保存点,不终止事务 .如果不指定保存点名称,默认会回到离本次最近的保存点.回滚指定的保存.

  • release savepoint point_xxx;

删除保存点,从当前事务保存点集中删除名为xxx的保存点.

1.3 举例说明

savepoint举例

举例:

-- 准备数据
CREATE TABLE if not EXISTS  `emp` (
  `EMPNO` int(4) UNSIGNED PRIMARY KEY   NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) UNSIGNED  DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` DECIMAL(7,2) DEFAULT NULL,
  `COMM` DECIMAL(7,2) DEFAULT NULL,
  `DEPTNO` int(2) UNSIGNED DEFAULT NULL);

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300',null,'20');
  • 创建表,构造数据
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
  • 操作过程: 关闭事务自动提交,开启事务,更新empno为7698行的SAL字段,更新之后为4000.00,创建事务保存点point_update7698,再创建一条新的记录(7700,ANDY,TEACHER,NULL,2021-04-07,5000.00,NULL,20);执行完成后,回到point_update7698,观察数据
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update emp set sal=4000 where empno=7698;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> insert into emp values ('7700',"ANDY","TEACHER",NULL,'2021-04-07',5000.00,NULL,20);
Query OK, 1 row affected (0.00 sec)

mysql> rollback to point_update7698;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 4000.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
设置两个相同的保存点
  • 创建表,构造数据
mysql> select  * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
  • 操作过程:关闭事务自动提交,开启事务,更新empno为7698行的SAL字段,更新之后为4000.00,创建事务保存点point_update7698,再创建一条新的记录(7700,ANDY,TEACHER,NULL,2021-04-07,5000.00,NULL,20);,创建事务保存点poing_insert7700,删除empno为7782的一行数据,并修改empno为7698的deptno列为10,创建保存点poing_update7698.再次插入一行数据(7777,‘AMI’,‘QA’,NULL,‘2021-04-07’,8000.00,NULL,20); rollback 到point_update7698.
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update emp set sal=4000 where empno=7698;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> insert into emp values ('7700',"ANDY","TEACHER",NULL,'2021-04-07',5000.00,NULL,20);
Query OK, 1 row affected (0.00 sec)

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

mysql> delete from emp where empno=7782;
Query OK, 1 row affected (0.00 sec)

mysql> update emp set deptno=10 where empno=7698;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint point_update7698;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into emp values ('7777','AMI','QA',NULL,'2021-04-07',8000.00,NULL,20);
Query OK, 1 row affected (0.00 sec)

mysql> rollback to point_update7698;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 4000.00 |    NULL |     10 |
|  7700 | ANDY   | TEACHER   | NULL | 2021-04-07 | 5000.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
4 rows in set (0.00 sec)
releasepoint
release掉第一个保存点
  • 创建表,构造 数据
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
  • 操作过程: 关闭事务自动提交,开启事务,更新empno为7698行的SAL字段,更新之后为4000.00,创建事务保存点point_update7698,再创建一条新的记录’7777’,‘AMI’,‘QA’,NULL,‘2021-04-07’,8000.00,NULL,20,创建事务保存点poing_insert7700,删除empno为7782的一行数据,删除point_update7698 savepoing,再rollback 到poing_insert7700,观察数据
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> update emp set sal=4000 where empno=7698;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> insert into emp values ('7777','AMI','QA',NULL,'2021-04-07',8000.00,NULL,20);
Query OK, 1 row affected (0.00 sec)

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

mysql> delete from emp where empno=7782;
Query OK, 1 row affected (0.00 sec)

mysql> release savepoint point_update7698;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback to point_update7698;
ERROR 1305 (42000): SAVEPOINT point_update7698 does not exist
mysql> rollback to point_insert;
ERROR 1305 (42000): SAVEPOINT point_insert does not exist
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 4000.00 |    NULL |     30 |
|  7777 | AMI    | QA        | NULL | 2021-04-07 | 8000.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
release掉当前的保存点
  • 创建表,构造数据
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
  • 操作过程:关闭事务自动提交,开启事务,更新empno为7698行的SAL字段,更新之后为4000.00,创建事务保存点point_update7698,再创建一条新的记录’7777’,‘AMI’,‘QA’,NULL,‘2021-04-07’,8000.00,NULL,20,创建事务保存点poing_insert7777,删除empno为7782的一行数据,执行release savepoint point_insert,查看数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp set sal=4000 where empno=7698;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> release savepoint point_update7698;
ERROR 1305 (42000): SAVEPOINT point_update7698 does not exist
mysql>  savepoint point_update7698;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into emp values ('7777','AMI','QA',NULL,'2021-04-07',8000.00,NULL,20);
Query OK, 1 row affected (0.00 sec)

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

mysql> delete from emp where empno=7782;
Query OK, 1 row affected (0.01 sec)

mysql> RELEASE SAVEPOINT point_insert;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 4000.00 |    NULL |     30 |
|  7777 | AMI    | QA        | NULL | 2021-04-07 | 8000.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
保存点失效
  • 创建表,构造数据
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
  • 操作过程: 关闭事务自动提交,开启事务,更新empno为7698行的SAL字段,更新之后为4000.00,创建事务保存点point_update7698提交事务后rollback 到保存点中观察数据
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update emp set deptno=40 where empno=7788;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

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

mysql> rollback to point_update7788;
ERROR 1305 (42000): SAVEPOINT point_update7788 does not exist
mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     40 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     20 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)
陈娇娜!
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
关于MySQL中savepoint语句使用时所出现的错误
09-10
主要介绍了关于MySQL中savepoint语句使用时所出现的错误,字符串出现e时所产生的问题也被作为MySQL的bug进行过提交,需要的朋友可以参考下
MySQL中常用的存储引擎
程铭程铭你快成名的博客
08-03 2436
什么是存储引擎 关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时去很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，也就是需要不同存储引擎。 MySQL都有哪些存储引擎 可以看到，MySQL中一共有...
MySQL8.0高级篇(下)-事务与日志和备份
Shawn的个人博客
06-01 1348
SHOW ENGINES`命令来查看当前 MySQL 支持的存储引擎都有哪些，以及这些存储引擎是否支持事务**事务：**一组逻辑操作单元，使数据从一种状态变换到另一种状态。**事务处理的原则：**保证所有事务都作为 一个工作单元 来执行，即使出现了故障，都不能改变这种执行方 式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就 永久 地保 存下来；要么数据库管理系统将 放弃 所作的所有 修改 ，整个事务回滚( rollback )到最初状态。more。
MySQL 8.0 事务简介
只是甲的博客
07-30 1182
备注:测试数据库版本为MySQL 8.0 这个blog我们来聊聊MySQL 事务 文章目录一.事务的定义二.事务的属性三.事务的隔离级别四.MySQL的事务4.1 自动提交4.2 事务的隔离级别4.3 MySQL开启和关闭事务4.4 MySQL 事务的savepoint 一.事务的定义 数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过
mysql-事务嵌套之savepoint
chai471793的博客
11-17 2075
背景 最近业务开发中有发现公司自研框架会发生事务嵌套问题导致事务开启异常的一些问题，搜索发现laravel是通过savepoint 实现的嵌套处理，特此做记录。 mysql支持事务嵌套吗 不支持。官方文档有说明 因为每次开启事务都会隐式的执行commit 验证 START TRANSACTION 会隐式提交语句 start transaction 后自动提交已开启的事务 ，如图。 解析：（当前事务隔离级别 可重复读） 1. 清空老表数据 t1 2. set autocommit=0; 关闭自
mysql savepoint作用_savepoint原理
weixin_28704675的博客
02-01 2658
保存点在MySQL中, 保存点SAVEPOINT属于事务控制处理部分。利用SAVEPOINT可以回滚指定部分事务，从而使事务处理更加灵活和精细。SAVEPOINT相关的SQL语句如下SAVEPOINT identifier设置SAVEPOINT。如果重复设置同名savepoint,新的会覆盖老的.RELEASE SAVEPOINT identifier释放SAVEPOINT。ROLLBACK [W...
MySQL事务的SavePoint简介及操作浅谈
xiaoweids的博客
06-15 246
SavePoint是数据库事务中的一个概念, 可以将整个事务切割为不同的小事务, 可以选择将状态回滚到某个小事务发生时的样子，
【转】Mysql笔记-事务
路漫漫其修远兮
01-02 154
原文地址：https://blog.csdn.net/V_victor/article/details/52266815 事务   事务（Transaction）会把数据库从一种一致状态转换为另一种一致状态。在数据库提交工作时，要么所有修改都已经保存了，要么修改都不保存。 事务是访问并更新数据库中各种数据项的一个程序执行单元。在执行中要么都做修改，要么都不做修改。 InnoDB存储引擎中的事务完全...
mysql 查看savepoint_MySQL存储过程SAVEPOINT ROLLBACK to
weixin_39617502的博客
01-19 134
来源：赛迪网作者：若水2008-04-08/14:00MySQL存储过程SAVEPOINT ROLLBACK to示例如下：DELIMITER $$DROP PROCEDURE IF EXISTS `test`.`handlerdemo`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `handlerdemo`()BEGINDECLARE X2 INT;...
mysql数据库的基本操作语法
12-05
MySQL结束符是“；”结束。 1、 显示所有数据库 show databases; 2、 删除数据库 drop database dbName; 3、 创建数据库 create database [if not exists] dbName; 中括号部分可选的，判断该数据不存在就创建 4、 ...
MYSQL中文手册
03-11
1.7.3. MySQL论坛上的MySQL社区支持 1.8. MySQL标准的兼容性 1.8.1. MySQL遵从的标准是什么 1.8.2. 选择SQL模式 1.8.3. 在ANSI模式下运行MySQL 1.8.4. MySQL对标准SQL的扩展 1.8.5. MySQL与标准SQL的差别 ...
SavePoint.java
07-25
SavePoint.java
MySQL事务的SavePoint简介及操作
xiaoweids的博客
06-16 608
SavePoint是数据库事务中的一个概念, 可以将整个事务切割为不同的小事务, 可以选择将状态回滚到某个小事务发生时的样子，
Mysql-savepoint
文天大人
01-14 1513
怀念二抱三抱
Mysql——》保存点
小仙~
06-28 503
如果你开启了一个事务，执行了很多语句，忽然发现某条语句有点问题，你只好使用ROLLBACK语句来让数据库状态恢复到事务执行之前的样子，然后一切从头再来，但是可能根据业务和数据的变化，不需要全部回滚。所以MySQL里提出了一个保存点savepoint，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。
MySQL 基础 ———— SAVEPOINT 的应用
Morty的技术乐园
12-07 3969
引言 savepoint 关键字用于在数据库事务中设置一个存储点，在一个较长的事务中暂存数据，如果在事务末尾执行回滚，可选择性的回滚到 savepoint 设置的暂存点。 本文承接上一篇博客《MySQL 基础 ————事务与隔离级别总结》，进一步探讨事务在实际生产中的重要场景——回滚。 一、SAVEPOINT 语法 SAVEPOINT identifier ROLLBACK [WORK]...
MySQL的savepoint分析
wang0907的博客
07-27 723
savepoint是事务中一个概念，允许在事务执行的过程中暂存事务执行到当前所产生的修改，这样当我们需要回滚事务时就可以选择回滚到某个保存点，而不用回滚整个事务。
mysql savepoint语法_SAVEPOINT和ROLLBACK TO SAVEPOINT语法
weixin_42312523的博客
01-19 633
【赛迪网－IT技术报道】SAVEPOINT和ROLLBACK TO SAVEPOINT语法:SAVEPOINT identifierROLLBACK [WORK] TO SAVEPOINT identifierRELEASE SAVEPOINT identifierInnoDB支持SQL语句SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT和自选...
node-v0.8.10-sunos-x64.tar.gz
最新发布
05-16
Node.js，简称Node，是一个开源且跨平台的JavaScript运行时环境，它允许在浏览器外运行JavaScript代码。Node.js于2009年由Ryan Dahl创立，旨在创建高性能的Web服务器和网络应用程序。它基于Google Chrome的V8 JavaScript引擎，可以在Windows、Linux、Unix、Mac OS X等操作系统上运行。 Node.js的特点之一是事件驱动和非阻塞I/O模型，这使得它非常适合处理大量并发连接，从而在构建实时应用程序如在线游戏、聊天应用以及实时通讯服务时表现卓越。此外，Node.js使用了模块化的架构，通过npm（Node package manager，Node包管理器）,社区成员可以共享和复用代码，极大地促进了Node.js生态系统的发展和扩张。 Node.js不仅用于服务器端开发。随着技术的发展，它也被用于构建工具链、开发桌面应用程序、物联网设备等。Node.js能够处理文件系统、操作数据库、处理网络请求等，因此，开发者可以用JavaScript编写全栈应用程序，这一点大大提高了开发效率和便捷性。 在实践中，许多大型企业和组织已经采用Node.js作为其Web应用程序的开发平台，如Netflix、PayPal和Walmart等。它们利用Node.js提高了应用性能，简化了开发流程，并且能更快地响应市场需求。
mysql savepoint
05-26
MySQL 中的 Savepoint 是一种机制，它允许在事务处理中创建一个标记点，以便在稍后的时间点回滚到该标记点，而不必回滚整个事务。Savepoint 可以在嵌套事务中使用，以便更细粒度地控制事务处理。 使用 Savepoint 的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值