SQL transactions are used to manage actions that must be executed together. If one action fails, the transaction ensures that none of the actions affect the database. This is crucial for maintaining data integrity.

1. BEGIN Transaction

Begins a new transaction. It’s the starting point from where the changes start being tracked.

BEGIN TRANSACTION;

2. COMMIT Transaction

Commits the transaction, making all changes permanent. This is used when all actions within the transaction are successful.

COMMIT TRANSACTION;

3. ROLLBACK Transaction

Rolls back the transaction, undoing all changes made within the transaction. This is used when one or more actions in the transaction fail.

ROLLBACK TRANSACTION;

Example:

Imagine you are managing bank accounts and need to transfer money from one account to another. Both the debit from one account and the credit to another must succeed or fail together to maintain the accounts’ balance integrity.

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2;

-- If both updates succeed
COMMIT TRANSACTION;

-- If one of the updates fails, for example due to a constraint violation
ROLLBACK TRANSACTION;

Using transactions ensures that your database remains consistent, even in the face of errors or failures.