Why transactions ensure data integrity in MySQL

Introduction

Transactions help keep data correct and safe by making sure all parts of a task finish together or not at all.

When transferring money between bank accounts to avoid losing or duplicating money.

When updating multiple related records that must stay consistent.

When saving a group of changes that should only apply if all succeed.

When deleting data that affects other tables and must be done carefully.

Syntax

MySQL

START TRANSACTION;
-- SQL statements here
COMMIT;
-- or
ROLLBACK;

Use START TRANSACTION to begin a transaction.

Use COMMIT to save changes or ROLLBACK to undo if something goes wrong.

Examples

This moves 100 units from account 1 to account 2 safely.

MySQL

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

This inserts an order but then cancels it, so no change happens.

MySQL

START TRANSACTION;
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 5, 2);
ROLLBACK;

Sample Program

This example creates two accounts, transfers 200 from account 1 to account 2 inside a transaction, and then shows the updated balances.

MySQL

CREATE TABLE accounts (id INT PRIMARY KEY, balance INT);
INSERT INTO accounts VALUES (1, 500), (2, 300);

START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

SELECT * FROM accounts ORDER BY id;

Important Notes

Transactions make sure either all changes happen or none do, preventing partial updates.

If an error occurs during a transaction, use ROLLBACK to undo all changes.

Transactions are important for keeping data trustworthy in multi-step operations.

Summary

Transactions group multiple steps into one safe action.

They prevent data errors by ensuring all or nothing changes.

Use START TRANSACTION, COMMIT, and ROLLBACK to control transactions.