Nested Transaction Concepts

Definition

A nested transaction would mean starting a new transaction WHILE another is already open — most SQL databases don't support TRUE nested transactions; instead, SAVEPOINT (22.11) provides most of the practical benefit, and a second START TRANSACTION while one is already open is typically handled by dialect-specific rules rather than genuine nesting.

How It Works

In most databases (including the platform used in this course), issuing START TRANSACTION while a transaction is already open does NOT create an independent nested transaction — common behaviors include: implicitly committing the current transaction first, raising a warning/error, or simply being a no-op that continues the existing transaction. None of these give you genuine "commit the inner transaction without affecting the outer one" semantics.

SAVEPOINT is the practical substitute: it gives you an inner "checkpoint" you can roll back to without discarding the outer transaction, covering the most common real use case (isolate and undo just a portion of a larger transaction) without needing true nesting.

Edge Cases and Pitfalls

• Application frameworks/ORMs that expose a "nested transaction" API to their users are usually implementing it via SAVEPOINT under the hood, not via genuine database-level nested transactions — knowing this helps explain their actual behavior (e.g. why an "inner transaction" rollback doesn't undo the outer one, but also doesn't provide fully independent commit semantics either).
• The behavior of a second START TRANSACTION while one is open is genuinely dialect-specific — some databases raise a warning and implicitly commit the previous transaction first (which can silently commit work you thought was still pending), a subtle correctness trap for code that assumes "starting a transaction is always safe."
• True nested transactions (with fully independent inner commit/rollback) exist in some other database systems' proprietary extensions but are not part of the SQL standard's common core — don't assume portability of this feature across databases.

Key Takeaways

• Most SQL databases do not support true nested transactions — SAVEPOINT is the practical, widely-supported substitute for isolating and undoing part of a larger transaction.
• A second START TRANSACTION while one is already open is dialect-specific and can silently commit the outer transaction in some databases — a real correctness trap.
• "Nested transaction" APIs in ORMs/frameworks are typically SAVOEPOINT-based implementations, not genuine independent nested database transactions.