START TRANSACTION, COMMIT, and ROLLBACK in SQL – Complete Guide

Overview

Transactions in SQL are a way to group multiple operations into a single logical unit that can be executed together or undone if something goes wrong. SQL provides three key commands to manage transactions:

• START TRANSACTION (or BEGIN)
• COMMIT
• ROLLBACK

These commands are essential for ensuring data consistency, reliability, and error recovery in database operations.

1. START TRANSACTION

What It Does:
Begins a new transaction block. All subsequent SQL statements become part of this transaction until you end it with COMMIT or ROLLBACK.

Syntax:

START TRANSACTION;
-- or
BEGIN;

Use Case:
Use this when you want to group multiple changes (like inserts, updates, deletes) into a single atomic unit.

2. COMMIT

What It Does:
Saves all the changes made during the current transaction permanently to the database.

Syntax:

COMMIT;

Use Case:
Use this when all operations are successful and you want to make the changes final.

3. ROLLBACK

What It Does:
Cancels all changes made in the current transaction, reverting the database to its previous state.

Syntax:

ROLLBACK;

Use Case:
Use this when an error occurs or something goes wrong, and you don't want to save any part of the transaction.

Example: Bank Transfer with START, COMMIT, and ROLLBACK

START TRANSACTION;

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

-- Check if both updates succeed
COMMIT;  -- Save changes permanently

If one update fails:

ROLLBACK;  -- Undo everything

When to Use These Commands