Multics, the Relational Model, and ACID: How Databases Got Their Guarantees

Before Databases, There Were Just Files

In the early 1960s, software systems stored data however the programmer felt like it. There were no standards, no abstractions, no way to share data between programs. Each application owned its own files in its own format. If two programs needed the same customer record, one of them made a copy. When the original changed, the copy became stale. The state of enterprise software in 1965 was approximately the state of your Downloads folder today: chaotic, redundant, and nobody fully understands what is in there-and there was no concept of ACID guarantees to keep any of it consistent.

Three ideas published between 1965 and 1981 fixed this. They did not just improve databases. They defined what a database is and what it should guarantee. Every PostgreSQL query, every Django ORM call, every Hibernate session you have ever written runs on top of these three foundational ideas.

Multics and Timesharing (1965)

MIT, Bell Labs, and General Electric collaborated on Multics, a wildly ambitious operating system that introduced timesharing. The idea: split one expensive mainframe's attention across dozens of simultaneous users. Each user got the illusion of having the machine to themselves. In reality the CPU was context-switching between their jobs hundreds of times per second.

Multics was overly complex and ultimately abandoned by Bell Labs. Ken Thompson and Dennis Ritchie took the core ideas and stripped them down into something simpler: Unix. Every server you have ever SSH'd into is a direct descendant of that decision to leave Multics and build something leaner. The insight that computing power could be shared rather than owned set the stage for everything from time-sharing mainframes to cloud computing.

The very first distributed systems problem was multi-tenancy: how to share one machine across many users without them interfering with each other. Every cloud isolation problem today is the same problem at larger scale.

The Relational Model (1970)

Edgar Codd, working at IBM's San Jose research lab, published a paper that would eventually be worth hundreds of billions of dollars. The title was dry: 'A Relational Model of Data for Large Shared Data Banks.' The idea was radical. Before Codd, every database was a labyrinth. Data was stored in hierarchical trees or network graphs, and every application had to know the exact physical layout of data on disk. Reorganize your storage structure and every program that touched it broke.

Codd proposed separating the logical structure of data from its physical storage. Define data as relations (what we now call tables). Define a query language to retrieve it. Let the database engine figure out how to physically store and access the data. Applications should only need to know WHAT they want, not HOW it is stored. IBM ignored the paper for years, considering it too theoretical. Larry Ellison read it and built Oracle. The relational database industry that followed is now worth over 300 billion dollars annually.

The insight that outlasted the relational model itself is the abstraction principle: separate what you want from how you get it. Every API, every cache layer, every microservice boundary you design applies this same principle. The 'what' and the 'how' should live in different places.

ACID Transactions (1981)

Jim Gray asked the most important question in database engineering: what happens when things go wrong halfway through? A bank transfer debits one account and credits another. If the system crashes after the debit but before the credit, the money has vanished. This is not a theoretical edge case. It is the scenario that happens in production at 3 AM on the most inconvenient possible night.

Gray formalized four properties every reliable transaction system needs.

Atomicity: the entire operation either completes or does nothing. There is no half-done state.

Bank Transfer Example

Suppose:
   Account A = ₹5000
   Account B = ₹3000

Transaction:
   Transfer ₹1000 from A to B

Steps:
   1. Deduct ₹1000 from A
   2. Add ₹1000 to B

Successful Execution
  Before:                After:
    A = 5000               A = 4000
    B = 3000               B = 4000

Everything is correct.

Failure Scenario

Suppose:
    Step 1 completed
        A = 4000 but
    Step 2 failed
        because the server crashed.

    Result:
        A = 4000
        B = 3000
 ₹1000 disappeared. This is unacceptable.

Atomicity ensures:
    Transaction Rollback

    Result:
        A = 5000
        B = 3000

No money is lost.

Consistency: the database moves from one valid state to another.

Suppose:
Total Money in System = ₹8000

Before Transfer:
    A = 5000
    B = 3000

After Transfer:
    A = 4000
    B = 4000

Total remains:
    ₹8000

The system remains consistent.

Isolation: concurrent transactions do not interfere with each other.

Suppose:
    Balance = ₹1000

Two ATM withdrawals happen simultaneously.

Transaction T1
    Withdraw ₹500

Transaction T2
    Withdraw ₹500

Without Isolation:
    T1 reads 1000
    T2 reads 1000

    T1 writes 500
    T2 writes 500

Final Balance:
    ₹500
Expected:
    ₹0

Isolation prevents such anomalies.

