Kekkei - Advancing Financial Science For Everyone

Introduction to ACID Properties

Why do we trust databases with critical data like bank account balances, medical records, or e-commerce orders? Because databases follow ACID principles - a set of guarantees that ensure data remains reliable even when things go wrong.

ACID stands for:

Atomicity - All or nothing
Consistency - Rules are never broken
Isolation - Transactions don't interfere with each other
Durability - Committed data is permanent

These four properties work together to ensure your database is reliable, correct, and predictable.

Real-world scenario: When you transfer money between bank accounts, ACID ensures:

Both debit and credit happen, or neither does (Atomicity)
Your balance never goes negative if that's forbidden (Consistency)
Other people's transactions don't corrupt your transfer (Isolation)
Once confirmed, the transfer survives even if the server crashes (Durability)

A set of four properties (Atomicity, Consistency, Isolation, Durability) that guarantee database transactions are processed reliably and maintain data integrity.

ACID Properties Overview

Property	Question It Answers	Database Feature
Atomicity	Do all operations succeed or fail together?	Transactions (BEGIN, COMMIT, ROLLBACK)
Consistency	Are database rules always enforced?	Constraints, triggers, rules
Isolation	Do concurrent transactions interfere?	Isolation levels, locking
Durability	Is committed data permanent?	Write-ahead logging, backups

Atomicity - All or Nothing

Atomicity ensures that a transaction is treated as a single, indivisible unit. Either all operations in the transaction complete successfully, or none of them do.

Think of it like mailing a package: either the entire package arrives at its destination, or none of it does. You never get "half a package."

The property that ensures a transaction is indivisible - all operations succeed together (commit) or all fail together (rollback). No partial completion.

accounts

account_id	account_holder	balance
1	Alice	1000.00
2	Bob	500.00

Example: Transfer $300 from Alice to Bob

sql

BEGIN;

-- Step 1: Deduct from Alice
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;

-- Step 2: Add to Bob
UPDATE accounts SET balance = balance + 300 WHERE account_id = 2;

COMMIT;

With atomicity:

If both updates succeed → COMMIT → both changes are saved
If either update fails → ROLLBACK → neither change is saved
No middle ground - you never lose the $300!

Without atomicity (dangerous!):

sql

-- No transaction (NOT ATOMIC)
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
-- CRASH HERE!
UPDATE accounts SET balance = balance + 300 WHERE account_id = 2;

If the system crashes after the first UPDATE:

Alice loses $300 (balance = 700)
Bob never receives it (balance = 500)
The money vanishes!

This violates atomicity. Transactions prevent this.

Key insight: Atomicity prevents partial failures. In a transaction, it's impossible for only some operations to succeed while others fail.

Consistency - Rules Are Never Broken

Consistency ensures that a transaction takes the database from one valid state to another valid state. All database rules (constraints, triggers, cascades) must be satisfied before and after the transaction.

Think of it like a game: you must follow all the rules, and at the end of your turn, the game must still be in a valid state.

The property that ensures transactions maintain all database rules and constraints. The database moves from one valid state to another - never to an invalid state.

Common consistency rules:

Primary keys must be unique
Foreign keys must reference existing rows
Check constraints must be satisfied
Data types must match
NOT NULL columns must have values
Custom business rules must be enforced

products

product_id	product_name	price	stock
1	Laptop	999.99	10
2	Mouse	29.99	50

Constraint: price must be positive, stock must be non-negative

sql

-- This violates consistency (negative price)
BEGIN;
UPDATE products SET price = -50 WHERE product_id = 2;
COMMIT;

-- Database rejects this!
-- Error: CHECK constraint failed: price > 0

The database refuses to commit because it would violate the constraint. Consistency is preserved!

Another example: Foreign key consistency

orders

order_id	customer_id	product_id	quantity
1	101	1	2

sql

-- Try to create order for non-existent customer
BEGIN;
INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (2, 999, 1, 1);  -- customer 999 doesn't exist!
COMMIT;

