Nested Rollback

So far, we've treated transactions as monolithic units: either all their changes commit, or all their changes are rolled back. But real-world transactions are often more complex. Applications use savepoints to create rollback points within a transaction, allowing partial undo while keeping earlier work intact.

Consider an e-commerce checkout:

1. Reserve inventory items
2. SAVEPOINT 'after_reserve'
3. Attempt payment processing
4. If payment fails: ROLLBACK TO SAVEPOINT 'after_reserve' (keep reservation, retry payment)
5. If payment succeeds: COMMIT

This introduces nested rollback—the ability to undo part of a transaction while preserving other parts. How does ARIES handle this complexity during recovery?

A savepoint is a named marker within a transaction that allows partial rollback. When you create a savepoint, the system records the current position in the transaction's log chain. Later, you can rollback to that savepoint, undoing all changes made after it while preserving changes before it.

Savepoint Operations:

1. SAVEPOINT name: Create a marker at the current point
2. ROLLBACK TO SAVEPOINT name: Undo all changes after the savepoint
3. RELEASE SAVEPOINT name: Discard the savepoint (changes become permanent within txn)

What Savepoints Do NOT Do:

• Savepoints do not commit anything—the entire transaction can still be rolled back
• Releasing a savepoint doesn't persist data—only COMMIT does that
• Savepoints are internal to one transaction—they can't span transactions

Log Structure with Savepoints:

When a savepoint is created, a SAVEPOINT log record is written. When rolling back to a savepoint, CLRs are written for each undone operation, and the rollback stops at the savepoint marker.

When an application issues ROLLBACK TO SAVEPOINT, the database performs a partial undo that's conceptually similar to the recovery undo phase—but it operates on a running transaction rather than during crash recovery.

The Process:

1. Find the savepoint record in the log (using savepoint name lookup)
2. Get the saveLSN from that record (the LSN before the savepoint)
3. Walk backward through the transaction's log chain from lastLSN
4. Undo each update record, writing CLRs as we go
5. Stop when we reach the saveLSN (or pass it)
6. Update the transaction's lastLSN to the latest CLR
7. Continue normal transaction processing

Key Observations:

1. CLRs are written just like full abort: The partial undo creates CLR records that document each undone operation.

2. The transaction continues: Unlike a full abort, the transaction remains active and can make more updates after the rollback to savepoint.

3. Chain navigation is the same: We follow PrevLSN pointers, skip CLRs using undoNextLSN, just like recovery undo.

4. Stop condition differs: Instead of reaching BEGIN, we stop when passing the saveLSN.

Savepoints can be nested—you can create multiple savepoints at different points within a transaction. When rolling back, you can choose which savepoint to target, rolling back to any previously established point.

Example: Multiple Savepoints

Nested Rollback Behavior:

Nested Savepoint Considerations:

1. Rolling back destroys inner savepoints: If you ROLLBACK TO A, savepoints B and C are invalidated—the work they marked is now gone.

2. Savepoint names can be reused: After rolling back, you can create a new savepoint with the same name at the current position.

3. Stack-like behavior: Savepoints are often managed as a stack. RELEASE SAVEPOINT pops the top savepoint; ROLLBACK TO pops back to a named point.

When a crash occurs, the undo phase must handle transactions that have savepoint history—including partial rollbacks that created CLRs. The good news: ARIES already handles this correctly through its standard CLR processing.

Scenario: Crash After Partial Rollback

Consider this sequence:

