Savepoint In Sql in SQL
? SAVEPOINT in SQL
A SAVEPOINT allows you to set a point within a transaction to which you can later roll back without rolling back the entire transaction. This is useful for partial undo within a large transaction.
? What is SAVEPOINT?
Marks a specific spot in a transaction.
You can rollback to this point instead of undoing everything.
Helps manage complex transactions with multiple steps.
? Syntax:
SAVEPOINT savepoint_name;To rollback to a savepoint:
ROLLBACK TO SAVEPOINT savepoint_name;To release (remove) a savepoint:
RELEASE SAVEPOINT savepoint_name;? Example:
BEGIN;INSERT INTO orders (order_id, customer_id) VALUES (101, 1);SAVEPOINT sp1;UPDATE customers SET credit_limit = credit_limit - 1000 WHERE customer_id = 1;-- Oops, problem with the update, rollback to savepointROLLBACK TO SAVEPOINT sp1;-- Continue transaction without the updateCOMMIT;? Notes:
Not all databases support
SAVEPOINT(most modern ones like PostgreSQL, Oracle, MySQL do).Rolling back to a savepoint does not end the transaction, you can continue working.
Releasing a savepoint frees resources but is optional.
? Related commands:
| Command | Description |
|---|---|
BEGIN or START TRANSACTION | Starts a transaction |
SAVEPOINT | Sets a point within the transaction |
ROLLBACK TO SAVEPOINT | Rolls back changes to the savepoint |
RELEASE SAVEPOINT | Removes the savepoint |
COMMIT | Saves all changes permanently |
ROLLBACK | Undoes all changes in the transaction |
Want examples of using SAVEPOINT in a specific SQL dialect?