-- Error: Foreign key constraint failed
-- customer_id 999 does not exist in customers table

Consistency in action:

sql

-- Order and inventory update (consistent)
BEGIN;

-- Create order
INSERT INTO orders (order_id, customer_id, product_id, quantity)
VALUES (3, 101, 1, 2);

-- Reduce inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 1;

-- Verify stock didn't go negative
SELECT stock FROM products WHERE product_id = 1;
-- Result: 8 (started at 10, removed 2)

COMMIT;  -- Both operations maintain consistency

Developer responsibility: While databases enforce structural consistency (constraints), you're responsible for logical consistency (business rules). Always validate that your transactions make business sense!

Isolation - Transactions Don't Interfere

Isolation ensures that concurrent transactions don't interfere with each other. Even when multiple transactions run simultaneously, each one appears to execute alone.

Think of it like separate phone calls: you can talk to someone while others are making their own calls, but you don't hear their conversations mixed into yours.

The property that ensures concurrent transactions execute independently without interfering with each other. Each transaction sees a consistent view of the data.

Scenario: Two users trying to book the last seat on a flight

flights

flight_id	destination	seats_available
100	NYC	1

Without proper isolation (PROBLEM!):

sql

-- User A (starts booking)
SELECT seats_available FROM flights WHERE flight_id = 100;
-- Returns: 1 seat available

-- User B (also starts booking at same time)
SELECT seats_available FROM flights WHERE flight_id = 100;
-- Returns: 1 seat available

-- User A (books the seat)
UPDATE flights SET seats_available = 0 WHERE flight_id = 100;

-- User B (also books the seat!)
UPDATE flights SET seats_available = 0 WHERE flight_id = 100;

-- PROBLEM: Both users think they booked the seat!

With proper isolation (CORRECT):

sql

-- User A
BEGIN;
SELECT seats_available FROM flights WHERE flight_id = 100 FOR UPDATE;
-- Locks the row
UPDATE flights SET seats_available = 0 WHERE flight_id = 100;
COMMIT;