Result:
    T1 completes first
    T2 sees updated balance

Final Balance:
    ₹0

Durability: once committed, the data survives crashes. These four properties, ACID, became the gold standard for database guarantees.

Suppose:
    Transfer ₹1000

Database returns:
    COMMIT SUCCESS

Immediately after:
    Server Crash

Durability guarantees:
    Transaction is not lost

When the database restarts:
    Data Still Exists

ACID is not just a database spec. It is a design philosophy about what you promise your users. When payment systems go down, it is usually because someone assumed ACID guarantees that their system cannot actually provide.

Gray won the Turing Award. Every payment system, booking engine, and inventory tracker you build needs to decide which ACID properties it requires.

Why This Still Matters for System Design

Modern distributed databases deliberately relax ACID guarantees to achieve horizontal scalability. Cassandra offers eventual consistency instead of strong consistency. Redis offers no durability by default. DynamoDB lets you choose your consistency level per read. Each of these is a deliberate trade-off against ACID, not ignorance of it. Understanding what ACID provides helps you understand exactly what you are giving up when you choose a NoSQL database. The interview question is never 'what is ACID?' The question is always 'what happens to your system when one of these guarantees is absent?'

• No atomicity: partial updates leave data in corrupted intermediate states. Requires compensating transactions or saga patterns to clean up.

• No consistency: the database allows invalid states. Requires application-level validation on every write path.

• No isolation: concurrent requests see each other's in-progress changes. Requires careful locking or optimistic concurrency control.

• No durability: committed data can be lost on crash. Requires application-level retry logic and idempotency.

Further Reading

• Codd (1970): 'A Relational Model of Data for Large Shared Data Banks', the paper that started it all

• Gray (1981): 'The Transaction Concept: Virtues and Limitations'

• Martin Kleppmann's 'Designing Data-Intensive Applications', Chapter 7 on transactions

• The PostgreSQL documentation on transaction isolation levels, probably the best free reference on practical ACID

Isolation Levels: The Knob Between Safety and Speed

ACID says transactions must be isolated, but isolation is not binary. SQL defines four levels, each trading safety for concurrency. READ UNCOMMITTED lets transactions read uncommitted changes from other transactions (dirty reads). Almost no application should use this. READ COMMITTED (the PostgreSQL and Oracle default) prevents dirty reads but allows non-repeatable reads: reading the same row twice within a transaction can return different values if another transaction commits in between. REPEATABLE READ prevents that but still allows phantom reads, where a range query returns different rows on two executions because another transaction inserted rows. SERIALIZABLE prevents all anomalies but serializes concurrent transactions, crushing throughput.

• READ UNCOMMITTED: can read uncommitted data. Never use this for financial or inventory data.

• READ COMMITTED: prevents dirty reads. Default in PostgreSQL and Oracle. Most web apps run here.

• REPEATABLE READ: prevents dirty and non-repeatable reads. MySQL InnoDB default. Uses gap locks.

• SERIALIZABLE: prevents all anomalies. Required for correctness in financial systems. Slowest.

• Snapshot Isolation: what most databases actually implement. Gives each transaction a consistent snapshot without fully serializing. Better performance than true SERIALIZABLE.

Real Failures When Distributed Systems Relax ACID

The failures that happen when distributed systems drop ACID guarantees are not theoretical. The double-spend problem in cryptocurrency is a lost update: two concurrent withdrawals each read a balance of 100, each see enough funds, both commit, the account ends up at negative 100. The overselling problem in e-commerce is the same failure: two checkout processes each read inventory as 1, both decrement, the count goes to -1. These are not edge cases. They happen under normal concurrent load when isolation is insufficient.

• Lost update: two transactions read-modify-write the same row. One update is silently overwritten. Fix: SELECT FOR UPDATE or optimistic locking with version columns.

Suppose:
Initial balance = ₹100

Transaction T1
   SELECT balance FROM accounts; -- 100
Transaction T2
   SELECT balance FROM accounts; -- 100

T1 adds ₹50:
   UPDATE accounts SET balance = 150;
T2 adds ₹20:
   UPDATE accounts SET balance = 120;

Final Balance
   120
Expected:
   100 + 50 + 20 = 170

T1's update was lost.

Fix:
1. Pessimistic Locking
      SELECT * FROM accounts
      FOR UPDATE;
      Locks the row so nobody else can modify it.

