Schema Design & Table Relationships

ANSI SQL Standards & Database Differences

Transaction Control (ACID)

Isolation Levels

Write an ACID-compliant checkout flow for an e-commerce cart

Design a seat reservation system that handles 1,000 concurrent bookings correctly

Identify the isolation level needed for each of: social feed, bank transfer, invoice generation

Implement optimistic locking on a high-read, low-write table and measure retry rates

What does each ACID property prevent?

Explain WAL and why it makes Durability practical.

What is the difference between REPEATABLE READ and SERIALIZABLE?

How does MVCC allow readers and writers to proceed concurrently?

How would you prevent double-booking in a seat reservation system?

What is a deadlock and how do you prevent one?

When would you choose eventual consistency (BASE) over ACID?

PostgreSQL Documentation: Transaction Isolation

MySQL InnoDB: Transaction Model and Locking

Martin Kleppmann — Designing Data-Intensive Applications (Chapter 7)

The original ACID paper: Haerder & Reuter (1983)

SQL Server: Understanding Isolation Levels

ACID is the foundation every production database transaction rests on. You now understand not just what each property means, but the internal mechanisms (WAL, MVCC, undo logs, locking) that make them practical at scale.

Implement a bank transfer and verify atomicity by killing the process mid-transaction

Run two concurrent transactions at READ COMMITTED and observe a non-repeatable read

Reproduce a deadlock and catch the error code in application code

Compare execution plans for a query under READ COMMITTED vs SERIALIZABLE