Database Transactions ACID Isolation Levels & Best Practices

Introduction

Database transactions are the backbone of reliable and consistent data management in modern applications. When you hear the term database transactions, ACID, isolation levels & best practices, it refers to how databases safely handle multiple operations as a single unit of work.

Imagine Ahmad transferring PKR 5,000 from his bank account in Lahore to Fatima’s account in Karachi. This process involves multiple steps:

• Deducting money from Ahmad’s account
• Adding money to Fatima’s account

If something fails in between, the system must ensure no money is lost or duplicated. This is where database transactions come in.

For Pakistani students building real-world applications—whether fintech apps, e-commerce platforms, or student portals—understanding transactions ensures your systems are reliable, secure, and scalable.

Prerequisites

Before diving into this tutorial, you should have:

• Basic knowledge of databases (tables, rows, columns)
• Familiarity with SQL (SELECT, INSERT, UPDATE)
• Understanding of relational databases like MySQL or PostgreSQL
• Basic programming knowledge (JavaScript, Python, or Java helpful but not required)

Core Concepts & Explanation

What is a Database Transaction?

A database transaction is a sequence of operations performed as a single logical unit of work. Either all operations succeed, or none are applied.

Example:
Ali is booking a train ticket:

1. Deduct balance
2. Reserve seat
3. Confirm booking

If step 2 fails, step 1 must be reversed.

ACID Properties Explained

ACID is a set of four properties that guarantee reliable transactions:

1. Atomicity (All or Nothing)

• Either the entire transaction completes or nothing happens.
• No partial updates allowed.

Example:
If Ahmad’s PKR 5,000 transfer fails midway, the system rolls back everything.

2. Consistency (Valid State)

• The database must remain in a valid state before and after the transaction.

Example:
A bank balance should never become negative unless allowed.

3. Isolation (No Interference)

• Transactions should not interfere with each other.

Example:
Two users booking the last seat should not both succeed.

4. Durability (Permanent Changes)

• Once committed, changes are permanent—even if the system crashes.

Transaction Isolation Levels

Isolation levels define how transactions interact with each other.

Types of Isolation Levels

1. Read Uncommitted
   • Lowest level
   • Can see uncommitted data (dirty reads)
2. Read Committed
   • Only committed data is visible
3. Repeatable Read
   • Prevents data from changing during a transaction
4. Serializable
   • Highest level
   • Transactions behave as if executed sequentially

Common Transaction Problems

Dirty Read

Reading uncommitted data.

Non-Repeatable Read

Same query returns different results.

Phantom Read

New rows appear during a transaction.

Practical Code Examples

Example 1: Basic Transaction in SQL

BEGIN;

UPDATE accounts
SET balance = balance - 5000
WHERE name = 'Ahmad';

UPDATE accounts
SET balance = balance + 5000
WHERE name = 'Fatima';

COMMIT;

Line-by-line Explanation:

• BEGIN;
  Starts the transaction.
• UPDATE accounts SET balance = balance - 5000 WHERE name = 'Ahmad';
  Deducts PKR 5,000 from Ahmad’s account.
• UPDATE accounts SET balance = balance + 5000 WHERE name = 'Fatima';
  Adds PKR 5,000 to Fatima’s account.
• COMMIT;
  Saves all changes permanently.

Example 2: Real-World Application (Rollback Scenario)

BEGIN;

UPDATE accounts
SET balance = balance - 10000
WHERE name = 'Ali';

-- Simulate error
ROLLBACK;

Line-by-line Explanation:

• BEGIN;
  Starts the transaction.
• UPDATE accounts SET balance = balance - 10000 WHERE name = 'Ali';
  Attempts to deduct PKR 10,000.
• ROLLBACK;
  Cancels the transaction and restores original data.

Example 3: Using Isolation Level

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT * FROM accounts WHERE city = 'Islamabad';

COMMIT;

Line-by-line Explanation:

• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  Sets the strictest isolation level.
• BEGIN;
  Starts the transaction.
• SELECT * FROM accounts WHERE city = 'Islamabad';
  Fetches consistent data.
• COMMIT;
  Ends transaction.

Common Mistakes & How to Avoid Them

Mistake 1: Forgetting COMMIT

If you don’t commit, changes won’t be saved.

Wrong:

BEGIN;
UPDATE accounts SET balance = 1000;

Fix:

COMMIT;

Mistake 2: Using Wrong Isolation Level

Using low isolation in sensitive systems can cause data issues.

Problem:

• Dirty reads in banking apps

Fix:
Use:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Mistake 3: Long Transactions

Keeping transactions open too long can lock tables.

Fix:

• Keep transactions short
• Avoid unnecessary queries inside transactions

Mistake 4: Not Handling Errors

Failing to rollback on errors causes inconsistent data.

Fix:
Always include rollback logic in application code.

Practice Exercises

Exercise 1: Simple Money Transfer

Problem:
Write a transaction to transfer PKR 2,000 from Ahmad to Ali.

Solution:

BEGIN;

UPDATE accounts SET balance = balance - 2000 WHERE name = 'Ahmad';
UPDATE accounts SET balance = balance + 2000 WHERE name = 'Ali';

COMMIT;

Exercise 2: Prevent Dirty Reads

Problem:
Ensure a transaction only reads committed data.

Solution:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

SELECT * FROM accounts;

COMMIT;

Frequently Asked Questions

What is a database transaction?

A database transaction is a group of operations executed as a single unit. Either all operations succeed or none are applied, ensuring data integrity.

What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable and safe database transactions.

How do I rollback a transaction?

You can use the ROLLBACK command to undo changes made during a transaction if an error occurs.

What is the best isolation level?

It depends on your use case. For banking systems, Serializable is best. For general apps, Read Committed is commonly used.

Why are transactions important?

Transactions prevent data corruption, ensure consistency, and handle failures gracefully—critical for real-world systems like banking or e-commerce.

Summary & Key Takeaways

• Database transactions ensure safe and reliable data operations
• ACID properties guarantee consistency and durability
• Isolation levels control how transactions interact
• Always use COMMIT or ROLLBACK appropriately
• Choose isolation levels based on application needs
• Keep transactions short and efficient

Next Steps & Related Tutorials

To continue learning, explore these tutorials on theiqra.edu.pk:

• Learn database fundamentals with the MySQL Tutorial
• Dive deeper into advanced queries in the PostgreSQL Tutorial
• Understand backend development with a Node.js Database Integration Guide
• Explore ORM tools like Sequelize and Prisma for real-world apps

By mastering database transactions, you’re taking a big step toward building robust, production-ready applications—something every Pakistani developer should aim for 🚀