🔄 Transaction Simulator

Learn how ACID properties ensure data integrity through interactive simulations of database transactions, concurrency control, and isolation levels.

ACID Properties

Atomicity

A transaction is an all-or-nothing operation. If any part fails, the entire transaction is rolled back as if it never happened.

In a fund transfer, both the debit and credit must succeed. If the credit fails, the debit is reversed.

Consistency

Transactions bring the database from one valid state to another. All rules, constraints, and triggers are preserved.

After a transfer, the total money across all accounts remains the same — no money is created or destroyed.

Isolation

Concurrent transactions execute as if they were run sequentially. Intermediate states are invisible to other transactions.

Two concurrent transfers from the same account won't interfere — each sees a consistent snapshot.

Durability

Once committed, a transaction's changes survive system failures, crashes, or power losses permanently.

After the bank confirms a transfer, the new balances persist even if the server crashes immediately after.

Interactive Transaction Simulator

Account A

$1,000

Initial: $1,000

Account B

$500

Initial: $500

BEGIN TRANSACTION

Start a new database transaction

DEBIT $200 FROM Account A

Balance: $1,000 → $800

CREDIT $200 TO Account B

Balance: $500 → $700

COMMIT

Changes permanently saved!

Transaction Log

No operations yet. Click a button above to start a simulation.

Concurrency Control

Concurrency Problems

Dirty Read

Reading uncommitted data from another transaction that may later be rolled back.

Transaction A reads balance $800 before Transaction B rolls back its debit, so A saw a phantom state.

Non-repeatable Read

Reading the same row twice within a transaction but getting different values each time.

Transaction A reads $1000, Transaction B updates it to $900 and commits, then A reads $900 — inconsistent.

Phantom Read

A query returns different sets of rows when executed twice within the same transaction.

A reads accounts with balance > $500 getting 3 rows, then B inserts a new $700 account, A re-reads and gets 4 rows.

Isolation Levels Comparison

Isolation Level	Dirty Read	Non-repeatable Read	Phantom Read	Notes
Read Uncommitted	✔️	✔️	✔️	No isolation; all problems can occur.
Read Committed	❌	✔️	✔️	Default in PostgreSQL, Oracle, SQL Server.
Repeatable Read	❌	❌	✔️	Default in MySQL/InnoDB.
Serializable	❌	❌	❌	Fully isolated; slowest but safest.

SQL Transaction Syntax

Basic Transaction

BEGIN TRANSACTION;

UPDATE accounts 
SET balance = balance - 200 
WHERE id = 1;

UPDATE accounts 
SET balance = balance + 200 
WHERE id = 2;

COMMIT;

A complete fund transfer wrapped in a transaction. Both UPDATEs succeed or neither does.

Transaction with ROLLBACK

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 200 WHERE id = 1;

-- Simulate error
IF @@ERROR > 0
    ROLLBACK;

UPDATE accounts SET balance = balance + 200 WHERE id = 2;

COMMIT;

Error handling with ROLLBACK — if the credit fails, the debit is undone.

Using SAVEPOINT

BEGIN TRANSACTION;

SAVEPOINT sp1;

UPDATE products SET stock = stock - 1 WHERE id = 101;

IF @@ROWCOUNT = 0
    ROLLBACK TO SAVEPOINT sp1;

RELEASE SAVEPOINT sp1;

COMMIT;

SAVEPOINT lets you roll back part of a transaction without aborting entirely.

Setting Isolation Level

SET TRANSACTION 
ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT balance FROM accounts 
WHERE id = 1;

-- Other transactions cannot modify
-- accounts until this one commits

COMMIT;

Serializable isolation prevents all concurrency issues at the cost of performance.

Interview Questions

What is a database transaction and why is it important?

Click to reveal answer

A transaction is a sequence of database operations executed as a single logical unit of work. It's important because it ensures data integrity — either all operations succeed (commit) or none do (rollback), preventing partial updates and data corruption in multi-step operations like bank transfers or order processing.

Explain the ACID properties in detail with real-world examples.

Click to reveal answer

Atomicity: All-or-nothing execution (bank transfer: debit + credit both succeed or both fail). Consistency: Valid state transitions (invariants like total balance preserved). Isolation: Concurrent transactions don't interfere (two withdrawals from same account are serialized). Durability: Committed changes persist (survive power loss, crashes via WAL write-ahead logging).

What is the difference between COMMIT and ROLLBACK?

Click to reveal answer

COMMIT permanently saves all changes made during the current transaction, making them visible to other transactions. ROLLBACK undoes all changes made since the transaction began (or since a SAVEPOINT), restoring the database to its previous consistent state. Think of COMMIT as 'save' and ROLLBACK as 'undo all'.

What are the four isolation levels in SQL and what problems do they prevent?

Click to reveal answer

Read Uncommitted (no prevention), Read Committed (prevents dirty reads), Repeatable Read (prevents dirty + non-repeatable reads), Serializable (prevents all: dirty, non-repeatable, and phantom reads). Higher isolation = better consistency but lower concurrency and performance.

What is a deadlock in database transactions and how can it be resolved?

Click to reveal answer

A deadlock occurs when two or more transactions each hold locks the other needs, causing them to wait indefinitely. Databases detect deadlocks via wait-for graphs and resolve them by aborting one transaction (the 'victim'), rolling it back, and allowing others to proceed. Prevention: acquire locks in a consistent order, use timeouts, keep transactions short.