++++

Data Engineering

May 2026×9 min read

Use START TRANSACTION, COMMIT, and ROLLBACK to keep multi-step MySQL changes safe and consistent.

15. Transactions

Driptanil DattaSoftware Developer

Transactions

A transaction groups multiple SQL statements into one unit of work. Either all changes are saved, or none of them are.

Why transactions matter

Imagine transferring money from one account to another:

1. Subtract money from account A.
2. Add money to account B.

If the first update succeeds and the second fails, the data is wrong. A transaction protects that workflow.

Basic transaction flow

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

COMMIT permanently saves the changes.

Roll back a transaction

Use ROLLBACK to undo changes made since START TRANSACTION.

START TRANSACTION;
 
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
 
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 999;
 
ROLLBACK;

After rollback, neither update remains.

Preview before commit

You can inspect changes before committing.

START TRANSACTION;
 
UPDATE employees
SET Salary = Salary * 1.10
WHERE Salary < 60000;
 
SELECT *
FROM employees
WHERE Salary < 66000;
 
COMMIT;

If the result looks wrong, run ROLLBACK instead of COMMIT.

Safe update habits

• Always use a specific WHERE clause for updates and deletes.
• Preview affected rows with SELECT.
• Keep transactions short so they do not hold locks longer than necessary.
• Commit only after the full workflow succeeds.

Transactions are one of the main differences between casual SQL scripts and reliable database operations.