How do transactions work in SQL Server?

In SQL Server, a transaction is a sequence of operations that are executed as a single unit. A transaction guarantees the ACID properties:

• Atomicity: all operations within the transaction are either fully completed or not executed at all.
• Consistency: after the transaction completes, the database remains in a correct state.
• Isolation: concurrent transactions do not affect each other.
• Durability: after a transaction is committed, the changes are saved even in case of failures.

Transactions start with BEGIN TRANSACTION and end with COMMIT (to save changes) or ROLLBACK (to undo changes). SQL Server uses a transaction log to ensure reliability and recovery.

Example:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;