Transactions and ACID Properties in SQL

11/22/2025
All Articles

Transactions and ACID Properties in SQL

Transactions and ACID Properties in SQL

Transactions and ACID Properties in SQL – SQL Tutorial for Beginners

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

🔸 Why Use Transactions?

Transactions help ensure:

  • Data safety when executing multiple operations

  • Consistency even after failures

  • Correctness in banking, inventory, booking systems

🔹 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)

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.

🔹 Real-World Use Cases

✔ Banking system (money transfer)

✔ E-commerce order placement

✔ ATM withdrawals

✔ Inventory deduction after purchase

✔ Ticket booking systems

🔹 Best Practices

✔ Always use transactions for critical operations
✔ Use COMMIT only when all steps succeed
✔ Use SAVEPOINT for complex logic
✔ Choose proper isolation levels for performance
✔ Log failed transactions for debugging

⭐ 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.

Meta Description

Learn SQL transactions and ACID properties with examples. Understand COMMIT, ROLLBACK, SAVEPOINT, and how ACID ensures data reliability.

Meta Keywords

sql transactions, acid properties, commit rollback sql, savepoint sql, transaction tutorial, database integrity

Alt Tag

"SQL transactions and ACID properties tutorial with examples for beginners"

Article