Transactions and ACID Properties in SQL

11/22/2025
All Articles

SQL transactions and ACID properties tutorial with examples for beginners

Transactions and ACID Properties in SQL

Transactions and ACID Properties in SQL Explained with Examples (Beginner Guide)

A transaction in SQL is a sequence of one or more SQL operations that are executed as a single logical unit of work. Transactions are crucial for maintaining data integrity, especially in systems where multiple users access or modify data at the same time.

In this beginner-friendly SQL tutorial, you will learn:

  • What a transaction is

  • Why transactions are important

  • SQL commands for transactions

  • What ACID properties are

  • Real-world examples

  • Best practices

What Is a Transaction in SQL?

A transaction is a group of operations that are treated as a single unit.

A transaction must follow this rule:

Either all operations succeed or none of them are applied.

Example:

  • Transfer money from Account A to B

  • Debit from A & credit to B must both succeed
     

Without transactions, concurrent operations could lead to race conditions, dirty reads, lost updates, or phantom data—issues that erode trust and cause financial or operational damage. In 2026, with microservices, real-time analytics, and AI-driven apps pushing concurrency to new heights, transactions remain essential for scalable, reliable data integrity.

Transaction Control Commands

SQL provides the following commands:

Command Description
BEGIN / START TRANSACTION Start a new transaction
COMMIT Save changes permanently
ROLLBACK Undo changes
SAVEPOINT Set intermediate checkpoints

1.BEGIN / START TRANSACTION

Start a new transaction.

START TRANSACTION;

2.COMMIT

Saves all changes.

COMMIT;

3.ROLLBACK

Reverts all changes since the last COMMIT.

ROLLBACK;

4.SAVEPOINT

Creates a point inside a transaction to roll back to.

SAVEPOINT sp1;

Rollback to that point:

ROLLBACK TO sp1;

Example: Bank Transaction (Debit + Credit)

The classic real-world example that demonstrates why database transactions are essential is a simple bank money transfer—often called the "debit-credit" or "account-to-account transfer" scenario. This example perfectly illustrates how transactions prevent financial disasters by ensuring operations happen reliably together.

Imagine you're using a banking app to transfer $500 from your Checking Account (Account A) to your Savings Account (Account B). Without transactions, a failure could lead to money disappearing or being duplicated. With proper transactions, the system guarantees safety.

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

If any step fails:

ROLLBACK;

Example: Using SAVEPOINT

START TRANSACTION;

UPDATE products SET stock = stock - 1 WHERE id = 10;
SAVEPOINT stock_updated;

UPDATE orders SET status = 'Confirmed' WHERE id = 201;
-- Error occurs here

ROLLBACK TO stock_updated;
COMMIT;

What Are ACID Properties?

Transactions follow ACID properties to ensure reliability.

ACID stands for:

  • A – Atomicity

  • C – Consistency

  • I – Isolation

  • D – Durability

1.Atomicity

"All or nothing."
A transaction is fully completed or completely rolled back.

Example: Payment process—either money is debited & credited, or nothing happens.

2.Consistency

The database must remain valid and follow all rules before and after the transaction.

Example:

  • Balance cannot go negative if constraints prevent it.

3.Isolation

Multiple transactions running simultaneously should not interfere with each other.

Example:

  • Two users booking the same ticket should not conflict.

4.Durability

Once a transaction is committed, data must be permanently saved—even during power failure.

Example:

  • Bank transaction committed → money is safe.

Isolation Levels (Advanced)

SQL provides multiple isolation levels:

  • READ UNCOMMITTED

  • READ COMMITTED

  • REPEATABLE READ

  • SERIALIZABLE

These control how transaction interference is handled.

Database transactions are essential in scenarios where multiple related operations must succeed or fail together to maintain data integrity, prevent financial loss, or avoid business errors. The most common and critical examples include:

  1. Banking system (money transfer)
    Debit from one account + credit to another must be atomic. Partial failure (e.g., debit succeeds but credit fails) would cause money to disappear.

  2. E-commerce order placement
    Check stock availability → reserve/decrement inventory → create order → process payment. If payment fails, inventory must be restored to avoid overselling.

  3. ATM withdrawals
    Verify sufficient balance → deduct amount → dispense cash → update account. Transaction rollback prevents dispensing cash without debiting the account (or vice versa).

  4. Inventory deduction after purchase
    In warehouses/retail: decrement stock quantity only if the order is confirmed and paid. Prevents negative stock or selling items that no longer exist.

  5. Ticket booking / reservation systems (flights, hotels, events, trains)
    Check seat/availability → hold/reserve → process payment → confirm booking. Rollback frees the seat if payment fails, avoiding double-booking or orphaned holds.

 

Summary

In this SQL Transactions and ACID Properties tutorial, you learned:

  • What transactions are and why they matter

  • How COMMIT, ROLLBACK, and SAVEPOINT work

  • The four ACID properties

  • Real-world examples and best practices

Transactions ensure reliability, consistency, and safety in modern database systems.

Article