1. Transaction T1 makes updates at LSN 100, 200, 300, 400
2. T1 creates savepoint at LSN 250 (saveLSN = 200)
3. T1 rolls back to savepoint → CLRs at 500 (undo 400), 600 (undo 300)
4. T1 makes new update at LSN 700
5. CRASH (T1 hasn't committed)

Recovery Processing:

1. Analysis Phase: T1 is in ACTIVE state, lastLSN = 700, undoNextLSN = 700

2. Redo Phase: Replays all records including CLRs 500 and 600. After redo:

   • Updates 100, 200 are applied
   • Updates 300, 400 are applied, then CLRs 500, 600 reverse them
   • Update 700 is applied
   • Net effect: Updates 100, 200, 700 are active

3. Undo Phase:

   • Start with ToUndo = {700 → T1}
   • Undo 700 → write CLR (undoNextLSN = 600)
   • Next LSN is 600 (a CLR), follow undoNextLSN = 200
   • Undo 200 → write CLR (undoNextLSN = 100)
   • Undo 100 → write CLR (undoNextLSN = null)
   • T1 fully undone → write END

The Key Insight: CLRs from the previous partial rollback automatically guide the undo phase to skip LSNs 300 and 400. The CLR chain "remembers" that those updates were already undone!

Some database systems support subtransactions or nested transactions, which provide stronger semantics than savepoints. While savepoints are just rollback markers, subtransactions can have their own commit/abort status.

Savepoints vs Subtransactions:

How Subtransactions Affect Recovery:

Subtransactions introduce independent units within a parent transaction. Each subtransaction has its own:

• Begin record
• Log chain (via PrevLSN within the subtransaction)
• Potentially its own savepoints
• Commit or abort record

Recovery Implications:

1. Subtransaction commit is provisional: A subtransaction's commit is only durable if the parent transaction commits. If the parent aborts, all subtransaction work is undone.

2. Undo must track hierarchy: The undo phase needs to understand parent-child relationships:

   • If parent is loser, undo all children regardless of their commit status
   • If child aborted before crash, its work is already undone via CLRs

3. Log structure becomes more complex: Each subtransaction has its own PrevLSN chain, nested within the parent's chain.

ARIES Support:

Standard ARIES handles savepoints naturally but needs extensions for full nested transactions. Extensions include:

• Hierarchical transaction IDs (T1.S1, T1.S2, etc.)
• Modified commit protocol (subtransaction commit logs don't force)
• Parent-child relationship tracking in transaction table

Let's examine some complex scenarios that combine savepoints, partial rollbacks, and crash recovery to solidify our understanding.

Scenario 1: Multiple Partial Rollbacks Before Crash

Wait, the above scenario has an error in reasoning. Let me correct it:

Corrected Scenario 1 Analysis:

When rolling back to savepoint X (at LSN 300, saveLSN=200), we undo:

• LSN 800 (update P4) → CLR with undoNextLSN pointing to 700
• LSN 700 is a CLR → follow its undoNextLSN = 400 (but wait, we need to check where 400 is relative to savepoint)
• LSN 400 > saveLSN (200), so undo 400 → CLR with undoNextLSN pointing to 300 (the savepoint record)
• LSN 300 is SAVEPOINT with saveLSN=200, we've reached the target

The CLR chain correctly encodes this. During recovery, following the CLR pointers skips precisely the right records.

Implementing nested rollback support requires careful design decisions. Here are key considerations:

Savepoint Storage:

Databases need to track active savepoints for each transaction:

class Transaction {
    id: TransactionId
    status: ACTIVE | COMMITTING | ABORTING
    lastLSN: LogSequenceNumber
    savepoints: Map<SavepointName, SavepointInfo>
}

class SavepointInfo {
    name: string
    saveLSN: LogSequenceNumber  // Log position at creation
    lockState?: LockSnapshot    // Optional: for lock restoration
    cursorsState?: CursorInfo   // Optional: for cursor restoration
}

Performance Implications:

1. Savepoint overhead is minimal: Creating a savepoint is just logging a record and updating in-memory state.

2. Rollback cost depends on work to undo: More updates after the savepoint = more CLRs = more time.

3. Recovery doesn't pay extra: The undo phase processes CLRs the same way whether they came from partial rollbacks or full recovery.

SQL Standard Compliance:

SQL:1999 defined savepoint semantics. Key requirements:

• SAVEPOINT savepointname
• ROLLBACK TO SAVEPOINT savepointname
• RELEASE SAVEPOINT savepointname

Most databases implement these, though details (like lock behavior) vary.