Transactions
A transaction groups several changes so they succeed or fail together. Moving money between two accounts must never debit one without crediting the other. This lesson explains ACID, BEGIN, COMMIT and ROLLBACK, error handling and isolation levels.
- StartBEGIN TRANSACTIONStart a unit of work
- Step 2Debit account AUPDATE Accounts SET Balance = Balance - 500 WHERE Id = 1
- Step 3Credit account BUPDATE Accounts SET Balance = Balance + 500 WHERE Id = 2
- Step 4Did both succeed?If any step fails, ROLLBACK undoes everything
- EndCOMMITBoth changes become permanent together
The idea in plain words
A transaction is a promise that a group of statements behaves as one. Databases guarantee four properties, known as ACID: Atomic (all or nothing), Consistent (constraints still hold), Isolated (other sessions do not see half-finished work) and Durable (committed changes survive a crash).
SQL Server runs each single statement in its own transaction automatically. You only need an explicit transaction when several statements must succeed together.
Example: transfer with error handling
SET XACT_ABORT ON makes SQL Server roll back the whole transaction if any statement fails. The CATCH block rolls back and re-throws the error so the caller knows.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW; -- pass the error on
END CATCH;Isolation levels in one minute
Isolation decides what a transaction can see of other unfinished work. SQL Server's default is READ COMMITTED: you never read uncommitted changes, but data can change between two reads. Stricter levels such as REPEATABLE READ and SERIALIZABLE prevent that at the cost of more locking. SNAPSHOT isolation uses row versions to avoid blocking readers. In EF Core, SaveChanges already wraps its changes in a transaction.
Common mistakes
- Keeping a transaction open while waiting for user input or a slow API call, which blocks other users.
- Forgetting to roll back on error, leaving locks held.
- Using NOLOCK hints to hide blocking, which can return wrong data.
- Assuming several separate EF Core SaveChanges calls are one transaction.
Interview question
What does ACID mean?
ACID describes the guarantees of a database transaction. Atomicity: all changes happen or none do. Consistency: the transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other's uncommitted changes, according to the isolation level. Durability: once committed, changes survive failures such as a crash or power loss.
Want the full details? Read the official Microsoft documentation for this topic.