Savepoints in Nested Transactions
Savepoints in Nested Transactions is a SQL statement in the Transactions category. Savepoints allow partial rollback within a transaction. RELEASE removes the savepoint without rolling back. The syntax is SAVEPOINT sp1; ... ROLLBACK TO SAVEPOINT sp1; RELEASE SAVEPOINT sp1;. It returns partial rollback. A typical example: START TRANSACTION; INSERT INTO log VALUES ('Step 1'); SAVEPOINT sp_before_step2; INSERT INTO log VALUES ('Step 2'); UPDATE accounts SET balance = balance - 100 WHERE id = 1; IF (SELECT balance FROM accounts WHERE id = 1) < 0 THEN ROLLBACK TO SAVEPOINT sp_before_step2; -- Undo step 2 only END IF; RELEASE SAVEPOINT sp_before_step2; -- Remove the savepoint INSERT INTO log VALUES ('Step 3'); COMMIT; -- Step 1 and 3… A close relative is BEGIN / START TRANSACTION, which begins a new transaction. Changes made within are invisible to others until COMMIT. A close relative is COMMIT, which saves all changes made in the current transaction permanently.