2. Optimistic Locking
   Table:
      id | balance | version
      1  | 100     | 1
   Update:
      UPDATE accounts
      SET balance = 150,
         version = 2
      WHERE id = 1
         AND version = 1;
   If another transaction already changed the version, the update fails and must be retried.

• Dirty read: a transaction reads data that another transaction later rolls back. You made a decision on data that never existed.

Suppose:
Initial balance = ₹1000

Transaction T1
   UPDATE accounts
   SET balance = 1000
   WHERE id = 1;
Not committed yet.

Transaction T2
   SELECT balance FROM accounts;
   Reads:
      1000
   Now T1 fails:
      ROLLBACK;
   Balance becomes:
      100

Issue:
   T2 made decisions based on data that never officially existed.
   This is called a dirty read.

Prevented By
   Isolation Level:
      READ COMMITTED
   or higher.

• Non-repeatable read: you read a user's account twice in the same request and get different balances. Confusing for users, dangerous for financial logic.

Suppose:
Initial balance = ₹100

Transaction T1
   SELECT balance; -- 100

Transaction T2
   UPDATE accounts
   SET balance = 200;
   COMMIT;

Transaction T1 Again
   SELECT balance; -- 200

Same query. Same transaction.
Different result.

Issue:
   The row changed between reads.

Prevented By:
   Isolation Level:
      REPEATABLE READ
   or
      SERIALIZABLE

• Phantom read: your 'count available seats' query returns 5, you book one, but between your two queries someone else also read 5. You both book the same seat.

Suppose:
Seat Booking System

Available seats:
   Seat 1
   Seat 2
   Seat 3
   Seat 4
   Seat 5

Transaction T1
   SELECT COUNT(*)
   FROM seats
   WHERE status='available';
Result:5

Transaction T2
   Books a seat:
      INSERT INTO bookings ...
      COMMIT;

Transaction T1 Again
   SELECT COUNT(*)
   FROM seats
   WHERE status='available';
Result:4

A new row appeared/disappeared in the result set.
That new row is called a phantom.

Prevented By:
   SERIALIZABLE

• Write skew: two transactions each check a condition, each condition passes individually, but the combined writes violate the constraint. Classic example: two doctors both go off-call believing the other will cover.

Doctor On-Call Example
Rule:
   At least one doctor must always remain on call.
Table:
   Doctor A = ON
   Doctor B = ON

Transaction T1
   Checks:
      SELECT COUNT(*)
      FROM doctors
      WHERE on_call = true;
   Result:2
   T1 thinks:
      Safe to go off-call.

Transaction T2
   Runs at the same time.
   Checks:
      SELECT COUNT(*)
      FROM doctors
      WHERE on_call = true;
   Result:2
   T2 also thinks:
      Safe to go off-call.

   T1 Updates
      UPDATE doctors
      SET on_call = false
      WHERE doctor_id = A;

   T2 Updates
      UPDATE doctors
      SET on_call = false
      WHERE doctor_id = B;
   
Both commit.
Final state:
   Doctor A = OFF
   Doctor B = OFF
Rule violated:
   No doctor is on call.

Prevented By:
   SERIALIZABLE

What Interviewers Actually Test About ACID

ACID questions in interviews are rarely about definitions. The definition is table stakes. What interviewers test is whether you understand the trade-offs when ACID is weakened or absent in distributed systems. Can you identify which ACID property a given scenario violates? Can you propose the minimum isolation level needed for a specific use case? Can you design an application-level compensating transaction for a distributed system that cannot use database ACID across service boundaries?

• Know: what each isolation level prevents and allows, with a concrete example of each anomaly

• Know: why distributed transactions (two-phase commit) are expensive and what SAGA patterns replace them with

• Know: the difference between optimistic locking (version check at commit) and pessimistic locking (SELECT FOR UPDATE)

• Know: why eventual consistency is not the same as no consistency, and how conflict resolution works

• Red flag: saying 'just use a distributed database with ACID' without discussing latency and availability trade-offs

Isolation Levels: The Knob Between Safety and Speed

ACID says transactions must be isolated, but isolation is not binary. SQL defines four levels, each trading safety for concurrency. READ UNCOMMITTED lets transactions read uncommitted changes from other transactions (dirty reads). Almost no application should use this. READ COMMITTED (the PostgreSQL and Oracle default) prevents dirty reads but allows non-repeatable reads: reading the same row twice within a transaction can return different values if another transaction commits in between. REPEATABLE READ prevents that but still allows phantom reads, where a range query returns different rows on two executions because another transaction inserted rows. SERIALIZABLE prevents all anomalies but serializes concurrent transactions, crushing throughput.

