Transactions

A transaction groups multiple statements into a single all-or-nothing unit of work, ensuring a database never ends up in a half-completed, inconsistent state.

BEGIN, COMMIT, and ROLLBACK

A transaction begins with BEGIN TRANSACTION. If everything succeeds, COMMIT permanently saves the changes; if something goes wrong, ROLLBACK undoes everything done since the transaction started.

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;

IF @@ERROR = 0
    COMMIT TRANSACTION;
ELSE
    ROLLBACK TRANSACTION;

Why Transactions Matter

Imagine a money transfer between two accounts: deducting from one and adding to the other must happen together, or not at all. Without a transaction, a crash between the two UPDATE statements would leave money simply gone — deducted from one account but never added to the other.

Common Mistakes

• Forgetting to COMMIT or ROLLBACK, leaving a transaction open and holding locks that block other users.
• Wrapping unrelated statements into one large transaction, unnecessarily increasing lock contention.
• Assuming a transaction automatically rolls back on any error without an explicit ROLLBACK or proper error handling.
• Not testing failure scenarios, only ever verifying the "happy path" where every statement succeeds.