Definition
- Savepoint – a named marker inside an open transaction, created with
SAVEPOINT name. - Partial rollback –
ROLLBACK TO nameundoes only the changes made after the savepoint; everything before it stays intact and the transaction remains open. - Difference from full ROLLBACK – a full
ROLLBACKdiscards the entire transaction;ROLLBACK TO savepointis surgical. - Invalidation – rolling back to a savepoint invalidates any savepoints created after it.
RELEASE SAVEPOINT– discards the savepoint when you no longer need it, freeing resources.- Use cases – batch processing with per-record isolation, and partial retry of a transient failure within a larger transaction.
Imagine processing a batch of a hundred salary credits for National Bank of India inside one transaction. Before each credit you drop a bookmark (savepoint). If crediting employee number 57 fails because their account is frozen, you do not tear up the whole batch – you rewind to the bookmark just before 57 (ROLLBACK TO sp57), skip that one, log it, and carry on from 58. The 56 good credits already done stay, and the transaction stays open.
Understanding the Concepts
What is a savepoint?
A savepoint is a named marker you set inside an open transaction. You create it with SAVEPOINT name, do more work, and if that work goes wrong you issue ROLLBACK TO name to undo only the changes made after the savepoint. Everything before the savepoint stays intact and the transaction remains open.
BEGIN;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 'SAV2001'; -- credit emp 1
SAVEPOINT sp_emp2;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 'SAV2002'; -- credit emp 2
-- Suppose this fails (account frozen)
ROLLBACK TO sp_emp2; -- undo only emp 2's credit; emp 1's credit survives
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 'SAV2003'; -- credit emp 3
COMMIT; -- emp 1 and emp 3 are committed; emp 2 was skipped
How is this different from a full ROLLBACK?
A full ROLLBACK throws away the entire transaction. ROLLBACK TO savepoint is surgical – it rewinds to a point and keeps the rest. The transaction stays open after a partial rollback.
| Operation | Effect | Transaction state |
|-----------|--------|------------------|
| ROLLBACK | Undoes everything since BEGIN | Transaction ends |
| ROLLBACK TO sp1 | Undoes only work after sp1 | Transaction stays open |
When do you use savepoints for batch processing?
You want the batch to run in one transaction for efficiency, but you do not want one poison record to force the whole batch to roll back. You set a savepoint before each record; on failure you roll back to that savepoint, record the error, and proceed.
BEGIN;
-- Process 100 salary credits
FOR i IN 1..100 LOOP
SAVEPOINT sp_record;
BEGIN
UPDATE accounts SET balance = balance + salary_amount
WHERE account_id = emp_accounts[i];
EXCEPTION WHEN OTHERS THEN
ROLLBACK TO sp_record; -- undo this one credit
INSERT INTO batch_errors (emp_id, error_msg)
VALUES (emp_ids[i], SQLERRM);
END;
END LOOP;
COMMIT; -- all good credits committed, bad ones skipped and logged
When do you use savepoints for partial retry?
Inside a long transaction, a single step might fail transiently – a flaky external lookup, a temporary lock timeout. You roll back to a savepoint just before that step and retry it without abandoning the work already done.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.*;
public class BatchCreditService {
private final Connection connection;
public BatchCreditService(Connection connection) {
this.connection = connection;
}
public void processSalaryBatch(String[] accountIds, BigDecimal amount)
throws SQLException {
connection.setAutoCommit(false);
try {
for (String accountId : accountIds) {
Savepoint sp = connection.setSavepoint("sp_" + accountId);
try (PreparedStatement ps = connection.prepareStatement(
"UPDATE accounts SET balance = balance + ? WHERE account_id = ?")) {
ps.setBigDecimal(1, amount);
ps.setString(2, accountId);
ps.executeUpdate();
} catch (SQLException e) {
// This account failed (frozen, constraint violation, etc.)
connection.rollback(sp); // undo only this credit
logSkippedAccount(accountId, e.getMessage());
}
}
connection.commit(); // commit all successful credits
} catch (SQLException e) {
connection.rollback(); // something unexpected – undo everything
throw e;
} finally {
connection.setAutoCommit(true);
}
}
private void logSkippedAccount(String accountId, String error) {
System.err.printf("Skipped %s: %s%n", accountId, error);
}
}
What happens when you RELEASE a savepoint?
RELEASE SAVEPOINT name discards the savepoint. It does not commit or rollback anything – it just frees the resources held by the savepoint. After releasing, you can no longer roll back to it.
BEGIN;
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 'SAV2001';
RELEASE SAVEPOINT sp1; -- sp1 is gone, cannot roll back to it anymore
COMMIT;
What happens to nested savepoints?
Rolling back to a savepoint invalidates any savepoints created after it, because time has effectively rewound past them.
BEGIN;
SAVEPOINT sp1;
-- work A
SAVEPOINT sp2;
-- work B
ROLLBACK TO sp1; -- undoes work B AND invalidates sp2
-- sp2 no longer exists; only sp1 is valid
COMMIT;
How does Spring use savepoints?
In Spring, nested propagation (Propagation.NESTED) is implemented using JDBC savepoints under the hood. An inner method can roll back independently of the outer transaction.
@Service
public class SalaryService {
@Transactional
public void processAllSalaries() {
for (Employee emp : employees) {
try {
creditSingleEmployee(emp); // NESTED = savepoint
} catch (Exception e) {
// Inner savepoint rolled back; outer transaction continues
logError(emp, e);
}
}
}
@Transactional(propagation = Propagation.NESTED)
public void creditSingleEmployee(Employee emp) {
accountRepository.credit(emp.getAccountId(), emp.getSalary());
}
}
What are the limitations of savepoints?
Savepoints are not free. Holding a long transaction with many savepoints keeps locks and undo information around. For very large batches (hundreds of thousands of records), prefer chunking into several shorter transactions instead of one giant transaction with thousands of savepoints.
Small batch (100 records): One transaction + savepoints ✓
Large batch (1,000,000 records): Chunk into 1,000-record transactions ✓
Huge single transaction with 1M savepoints: ✗ (locks, undo bloat)
What are common mistakes?
The most common mistake is confusing ROLLBACK TO savepoint with a full ROLLBACK.
-- Wrong: intended partial rollback, but used full ROLLBACK
BEGIN;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 'SAV2001';
SAVEPOINT sp1;
UPDATE accounts SET balance = balance + 10000 WHERE account_id = 'SAV2002';
ROLLBACK; -- undoes BOTH updates, not just the second one!
-- Correct: partial rollback
ROLLBACK TO sp1; -- undoes only the second update
What are common interview follow-up questions?
- "Can you nest savepoints?" Yes. You can create savepoints within savepoints, and roll back to any ancestor.
- "Does ROLLBACK TO end the transaction?" No, the transaction stays open. You can continue working and eventually COMMIT or ROLLBACK.
- "Are savepoints supported in all databases?" PostgreSQL, MySQL, Oracle, and SQL Server all support savepoints.
Visual Overview
Transaction Timeline with Savepoints:
BEGIN
│
├── UPDATE emp 1 (credit Rs.10,000) ← stays
│
├── SAVEPOINT sp2
│ │
│ ├── UPDATE emp 2 (credit Rs.10,000) ← FAILS
│ │
│ └── ROLLBACK TO sp2 ← undo only emp 2
│
├── UPDATE emp 3 (credit Rs.10,000) ← stays
│
└── COMMIT
│
▼
Result: emp 1 and emp 3 credited.
emp 2 skipped and logged.
One transaction, partial failure handled.
Full ROLLBACK vs Partial ROLLBACK:
┌────────────────────────────┐ ┌────────────────────────────────┐
│ ROLLBACK │ │ ROLLBACK TO sp2 │
│ Undoes everything │ │ Undoes only work after sp2 │
│ Transaction ends │ │ Transaction stays open │
│ ┌───┐┌───┐┌───┐ │ │ ┌───┐┌───┐┌───┐ │
│ │ ✗ ││ ✗ ││ ✗ │ │ │ │ ✓ ││ ✗ ││ │ (can continue) │
│ └───┘└───┘└───┘ │ │ └───┘└───┘└───┘ │
└────────────────────────────┘ └────────────────────────────────┘
Key Takeaway
A savepoint is a named marker you set inside an open transaction. You create it with SAVEPOINT followed by a name, do more work, and if that work goes wrong you issue ROLLBACK TO that name, which undoes only the changes made after the savepoint while keeping everything before it intact. The transaction stays open. This is surgical, partial rollback – very different from a full ROLLBACK, which throws away the entire transaction.
The two situations where savepoints shine are partial retry and batch processing with per-record isolation. For batch processing, you want the efficiency of one transaction but you do not want a single poison record to force the whole batch to roll back. You set a savepoint before each record; when a record fails, you roll back to its savepoint, skip it, and carry on.
In the National Bank of India nightly salary batch, Rajesh credits many employee accounts in one transaction. Occasionally one account is frozen so its credit fails. The service sets a savepoint before each credit; when the frozen account throws, it rolls back to that savepoint, logs the skip, and carries on. At the end it commits once, so every good credit lands atomically and the frozen one is left untouched. In JDBC this maps to setSavepoint and rollback(savepoint), and in Spring the NESTED propagation uses savepoints under the hood. The caution is that holding a huge transaction with thousands of savepoints keeps locks and undo data alive, so for very large batches prefer chunking into shorter transactions.
Remember one sentence: a savepoint is a bookmark inside a transaction, and rolling back to it undoes only the work after the bookmark, so one bad step never sinks the whole transaction.
---