ACID names the basic rules that make a transaction dependable: atomicity, consistency, isolation, and durability. A transaction groups one or more T-SQL data updates so they are committed or undone as one logical unit. section 7.1 section 7.7
A common mistake is to assume an error automatically cleans up a transaction. If a stored procedure begins a transaction, completes one insert, then fails on a later insert, the transaction can remain open unless its error path rolls it back. Check @@TRANCOUNT when diagnosing this situation; with no intended open transaction, it should be 0. section 7.1
The four properties set the expected outcome:
- Atomic: all statements commit or all are undone by rollback; partial success is not the intended result. section 7.1
- Consistent: the tables are left in a consistent state when the transaction ends. section 7.1
- Isolated: concurrent transactions do not interact with one another. section 7.1
- Durable: committed updates survive a SQL Server crash. section 7.1
A sales-order failure
A sales-order procedure can insert a header and then fail while inserting details because the target table was renamed or a product ID violates a foreign-key constraint. Without complete error handling, a transaction count mismatch can reveal that the work was not rolled back. Treat such failures as transaction failures: trap the error and ensure the transaction is terminated rather than leaving a partial or open unit of work. section 7.1 section 7.1
ACID is not a reason to ignore operational choices. SQL Server uses locking to enforce isolation and flushes its transaction log to disk before commit completes to support durability. Isolation levels can vary, and delayed durability can reduce I/O contention on busy systems while accepting that an ill-timed crash may lose committed updates. section 7.1 section 7.1
Safer practice: wrap transactional work with error handling, use TRY..CATCH to trap failures, and make rollback behavior explicit. XACT_ABORT determines whether the whole transaction is terminated when a low-severity error causes a statement to fail; use it with an understanding of the error paths your procedure must handle. section 7.1 section 7.7
