MySQL transaction partial rollback-rollback to the specified save point

We can define the SavePoint (savepoint) during the MySQL transaction process and roll back to the state before the specified savepoint.

The syntax for defining a savepoint and rolling back to the state before the specified savepoint is as follows.

1. Define save Point---savepoint save name;
2. Roll back to the specified savepoint---ROLLBACK to savepoint save name:

The following demonstration will insert 3 consecutive data into the table user, define a savepoint after inserting the 2nd data, and finally see if you can roll back to this savepoint.

1. View the data in the user table

1. Mysql> select * from user;
2. +-----+----------+-----+------+
3. | Mid | name | SCX | Word |
4. +-----+----------+-----+------+
5. | 1 | Zhangsan | 0 | NULL |
6. | 2 | Wangwu | 1 | NULL |
7. +-----+----------+-----+------+
8. 2 rows in Set (0.05 sec)

2. mysql Transaction start

1. Mysql> BEGIN;
2. Query OK, 0 rows Affected (0.00 sec)

3. Insert 2 data into the table user

1. mysql> INSERT into User VALUES (' 3 ', ' One ', ' 0 ', ');
2. Query OK, 1 row affected (0.08 sec)
3. mysql> INSERT into User VALUES (' 4, ' both ', ' 0 ', ');
4. Query OK, 1 row Affected (0.00 sec)
5. Mysql> select * from user;
6. +-----+----------+-----+------+
7. | Mid | name | SCX | Word |
8. +-----+----------+-----+------+
9. | 1 | Zhangsan | 0 | NULL |
10. | 2 | Wangwu | 1 | NULL |
11. | 3 | One | 0 | |
12. | 4 | both | 0 | |
13. +-----+----------+-----+------+
14. 4 rows in Set (0.00 sec)

4. Specify the savepoint and save the name as test

1. mysql> savepoint test;
2. Query OK, 0 rows Affected (0.00 sec)

5. Insert the 3rd data into the table user

1. mysql> INSERT into User VALUES (' 5 ', ' Three ', ' 0 ', ');
2. Query OK, 1 row Affected (0.00 sec)
3. Mysql> select * from user;
4. +-----+----------+-----+------+
5. | Mid | name | SCX | Word |
6. +-----+----------+-----+------+
7. | 1 | Zhangsan | 0 | NULL |
8. | 2 | Wangwu | 1 | NULL |
9. | 3 | One | 0 | |
10. | 4 | both | 0 | |
11. | 5 | Three | 0 | |
12. +-----+----------+-----+------+
13. 5 rows in Set (0.02 sec)

6. Roll back to save point test

1. Mysql> ROLLBACK to savepoint test;
2. Query OK, 0 rows affected (0.31 sec)
3. Mysql> select * from user;
4. +-----+----------+-----+------+
5. | Mid | name | SCX | Word |
6. +-----+----------+-----+------+
7. | 1 | Zhangsan | 0 | NULL |
8. | 2 | Wangwu | 1 | NULL |
9. | 3 | One | 0 | |
10. | 4 | both | 0 | |
11. +-----+----------+-----+------+
12. 4 rows in Set (0.00 sec)

We can see that the record inserted after the save point test is not displayed, that is, the success group rolled to the state before the save point test was defined. The use of SavePoint enables the ability to commit only part of the processing in a transaction.

Article source page programming, reprint please specify Source: http://uphtm.com/database/192.html

MySQL transaction partial rollback-rollback to the specified save point