Transactions

A transaction groups multiple statements so they either all succeed or all fail together — critical for operations like transferring money between two accounts.

Basic transaction

BEGIN TRANSACTION;

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

COMMIT TRANSACTION;

Rolling back on error

BEGIN TRY
    BEGIN TRANSACTION;

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

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH;

The ACID properties

• Atomicity — all statements succeed or none do.
• Consistency — the database moves from one valid state to another.
• Isolation — concurrent transactions don't interfere with each other.
• Durability — once committed, changes survive a crash.