What are nested transactions?

Nested transactions are transactions that begin inside another, already active transaction.

Key aspects:

• Atomicity: Each nested transaction is atomic. If it completes successfully, its changes become visible in the outer transaction. If it is rolled back, its changes are canceled without affecting the outer transaction.
• Dependency: A nested transaction cannot be committed until the outer transaction is committed.
• Rollback: Rolling back the outer transaction causes all nested transactions to rollback, even if they were successfully completed. Rolling back a nested transaction does not rollback the outer transaction.
• Savepoints: Most implementations of nested transactions use savepoints. Starting a nested transaction corresponds to setting a savepoint, and its commit releases this savepoint. Rolling back a nested transaction rolls back to the corresponding savepoint.

Example usage (pseudocode):

// Start of outer transaction
BEGIN TRANSACTION;

// First operation in outer transaction
INSERT INTO table1 (column1) VALUES ('data1');

// Start of nested transaction
SAVEPOINT nested_transaction_1;

// Operation in nested transaction 1
INSERT INTO table2 (column2) VALUES ('data2');

// Successful completion of nested transaction 1
RELEASE SAVEPOINT nested_transaction_1;

// Start of second nested transaction
SAVEPOINT nested_transaction_2;

// Operation in nested transaction 2
INSERT INTO table3 (column3) VALUES ('data3');

// Error occurred in nested transaction 2
ROLLBACK TO SAVEPOINT nested_transaction_2; // Rollback only the second nested transaction

// Operation in outer transaction after rollback of nested
INSERT INTO table1 (column1) VALUES ('data4');

// End of outer transaction
COMMIT; // Commit changes to table1 and table2

Advantages:

• Allow structuring complex operations by breaking them into smaller atomic blocks.
• Simplify error handling: only part of the operation can be rolled back without rolling back the entire outer transaction.
• Useful when working with functions or procedures inside a transaction.

Disadvantages:

• Can increase code complexity and make debugging more difficult.
• Can cause resource locking issues.
• Not all database management systems fully support true nested transactions (some emulate them using savepoints).