Nested Transactions are logical levels within a single physical transaction where only the outermost COMMIT actually commits and any ROLLBACK rolls back the entire transaction. Multiple Transactions are independent transactions where each COMMIT or ROLLBACK affects only its own transaction scope.
|
Feature |
Nested
Transaction |
Multiple
Transactions |
|
Definition |
Transaction
inside another transaction |
Separate
independent transactions |
|
BEGIN TRAN Count |
Multiple BEGIN TRAN statements before final COMMIT |
Each transaction starts and ends independently |
|
Physical
Transaction |
One |
Multiple |
|
Logical Transaction Levels |
Multiple |
One per transaction |
|
@@TRANCOUNT |
Increases
with each BEGIN TRAN |
Resets
to 0 after each COMMIT |
|
Independent Commit |
❌ No |
✅ Yes |
|
Independent
Rollback |
❌ No |
✅ Yes |
|
Inner COMMIT Commits Data? |
❌ No |
✅ Yes |
|
Rollback
Effect |
Entire
transaction rolls back |
Only
current transaction rolls back |
|
Transaction Log BEGIN Record |
One physical BEGIN |
One BEGIN per transaction |
|
Transaction
Log COMMIT Record |
One
physical COMMIT |
One
COMMIT per transaction |
|
Lock Release |
Only after outer COMMIT |
After each COMMIT |
|
Blocking
Risk |
Higher |
Lower |
|
Production Usage |
Rare |
Very Common |
|
Best
Use Case |
Stored
procedure call hierarchy |
Normal
OLTP processing |
