SQL Transactions

A transaction is a group of one or more SQL operations that are treated as a single unit of work. Either all the operations in the group succeed together, or none of them take effect at all. There is no in-between state.

Think of a bank money transfer: money is debited from Account A and credited to Account B. These are two separate operations. If the debit succeeds but the credit fails due to an error, the money would simply disappear. A transaction ensures that either both steps complete successfully, or neither step is applied — keeping the data safe and consistent.

The ACID Properties

Transactions follow four fundamental principles known as ACID:

Transaction Control Commands

The Reference Table

COMMIT — Saving Changes Permanently

The COMMIT command finalises all changes made since the transaction began. Once committed, the changes cannot be undone with ROLLBACK.

Example: Transfer Money From Ravi to Priya

-- Begin the transaction
START TRANSACTION;

-- Step 1: Deduct 5000 from Ravi's account
UPDATE Accounts SET Balance = Balance - 5000 WHERE AccountID = 1;

-- Step 2: Add 5000 to Priya's account
UPDATE Accounts SET Balance = Balance + 5000 WHERE AccountID = 2;

-- Both steps succeeded — save the changes permanently
COMMIT;

After COMMIT:

Both updates are permanently saved. The total balance in the system remains the same (80000), and the transfer is complete.

ROLLBACK — Undoing Changes

If something goes wrong during a transaction — for example, an error occurs midway — ROLLBACK reverses all changes made since the transaction began, restoring the database to its original state.

Example: Transfer Fails Midway — Roll It Back

-- Begin the transaction
START TRANSACTION;

-- Step 1: Deduct 5000 from Ravi's account
UPDATE Accounts SET Balance = Balance - 5000 WHERE AccountID = 1;

-- An error is detected — the transfer cannot proceed
-- Undo all changes made in this transaction
ROLLBACK;

After ROLLBACK: Ravi's balance goes back to 50000. Nothing changed. Priya's account was never touched.

SAVEPOINT — Checkpoints Inside a Transaction

A SAVEPOINT marks a specific point within a transaction. If something goes wrong after the savepoint, the transaction can be rolled back to that checkpoint instead of undoing everything from the very beginning.

START TRANSACTION;

-- Insert first record
INSERT INTO Students (StudentName, Age, City) VALUES ('Kiran Roy', 21, 'Pune');

-- Create a savepoint after the first insert
SAVEPOINT after_first_insert;

-- Insert second record
INSERT INTO Students (StudentName, Age, City) VALUES ('Deepa Singh', 20, 'Jaipur');

-- Something went wrong with the second insert — roll back only to the savepoint
ROLLBACK TO SAVEPOINT after_first_insert;

-- The first insert (Kiran Roy) is still intact, the second (Deepa Singh) is undone
COMMIT;

After this sequence, Kiran Roy is added to the Students table but Deepa Singh is not.

RELEASE SAVEPOINT

Once a savepoint is no longer needed, it can be released (removed) to free up resources:

RELEASE SAVEPOINT after_first_insert;

Auto-Commit Mode

By default, MySQL runs in auto-commit mode. This means every SQL statement is treated as its own transaction and committed immediately — without needing an explicit COMMIT.

To disable auto-commit and control transactions manually:

-- Disable auto-commit
SET autocommit = 0;

-- Now all statements require an explicit COMMIT or ROLLBACK
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;
COMMIT;

Alternatively, using START TRANSACTION automatically suspends auto-commit for the duration of that transaction.

Practical Example: Student Enrollment With Transaction

START TRANSACTION;

-- Step 1: Add a new student
INSERT INTO Students (StudentName, Age, City, Course, Fees)
VALUES ('Meera Joshi', 22, 'Hyderabad', 'Commerce', 49000);

-- Step 2: Record the fee payment
INSERT INTO Payments (StudentID, Amount, PaymentDate)
VALUES (LAST_INSERT_ID(), 49000, CURDATE());

-- Both steps completed without error — commit the transaction
COMMIT;

If either the student insert or the payment insert fails, ROLLBACK would be called to ensure neither record is left in the database in an incomplete state.

When to Use Transactions

Key Points to Remember

• START TRANSACTION begins a transaction; COMMIT saves it; ROLLBACK undoes it.
• Once COMMIT is executed, the changes become permanent and cannot be rolled back.
• A SAVEPOINT creates a partial recovery point within a transaction.
• MySQL auto-commit is ON by default — START TRANSACTION or SET autocommit = 0 disables it temporarily.
• Transactions protect data integrity when multiple related operations must succeed or fail together.

Summary

Transactions group multiple SQL operations into one safe, all-or-nothing unit. The COMMIT command saves changes permanently, while ROLLBACK undoes them. SAVEPOINT provides fine-grained control by allowing rollback to a specific point within a transaction. Transactions are essential whenever multiple database operations depend on each other — such as financial transfers, order processing, or any multi-step data update scenario.