-- User B (tries to read while User A's transaction is active)
BEGIN;
SELECT seats_available FROM flights WHERE flight_id = 100 FOR UPDATE;
-- WAITS until User A commits
-- Then sees: 0 seats available
-- Can show "Sorry, flight is full" message
ROLLBACK;

With isolation, User B's transaction waits until User A finishes. This prevents double-booking!

Isolation levels control the strength of isolation:

Isolation Levels

Level	Prevents	Use Case
READ UNCOMMITTED	Nothing (weakest)	Rarely used, fast but unsafe
READ COMMITTED	Dirty reads	Most common default
REPEATABLE READ	Dirty + non-repeatable reads	Good for reports
SERIALIZABLE	All anomalies (strongest)	Critical transactions

sql

-- Set isolation level for critical transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Booking logic here
COMMIT;

Balance: Higher isolation = more data consistency, but slower performance. Most applications use READ COMMITTED as a good middle ground.

Durability - Committed Data Is Permanent

Durability ensures that once a transaction is committed, the changes are permanent - even if the system crashes immediately after.

Think of it like saving a document: once you click "Save" and see the confirmation, your work is safe even if your computer crashes.

The property that ensures committed transactions are permanently recorded. Once COMMIT succeeds, the data survives power failures, crashes, or other system failures.

sql

BEGIN;
INSERT INTO orders (order_id, customer_id, total)
VALUES (1001, 42, 299.99);
COMMIT;
-- User sees: "Order confirmed! Order #1001"

-- POWER FAILURE! Server crashes!

-- After restart:
SELECT * FROM orders WHERE order_id = 1001;

The order survives the crash! This is durability in action.

How databases achieve durability:

Write-Ahead Logging (WAL): Write changes to a log file before modifying actual data
Checkpoints: Periodically flush cached data to disk
Transaction logs: Keep a permanent record of all committed transactions
Replication: Copy data to multiple servers for redundancy
Backups: Regular full and incremental backups

Without durability (unreliable!):

sql

-- Imagine a database that only keeps data in memory (RAM)
BEGIN;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 1;
COMMIT;
-- User sees: "Deposit confirmed!"

-- CRASH!
-- All data in memory is LOST!

-- After restart:
-- The $1000 deposit is gone!

The durability guarantee:

If COMMIT succeeds, data is saved to persistent storage (disk)
If COMMIT fails, you get an error and can retry
You never get a false "success" message

Real-world durability example:

sql

-- E-commerce checkout
BEGIN;

-- Deduct inventory
UPDATE products SET stock = stock - 1 WHERE product_id = 201;

-- Create order
INSERT INTO orders (customer_id, product_id, total)
VALUES (42, 201, 79.99);

-- Charge payment (simulated)
INSERT INTO payments (order_id, amount, status)
VALUES (LAST_INSERT_ID(), 79.99, 'completed');

COMMIT;  -- Success message sent to customer

-- Even if server crashes NOW, all changes are saved
-- Customer can check order history and see their order
-- Warehouse can process the order
-- Payment was recorded

Never trust uncommitted data! If you haven't seen a COMMIT success message, assume the data might not be saved. Always handle errors and retry if needed.

ACID Working Together

ACID properties don't work in isolation - they complement each other to provide complete reliability.

Example: Bank transfer that demonstrates all four properties

sql

-- Transfer $500 from Alice (account 1) to Bob (account 2)
BEGIN;

-- Atomicity: Both updates happen or neither
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- Consistency: Constraints checked (balance >= 0, etc.)
-- If Alice doesn't have $500, transaction fails

-- Isolation: Other transactions see either old or new state, never partial
-- Another user checking balances won't see money "in transit"

COMMIT;

-- Durability: Once committed, transfer survives crashes
-- Both Alice and Bob's new balances are permanent

What each property guarantees:

Atomicity: If the deduction succeeds but addition fails, the deduction is rolled back. No money is lost.
Consistency: If Alice has only $400, the transaction fails completely. Balance constraints are never violated.
Isolation: If Charlie checks Alice's balance during the transfer, he sees either $1000 (before) or$ 500 (after), never $500 with Bob still at$ 500.
Durability: Once COMMIT returns, even if the server explodes, Alice has $500 and Bob has$ 1000. The transfer happened.

ACID in Different Database Systems

Most modern relational databases are ACID-compliant, but implementation details vary:

Database ACID Support

Database	ACID Support	Notes
PostgreSQL	Full	Strict ACID compliance
MySQL (InnoDB)	Full	InnoDB engine is ACID; MyISAM is not
SQL Server	Full	Enterprise-grade ACID support
Oracle	Full	Advanced isolation and recovery
SQLite	Full	ACID even for embedded database
MongoDB	Partial	ACID for single documents; multi-document requires replica sets
Redis	Partial	Atomic operations, but durability is optional

Important: Some NoSQL databases sacrifice ACID for performance and scalability. Always verify ACID support if you need strong consistency guarantees!

Trade-offs and Performance

ACID guarantees come with performance costs. Understanding trade-offs helps you make informed decisions:

Performance impacts:

Atomicity: Transaction overhead (BEGIN/COMMIT)
Consistency: Constraint checking takes time
Isolation: Locking reduces concurrency
Durability: Disk writes are slower than memory writes

When to relax ACID (carefully!):

Read-heavy applications: Use READ UNCOMMITTED for non-critical reads
High-performance caching: Accept eventual consistency
Analytics on copies: Run reports on read replicas
Batch processing: Disable constraints during bulk loads, re-enable after

Example: Relaxing durability for speed

sql

-- PostgreSQL: Asynchronous commit (faster, but slight risk)
SET synchronous_commit = OFF;
BEGIN;
-- Fast writes
COMMIT;
-- Returns immediately, writes happen in background

-- Risk: If crash happens before write completes, data might be lost

Best practice: Keep ACID enabled for critical transactions (payments, bookings, inventory). Relax it only for non-critical operations like logging or caching.

Common Mistakes to Avoid

Don't make these mistakes:

Forgetting transactions for multi-step operations: ❌
- Always wrap related operations in BEGIN...COMMIT
- Without it, you lose atomicity
Assuming auto-commit handles everything: ❌
- Auto-commit commits each statement individually
- Multi-step operations need explicit transactions
Not checking for constraint violations: ❌
- Consistency failures throw errors
- Always handle exceptions and rollback
Using wrong isolation level: ⚠️
- READ UNCOMMITTED for critical data = danger
- SERIALIZABLE for everything = slow
- Choose based on your needs
Trusting success without COMMIT: ❌
- Changes aren't durable until COMMIT succeeds
- Always wait for COMMIT confirmation
Mixing transactional and non-transactional tables: ❌
- MySQL: InnoDB supports transactions, MyISAM doesn't
- Mixing them breaks atomicity
Not testing failure scenarios: ❌
- Test what happens when transactions fail
- Ensure rollback works correctly
Keeping transactions open too long: ❌
- Long transactions lock resources
- Complete them quickly

Practical Examples

Example 1: E-commerce order with ACID

sql

BEGIN;  -- Atomicity starts

-- Check inventory (Consistency: can't sell what we don't have)
SELECT stock FROM products WHERE product_id = 100;
-- Result: 5 units

-- Create order
INSERT INTO orders (customer_id, total) VALUES (42, 199.99);
SET @order_id = LAST_INSERT_ID();

-- Add order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 100, 2, 99.99);