• READ UNCOMMITTED: can read uncommitted data. Never use this for financial or inventory data.

• READ COMMITTED: prevents dirty reads. Default in PostgreSQL and Oracle. Most web apps run here.

• REPEATABLE READ: prevents dirty and non-repeatable reads. MySQL InnoDB default. Uses gap locks.

• SERIALIZABLE: prevents all anomalies. Required for correctness in financial systems. Slowest.

• Snapshot Isolation: what most databases actually implement. Gives each transaction a consistent snapshot without fully serializing. Better performance than true SERIALIZABLE.

Real Failures When Distributed Systems Relax ACID

The failures that happen when distributed systems drop ACID guarantees are not theoretical. The double-spend problem in cryptocurrency is a lost update: two concurrent withdrawals each read a balance of 100, each see enough funds, both commit, the account ends up at negative 100. The overselling problem in e-commerce is the same failure: two checkout processes each read inventory as 1, both decrement, the count goes to -1. These are not edge cases. They happen under normal concurrent load when isolation is insufficient.

• Lost update: two transactions read-modify-write the same row. One update is silently overwritten. Fix: SELECT FOR UPDATE or optimistic locking with version columns.

• Dirty read: a transaction reads data that another transaction later rolls back. You made a decision on data that never existed.

• Non-repeatable read: you read a user's account twice in the same request and get different balances. Confusing for users, dangerous for financial logic.

• Phantom read: your 'count available seats' query returns 5, you book one, but between your two queries someone else also read 5. You both book the same seat.

• Write skew: two transactions each check a condition, each condition passes individually, but the combined writes violate the constraint. Classic example: two doctors both go off-call believing the other will cover.

What Interviewers Actually Test About ACID

ACID questions in interviews are rarely about definitions. The definition is table stakes. What interviewers test is whether you understand the trade-offs when ACID is weakened or absent in distributed systems. Can you identify which ACID property a given scenario violates? Can you propose the minimum isolation level needed for a specific use case? Can you design an application-level compensating transaction for a distributed system that cannot use database ACID across service boundaries?

• Know: what each isolation level prevents and allows, with a concrete example of each anomaly

• Know: why distributed transactions (two-phase commit) are expensive and what SAGA patterns replace them with

• Know: the difference between optimistic locking (version check at commit) and pessimistic locking (SELECT FOR UPDATE)

• Know: why eventual consistency is not the same as no consistency, and how conflict resolution works

• Red flag: saying 'just use a distributed database with ACID' without discussing latency and availability trade-offs

Isolation Levels: The Knob Between Safety and Speed

ACID says transactions must be isolated, but isolation is not binary. SQL defines four levels, each trading safety for concurrency. READ UNCOMMITTED lets transactions read uncommitted changes from other transactions (dirty reads). Almost no application should use this. READ COMMITTED (the PostgreSQL and Oracle default) prevents dirty reads but allows non-repeatable reads: reading the same row twice within a transaction can return different values if another transaction commits in between. REPEATABLE READ prevents that but still allows phantom reads, where a range query returns different rows on two executions because another transaction inserted rows. SERIALIZABLE prevents all anomalies but serializes concurrent transactions, crushing throughput.

• READ UNCOMMITTED: can read uncommitted data. Never use this for financial or inventory data.
• READ COMMITTED: prevents dirty reads. Default in PostgreSQL and Oracle. Most web apps run here.
• REPEATABLE READ: prevents dirty and non-repeatable reads. MySQL InnoDB default. Uses gap locks.
• SERIALIZABLE: prevents all anomalies. Required for correctness in financial systems. Slowest.
• Snapshot Isolation: what most databases actually implement. Gives each transaction a consistent snapshot without fully serializing. Better performance than true SERIALIZABLE.

Real Failures When Distributed Systems Relax ACID

The failures that happen when distributed systems drop ACID guarantees are not theoretical. The double-spend problem in cryptocurrency is a lost update: two concurrent withdrawals each read a balance of 100, each see enough funds, both commit, the account ends up at negative 100. The overselling problem in e-commerce is the same failure: two checkout processes each read inventory as 1, both decrement, the count goes to -1. These are not edge cases. They happen under normal concurrent load when isolation is insufficient.

