Introduction

Transaction Control Commands are used to manage changes made by SQL statements in a transaction. They allow you to COMMIT, ROLLBACK, or create SAVEPOINTS during a transaction. These commands ensure reliability, data integrity, and controlled execution of queries in sensitive applications like banking or inventory systems.

1. COMMIT

The COMMIT command is used to permanently save all the changes made in a transaction. Once committed, the changes cannot be undone.

General Syntax:

COMMIT;

Example (Transfer ₹200 from Alice to Bob):

START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';
COMMIT;

2. ROLLBACK

The ROLLBACK command is used to undo changes made during the current transaction if any error occurs.

General Syntax:

ROLLBACK;

Example (Cancel changes if an error occurs):

START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
-- Simulate an error here
ROLLBACK;

3. SAVEPOINT and ROLLBACK TO

SAVEPOINT allows you to set a point within a transaction to which you can later roll back without undoing the entire transaction.ROLLBACK TO reverts the transaction to a specific savepoint.

General Syntax:

SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;

Example (Using savepoint for partial rollback):

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
SAVEPOINT after_deduction;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
ROLLBACK TO after_deduction;
COMMIT;

Note:-

• Transactions work only with storage engines that support them, such as InnoDB.
• By default, MySQL runs in autocommit mode, so every query is automatically committed unless you start a transaction manually:       

SET autocommit = 0;