Fix ERROR_TRANSACTION_SUPERIOR_EXISTS (0X00001A35)

Transaction rollback failed because a parent transaction already exists. You need to commit or abort the outer transaction before running the inner one.

Quick answer: Commit or rollback the outer transaction before executing the inner transaction. This error means you tried to roll back a nested transaction while a parent transaction was still active — SQL Server and DB2 don't allow partial rollbacks in nested transactions.

You're seeing ERROR_TRANSACTION_SUPERIOR_EXISTS (0X00001A35) because your code opened a transaction, then opened another transaction inside it, and then tried to roll back only the inner one. The database engine won't let you do that. It sees the outer transaction as the 'superior' transaction — you can't roll back a child independently while the parent is still running.

This happens most often in stored procedures that call other stored procedures, where each one starts its own transaction. I've seen it in ETL jobs, batch scripts, and even in ORM-generated code (looking at you, Entity Framework). The culprit is almost always a BEGIN TRANSACTION inside a loop or a nested call without proper handling of the outer transaction.

Step-by-step fix

Find the outer transaction. Look for the first BEGIN TRANSACTION that isn't matched by a COMMIT or ROLLBACK before the error occurs. In SQL Server Management Studio, run SELECT @@TRANCOUNT right before the failing statement — if it's greater than 1, you've got nesting.
Check stored procedure calls. If you're calling sp_ProcA from sp_ProcB, and both have their own BEGIN TRANSACTION, you're nesting. The fix: either remove the inner BEGIN TRANSACTION and rely on the outer one, or use savepoints instead of nested transactions.
Use savepoints. Instead of BEGIN TRANSACTION inside a loop, use SAVE TRANSACTION SavePointName and ROLLBACK TRANSACTION SavePointName. This lets you roll back to a specific point without killing the outer transaction. Example:
```
SAVE TRANSACTION MySavePoint
-- do work here
IF @@ERROR <> 0
    ROLLBACK TRANSACTION MySavePoint
```
Commit the outer transaction first — only if you're absolutely sure the outer work is complete. This is the nuclear option. If you commit the outer, the inner transaction becomes the only one, and you can roll it back. But be careful: you're discarding the outer work.
Restructure your code so each procedure checks @@TRANCOUNT before starting its own transaction. If a transaction is already active, skip the BEGIN TRANSACTION. This is the cleanest long-term fix but requires changing multiple procedures.

Alternative fixes if the main one fails

Kill the session — sometimes you just need to clear the transaction state. Run KILL <spid> after finding the session ID with SELECT @@SPID. This forces a rollback of all pending transactions. Not elegant, but works.
Restart the service — on SQL Server, restarting the instance clears all open transactions. Use SHUTDOWN WITH NOWAIT in single-user mode. On DB2, db2stop force followed by db2start.
Check for implicit transactions. If SET IMPLICIT_TRANSACTIONS ON is set, every UPDATE or INSERT starts a new transaction without you asking for it. Turn it off with SET IMPLICIT_TRANSACTIONS OFF.
Look for trigger side effects. A trigger on the table might be starting its own transaction. Disable the trigger temporarily to test: DISABLE TRIGGER TriggerName ON TableName. If the error goes away, you've found the problem.

Prevention tip

Never nest transactions without savepoints. Write a wrapper pattern that checks @@TRANCOUNT first:

IF @@TRANCOUNT = 0
    BEGIN TRANSACTION
ELSE
    SAVE TRANSACTION MySavePoint

This way, if your code is called from another transaction, it doesn't create nesting disasters. I've been using this pattern for years in SQL Server 2012 through 2022, and it's saved me countless times. Also, always pair BEGIN TRANSACTION with COMMIT or ROLLBACK in the same scope — don't let transactions leak across procedure boundaries.