What Is a Transaction in SQL? – Complete Guide with Examples

Definition: What Is a SQL Transaction?

A transaction in SQL is a sequence of one or more SQL operations (such as INSERT, UPDATE, DELETE) that are executed as a single unit of work. A transaction must be completed successfully in its entirety or rolled back if any part fails.

Transactions ensure data integrity, consistency, and reliability—especially in multi-user and concurrent environments.

ACID Properties of Transactions

Transactions are governed by the ACID principles, which ensure data is processed reliably:

SQL Transaction Commands

Basic Transaction Syntax

START TRANSACTION;

-- Your SQL operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- Commit if both succeed

If something goes wrong:

ROLLBACK;  -- Undo all changes

Real-World Example: Bank Transfer

START TRANSACTION;

UPDATE bank_accounts SET balance = balance - 500 WHERE account_id = 101;
UPDATE bank_accounts SET balance = balance + 500 WHERE account_id = 202;

-- Check for errors here
COMMIT;

If any UPDATE fails, use ROLLBACK to prevent inconsistent account balances.

SAVEPOINT Example

START TRANSACTION;

INSERT INTO orders VALUES (...);

SAVEPOINT before_update;

UPDATE inventory SET stock = stock - 1 WHERE product_id = 5;

-- Oops! Error occurred
ROLLBACK TO before_update;

COMMIT;

Use SAVEPOINT to selectively undo parts of a transaction without discarding everything.

When to Use Transactions

• Transferring money between accounts
• Booking systems (flights, hotels)
• Invoicing or billing systems
• Any operation involving multiple related table updates

SQL Engines That Support Transactions