intermediateData IntegrityFree Guide

Database Transactions Explained

Definition

A database transaction is a sequence of SQL operations executed as a single atomic unit — all operations succeed together or all are rolled back, leaving the database in a consistent state.

Introduction to Database Transactions Explained

Transactions are the safety mechanism that makes relational databases reliable for critical operations like financial transfers, order processing, and inventory management. Without transactions, a server crash mid-operation could leave data in an inconsistent state — money debited but not credited, orders created but not fulfilled.

Key Takeaways

  • Atomicity: all-or-nothing — partial commits are impossible
  • Consistency: constraints, triggers, and rules are enforced on every commit
  • Isolation: concurrent transactions don't see each other's uncommitted work
  • Durability: committed data survives power failures (written to WAL/redo log first)
  • Isolation levels trade consistency guarantees for concurrency performance
  • Deadlocks are automatically detected; one transaction is chosen as the victim

Real-World Examples & SQL Schema

1

Bank Transfer Transaction

BEGIN;

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

-- Only commit if both updates succeeded
COMMIT;

-- On any error:
-- ROLLBACK;

The transfer is atomic — either both updates happen or neither does. No partial state is possible.

Run code in Playground
2

Isolation Levels

-- Set isolation level for a session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
  SELECT balance FROM accounts WHERE id = 1; -- reads committed data
COMMIT;

-- Highest isolation (slowest):
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  SELECT SUM(balance) FROM accounts; -- repeatable, no phantoms
COMMIT;

Higher isolation levels prevent more anomalies but reduce concurrency.

Run code in Playground

Primary Use Cases

Financial operations (transfers, payments, billing)

Order processing (create order + reduce inventory atomically)

User registration (create account + send verification atomically)

Data migrations that must be reversible

Batch operations where partial success is worse than complete failure

Frequently Asked Questions

What are ACID properties in database transactions?
ACID stands for: Atomicity (a transaction is all-or-nothing — either all operations commit or all are rolled back), Consistency (the database moves from one valid state to another, respecting all constraints and rules), Isolation (concurrent transactions execute as if they were sequential — they don't see each other's intermediate states), Durability (once committed, data survives system failures — written to persistent storage via write-ahead logging).
What is the difference between READ COMMITTED and REPEATABLE READ isolation?
READ COMMITTED: each statement within a transaction reads the latest committed data. If another transaction commits between two reads in your transaction, you may see different data — non-repeatable reads. REPEATABLE READ: within a transaction, the same query always returns the same data. MySQL InnoDB default. Prevents non-repeatable reads but may still allow phantom reads (new rows appearing).
What causes a database deadlock?
A deadlock occurs when Transaction A holds Lock 1 and waits for Lock 2, while Transaction B holds Lock 2 and waits for Lock 1 — circular wait. Neither can proceed. The database's deadlock detector breaks this by choosing one transaction as the victim (killing it). The victim transaction receives an error and is rolled back. Prevention: always acquire locks in the same order across all transactions.

Related Concepts

IndexingNormalizationReplicationAcid Properties