-- Reduce inventory
UPDATE products SET stock = stock - 2 WHERE product_id = 100;

-- Verify we didn't oversell
IF (SELECT stock FROM products WHERE product_id = 100) < 0 THEN
  ROLLBACK;  -- Atomicity: undo everything
ELSE
  COMMIT;  -- Durability: save everything
END IF;

Example 2: Salary adjustment with audit trail

sql

BEGIN;

-- Update salary
UPDATE employees
SET salary = 95000
WHERE employee_id = 42;

-- Log the change (audit trail)
INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_at)
VALUES (42, 85000, 95000, NOW());

-- Both happen atomically or not at all
COMMIT;

Key Takeaways

What you learned:

✅ ACID ensures database reliability: Atomicity, Consistency, Isolation, Durability ✅ Atomicity: All operations succeed or all fail (no partial completion) ✅ Consistency: Database rules are always enforced (constraints, foreign keys) ✅ Isolation: Concurrent transactions don't interfere with each other ✅ Durability: Committed data survives crashes and power failures ✅ Transactions (BEGIN/COMMIT/ROLLBACK) provide atomicity ✅ Constraints and triggers enforce consistency ✅ Isolation levels balance consistency vs performance ✅ Write-ahead logging provides durability ✅ All four properties work together for complete reliability

Real-world importance:

Banking: Prevent money from vanishing in transfers
E-commerce: Ensure orders and inventory stay synchronized
Healthcare: Maintain critical patient data integrity
Bookings: Prevent double-booking of limited resources

Remember: ACID isn't optional for critical data. It's the foundation of database reliability!

Practice Exercise: Think about these scenarios and which ACID property prevents each problem:

Money deducted but not credited → Atomicity
Booking 11 of 10 available seats → Consistency
Two users booking the same last seat → Isolation
Confirmed order lost after crash → Durability

Introduction to ACID PropertiesFocusStart Focus Mode

Atomicity - All or NothingFocusStart Focus Mode

Consistency - Rules Are Never BrokenFocusStart Focus Mode

Isolation - Transactions Don't InterfereFocusStart Focus Mode

Durability - Committed Data Is PermanentFocusStart Focus Mode

ACID Working TogetherFocusStart Focus Mode

ACID in Different Database SystemsFocusStart Focus Mode

Trade-offs and PerformanceFocusStart Focus Mode

Common Mistakes to AvoidFocusStart Focus Mode

Practical ExamplesFocusStart Focus Mode

Key TakeawaysFocusStart Focus Mode