SAVEPOINT related statements are used to create a savepoint within a transaction, allowing for partial rollback of the transaction.
Syntax
Create a savepoint:
SAVEPOINT savepoint_name
Roll back to a savepoint:
ROLLBACK [WORK] TO [SAVEPOINT] savepoint_name
Delete a savepoint:
RELEASE SAVEPOINT savepoint_name
Parameters
| Parameter | Description |
|---|---|
| savepoint_name | The name of the savepoint. The specified savepoint must be unique within the transaction. If a savepoint with the same name already exists, it will overwrite the previous one. After creating a savepoint, you can roll back the transaction to this savepoint, or use the ROLLBACK statement to roll back the entire transaction. |
| WORK | Optional keyword that does not affect the semantics. |
Examples
Create a savepoint and use it within 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 to 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 the savepoint sp1 to 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 a 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 all 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 must be unique within the transaction. If a savepoint with the same name already exists, it will overwrite the previous one.
- After rolling back to a savepoint, all savepoints created after that savepoint will be automatically deleted.
- You can explicitly delete a savepoint using `RELEASE SAVEPOINT`, but this will not roll back any modifications.
- Deleting a savepoint will also delete all subsequent savepoints.