SAVEPOINT statements are used to create savepoints within a transaction, enabling partial rollback within the transaction.
Syntax
Create a savepoint:
SAVEPOINT savepoint_name
Roll back to a savepoint:
ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name
Delete Savepoint:
RELEASE SAVEPOINT savepoint_name
Parameters
Parameter |
Note |
|---|---|
| savepoint_name | Specifies the name of the savepoint. The specified savepoint must be unique within the transaction scope. A savepoint with the same name will overwrite the previous one. After creating a savepoint, you can roll back the transaction to that savepoint, or use it for ROLLBACK Statement to roll back the entire transaction. |
| WORK | Optional keywords that do not affect semantics. |
Examples
Create a savepoint and use it in a transaction:
obclient> CREATE TABLE account_table(id INT PRIMARY KEY, account_name VARCHAR(50), balance DECIMAL(10,2));
obclient> START TRANSACTION;
obclient> INSERT INTO account_table VALUES (1, 'Account1', 1000.00);
obclient> SAVEPOINT sp1;
obclient> UPDATE account_table SET balance = balance - 100 WHERE id = 1;
obclient> SAVEPOINT sp2;
obclient> UPDATE account_table SET balance = balance - 50 WHERE id = 1;
obclient> SELECT * FROM account_table WHERE id = 1;
The query result is as follows:
+----+-------------+---------+
| id | account_name| balance |
+----+-------------+---------+
| 1 | Account1 | 850.00 |
+----+-------------+---------+
1 row in set
Roll back to the savepoint sp2 and undo the last modification:
obclient> ROLLBACK TO SAVEPOINT sp2;
obclient> SELECT * FROM account_table WHERE id = 1;
The query result is as follows:
+----+-------------+---------+
| id | account_name| balance |
+----+-------------+---------+
| 1 | Account1 | 900.00 |
+----+-------------+---------+
1 row in set
Roll back to savepoint sp1 and undo all modifications:
obclient> ROLLBACK TO SAVEPOINT sp1;
obclient> SELECT * FROM account_table WHERE id = 1;
The query result is as follows:
+----+-------------+---------+
| id | account_name| balance |
+----+-------------+---------+
| 1 | Account1 | 1000.00 |
+----+-------------+---------+
1 row in set
Delete Savepoint:
obclient> SAVEPOINT sp3;
obclient> UPDATE account_table SET balance = balance - 200 WHERE id = 1;
obclient> RELEASE SAVEPOINT sp3;
obclient> COMMIT;
Deleting a savepoint will also delete subsequent savepoints:
obclient> CREATE TABLE test_table(id INT PRIMARY KEY, name VARCHAR(50));
obclient> BEGIN;
obclient> INSERT INTO test_table VALUES(1, 'Alice');
obclient> SAVEPOINT sp1;
obclient> INSERT INTO test_table VALUES(2, 'Bob');
obclient> SAVEPOINT sp2;
obclient> INSERT INTO test_table VALUES(3, 'Charlie');
obclient> SAVEPOINT sp3;
obclient> SELECT * FROM test_table;
The query result is as follows:
+----+---------+
| id | name |
+----+---------+
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
+----+---------+
3 rows in set
obclient> RELEASE SAVEPOINT sp1;
obclient> ROLLBACK TO SAVEPOINT sp2;
-- ERROR 1305 (42000): savepoint does not exist
Notice
- A savepoint is unique within a transaction. A savepoint with the same name will overwrite the previous one.
- After rolling back to a savepoint, all savepoints created after that point are automatically deleted.
- You can explicitly drop a savepoint by using the `RELEASE SAVEPOINT` statement, but no modifications will be rolled back.
- Delete a savepoint will also delete all savepoints created after it.