• Lost update: two transactions read-modify-write the same row. One update is silently overwritten. Fix: SELECT FOR UPDATE or optimistic locking with version columns.
• Dirty read: a transaction reads data that another transaction later rolls back. You made a decision on data that never existed.
• Non-repeatable read: you read a user's account twice in the same request and get different balances. Confusing for users, dangerous for financial logic.
• Phantom read: your 'count available seats' query returns 5, you book one, but between your two queries someone else also read 5. You both book the same seat.
• Write skew: two transactions each check a condition, each condition passes individually, but the combined writes violate the constraint. Classic example: two doctors both go off-call believing the other will cover.

What Interviewers Actually Test About ACID

ACID questions in interviews are rarely about definitions. The definition is table stakes. What interviewers test is whether you understand the trade-offs when ACID is weakened or absent in distributed systems. Can you identify which ACID property a given scenario violates? Can you propose the minimum isolation level needed for a specific use case? Can you design an application-level compensating transaction for a distributed system that cannot use database ACID across service boundaries?

• Know: what each isolation level prevents and allows, with a concrete example of each anomaly
• Know: why distributed transactions (two-phase commit) are expensive and what SAGA patterns replace them with
• Know: the difference between optimistic locking (version check at commit) and pessimistic locking (SELECT FOR UPDATE)
• Know: why eventual consistency is not the same as no consistency, and how conflict resolution works
• Red flag: saying 'just use a distributed database with ACID' without discussing latency and availability trade-offs

Isolation Levels: The Knob Between Safety and Speed

ACID says transactions must be isolated, but isolation is not binary. SQL defines four levels, each trading safety for concurrency. READ UNCOMMITTED lets transactions read uncommitted changes from other transactions (dirty reads). Almost no application should use this. READ COMMITTED (the PostgreSQL and Oracle default) prevents dirty reads but allows non-repeatable reads: reading the same row twice within a transaction can return different values if another transaction commits in between. REPEATABLE READ prevents that but still allows phantom reads, where a range query returns different rows on two executions because another transaction inserted rows. SERIALIZABLE prevents all anomalies but serializes concurrent transactions, crushing throughput.

• READ UNCOMMITTED: can read uncommitted data. Never use this for financial or inventory data.
• READ COMMITTED: prevents dirty reads. Default in PostgreSQL and Oracle. Most web apps run here.
• REPEATABLE READ: prevents dirty and non-repeatable reads. MySQL InnoDB default. Uses gap locks.
• SERIALIZABLE: prevents all anomalies. Required for correctness in financial systems. Slowest.
• Snapshot Isolation: what most databases actually implement. Gives each transaction a consistent snapshot without fully serializing. Better performance than true SERIALIZABLE.

Real Failures When Distributed Systems Relax ACID

The failures that happen when distributed systems drop ACID guarantees are not theoretical. The double-spend problem in cryptocurrency is a lost update: two concurrent withdrawals each read a balance of 100, each see enough funds, both commit, the account ends up at negative 100. The overselling problem in e-commerce is the same failure: two checkout processes each read inventory as 1, both decrement, the count goes to -1. These are not edge cases. They happen under normal concurrent load when isolation is insufficient.

• Lost update: two transactions read-modify-write the same row. One update is silently overwritten. Fix: SELECT FOR UPDATE or optimistic locking with version columns.
• Dirty read: a transaction reads data that another transaction later rolls back. You made a decision on data that never existed.
• Non-repeatable read: you read a user's account twice in the same request and get different balances. Confusing for users, dangerous for financial logic.
• Phantom read: your 'count available seats' query returns 5, you book one, but between your two queries someone else also read 5. You both book the same seat.
• Write skew: two transactions each check a condition, each condition passes individually, but the combined writes violate the constraint. Classic example: two doctors both go off-call believing the other will cover.

What Interviewers Actually Test About ACID

ACID questions in interviews are rarely about definitions. The definition is table stakes. What interviewers test is whether you understand the trade-offs when ACID is weakened or absent in distributed systems. Can you identify which ACID property a given scenario violates? Can you propose the minimum isolation level needed for a specific use case? Can you design an application-level compensating transaction for a distributed system that cannot use database ACID across service boundaries?

• Know: what each isolation level prevents and allows, with a concrete example of each anomaly
• Know: why distributed transactions (two-phase commit) are expensive and what SAGA patterns replace them with
• Know: the difference between optimistic locking (version check at commit) and pessimistic locking (SELECT FOR UPDATE)
• Know: why eventual consistency is not the same as no consistency, and how conflict resolution works
• Red flag: saying 'just use a distributed database with ACID' without discussing latency and availability trade-offs