Kuidas töötavad tehingud SQL Serveris?

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

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

Transactions start with BEGIN TRANSACTION and end with COMMIT (to confirm changes) or ROLLBACK (to revert 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;