Why transactions are needed in SQL

Introduction

Transactions help keep data safe and correct when many people use a database at the same time. They make sure all parts of a task finish together or not at all.

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

When booking a flight and a hotel together to make sure both reservations happen or none.

When updating multiple related records that must stay consistent.

When multiple users are editing data at the same time to prevent conflicts.

When saving a large set of changes that should be treated as one unit.

Syntax

SQL

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

Use BEGIN TRANSACTION to start 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.

SQL

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

This tries to add an order but then cancels it, so no change happens.

SQL

BEGIN TRANSACTION;
INSERT INTO orders (id, product) VALUES (1, 'Book');
ROLLBACK;

Sample Program

This example transfers 50 units from account 1 to account 2 and then shows the updated balances.

SQL

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

SELECT id, balance FROM accounts ORDER BY id;

Important Notes

Without transactions, partial updates can leave data wrong or incomplete.

Transactions help databases handle errors and multiple users safely.

Summary

Transactions group multiple steps into one safe action.

They ensure data stays correct even if something fails.

Use COMMIT to save changes or ROLLBACK to undo them.