Support for Nested Transactions

Context :
As application complexity grows, transaction handling often extends beyond flat, single-level operations. Developers frequently encounter workflows where partial failure handling is crucial for example, a sub-module's failure shouldn't cascade into a full transaction rollback if the parent operation can still succeed safely.

Current : (Limitations)
TanStack/db currently supports only top-level transactions. There is no mechanism for managing sub-transactions (i.e., nested transactions) that can be conditionally committed or rolled back without affecting the outer scope.

Why
Nested transactions (or savepoint-based equivalents) are a common feature in advanced ORM and database libraries. They allow:

• Fine-grained control over rollback logic within composite operations.
• Modular transactional scopes for reusable components.
• Cleaner error recovery without forcing full transaction aborts.

Impact:*
Adding nested transaction support would enable:

• Better abstraction in domain modules (e.g., services managing their own transactional state).
• Robust error handling in multi-step operations.
• Safe composition of reusable transactional units.

My Point of View Approach

• Introduce a transaction context manager to track transaction levels.
• Use savepoints (where supported) to simulate nested transactions.
• Ensure that each nested scope can commit, rollback, or rollback to savepoint independently.
• Optionally provide a developer-friendly API like withNestedTransaction() or tx.nest().

Example:

await db.transaction(async (tx) => {
  // Create a new user
  await tx.insert('users', {
    id: 'user_1',
    name: 'Vivek Kumar',
  });

  try {
    // Begin a nested transaction
    await tx.nested(async (subTx) => {
      // Insert user preferences within the nested transaction
      await subTx.insert('preferences', {
        userId: 'user_1',
        theme: 'dark',
        notifications: true,
      });

      // Simulate an error to trigger rollback of only the nested transaction
      throw new Error('Invalid preference data');
    });
  } catch (err) {
    // Handle the error from the nested transaction without affecting the parent
    console.warn('Nested transaction failed, continuing with parent tx:', err.message);
  }

  // Continue with parent transaction logic
  await tx.insert('audit_logs', {
    action: 'User created without any preferences',
    userId: 'user_1,
  });
});

Hi @KyleAMathews

I’d like to propose adding support for nested transactions in TanStack/db. This feature would allow developers to execute sub-transactions within a parent transaction, enabling more granular control over rollbacks and commits in complex operations.
Thank You

No files, tests, or transaction entry points are named in the issue. Start by locating the existing transaction implementation, then clarify the nested API, savepoint support, and behavior for independent commit and rollback before defining completion criteria.