Understanding Database ACID Properties: A Tale of Tate and Zuck

INTRODUCTION

Have you ever wondered what ACID properties are? Why do we need them? is this person talking about chemical acid? What does it have to do with databases? what is a transaction?

Transaction is: a sequence of database operations that satisfies ACID properties.

ACID is:
Atomicity: the transaction is a single unit, either completely successful or completely failed.
Consistency: ensure transaction must bring the database from one consistent state to another. data should always follow all the database-defined rules (constraints, cascades .. etc).
Isolation: concurrent transactions should have the same effect as if they were executed sequentially. Failed transactions should not affect others, as they are running in isolation.
Durability: once a transaction is committed, the database must preserve its data state even in cases of failure (server crashes, power outages, etc.) by recording the commit in non-volatile memory.

Let’s dig deeper into the details so you won’t say I’m just scratching the surface, and I don’t want to look dumb in this smart Zuckerberg world.

ATOMICITY

Ensured a series of DB operations under transaction should either be completely successful or completely failed. there should not be any partially successful operation.

Let’s understand this through an example involving two people: Zuck’s and Tate’s.
Zuck’s and Tate’s are good friends, and Tate’s decides to send money to Zuck’s to help him escape the matrix.

Case 1: Atomic Success

Before Transaction:
Tate’s balance: $1000
Zuck’s balance: $500

Tate’s send $100 to Zuck’s

After Transaction:
Tate’s balance: $900
Zuck’s balance: $600

CASE 1: ATOMIC SUCCESS 

START TRANSACTION
   // Initial State Check
   Tate's Balance = $1000
   Zuck's Balance = $500

   // Transaction Steps
   Step 1: Check if Tate's has enough money
          IF Tate's Balance >= $100 THEN continue
          ELSE rollback

   Step 2: Deduct from Tate's account
          Tate's Balance = Tate's Balance - $100    // Now $900

   Step 3: Add to Zuck's account  
          Zuck's Balance = Zuck's Balance + $100    // Now $600

   // Everything worked!  
   COMMIT TRANSACTION

// Final State
Tate's Balance = $900     ✓ 
Zuck's Balance = $600     ✓
Transaction Complete       ✓

This is an atomic success case where the first debit occurs from Tate’s account and then credit occurs in Zuck’s account. In the above code, all the DB operations are executed inside the transaction block, so if any operation fails, we can roll back the entire series of operations. We only commit the transaction after all the DB operations are successful.

Case 2: Atomic Failure

Before Transaction:
Tate’s balance: $1000
Zuck’s balance: $500

Tate’s failed to send $100 to Zuck’s

After Transaction:
Tate’s balance: $1000
Zuck’s balance: $500

CASE 2: ATOMIC FAILURE

START TRANSACTION
   // Initial State Check  
   Tate's Balance = $1000
   Zuck's Balance = $500

   // Transaction Steps
   Step 1: Check if Tate's has enough money
          IF Tate's Balance >= $100 THEN continue
          ELSE rollback

   Step 2: Deduct from Tate's account
          Tate's Balance = Tate's Balance - $100    // Now $900

   Step 3: Add to Zuck's account  
          ERROR: Network Connection Failed! ❌
          
   // Since Step 3 failed, everything rolls back!
   ROLLBACK TRANSACTION 

// Final State (Same as Initial)
Tate's Balance = $1000    ✓ (Rolled back to original)
Zuck's Balance = $500     ✓ (Never changed)
Transaction Failed        ❌

This is an atomic failure case where we didn’t commit the transaction because one of the DB operations under the transaction block failed.
We successfully verified Tate’s account balance and deducted the amount. However, during the crediting to Zuck’s account, there was a network failure, which caused us to roll back the whole transaction. Now the state of the database remains unchanged despite the failure, and we can say it has passed the atomicity test.

Case 3: Non-Atomic Failure

Before Transaction:
Tate’s balance: $1000
Zuck’s balance: $500

Tate’s failed to send $100 to Zuck’s

After Transaction:
Tate’s balance: $900
Zuck’s balance: $500

CASE 3: NON-ATOMIC FAILURE (Dangerous!)

NO TRANSACTION    // No transaction block - each operation runs independently
   // Initial State
   Tate's Balance = $1000
   Zuck's Balance = $500

   // Operations run separately
   Step 1: Check if Tate's has enough money
          IF Tate's Balance >= $100 THEN continue    ✓

   Step 2: Deduct from Tate's account
          Tate's Balance = Tate's Balance - $100    // Now $900  ✓
          
   Step 3: Add to Zuck's account
          ERROR: Network Connection Failed! ❌
          
   // Cannot rollback because no transaction block!
   // Money is lost in the system! 💸

// Final State (Inconsistent!)
Tate's Balance = $900     ✓ (Money gone!)
Zuck's Balance = $500     ✓ (Never received)
Money Lost: $100         ❌

In a non-atomic operation, we first validate Tate’s account balance and deduct the amount. During the attempt to credit Zuck’s account, a network failure causes the DB operation to fail. Since we’re not using a transaction, we can’t roll back the previous operation. This leads to an inconsistent state where the amount is debited from the source account but not credited to the target account.

CONSISTENCY

Ensured data always moved from one valid state to another following all DB defined rules. rules can be constraints, cascades, triggers, or other defined rules.
Let’s understand it by example suppose we have a DB table account_balances.

CREATE TABLE account_balances (
    tate_balance INTEGER,
    zuck_balance INTEGER,
    -- Total money in system must always be $1000
    CHECK (tate_balance + zuck_balance = 1000),
    -- No negative balance
    CHECK (tate_balance >= 0),
    CHECK (zuck_balance >= 0)
);

We have defined two columns tate_balance & zuck_balance. additionally, we have defined a set of DB rules
1. tate_balance + zuck_balance should always be $1000
2. tate_balance should be greater or equal to 0
3. zuck_balance should be greater or equal to 0

Case 1: Consistency Success

Before Transaction:
Tate’s balance: $600
Zuck’s balance: $400

Tate’s send $200 to Zuck’s

After Transaction:
Tate’s balance: $400
Zuck’s balance: $600

START TRANSACTION
    Initial State:
    Tate's Balance = $600
    Zuck's Balance = $400
    System Total = $1000 ✓

    // Tate's sends $200 to Zuck's
    Step 1: Deduct from Tate's
           $600 - $200 = $400 ✓
    
    Step 2: Add to Zuck's
           $400 + $200 = $600 ✓
    
    Final Check: $400 + $600 = $1000 ✓
    COMMIT

Final State:
    Tate's: $400
    Zuck's: $600
    System Total = $1000 ✓

Starting Tate’s balance is $600 & Zuck’s balance is $400, as the sum of both balances is $1000 which follows the DB defined rule we can conclude our DB is in a consistent state.
When Tate’s sends $200 to Zuck’s to escape the matrix, the balance updates show Tate’s at $400 and Zuck’s at $600. The sum remains $1000, maintaining the database rules. This transaction is consistent because it moves from one valid state to another.

Case 2: Consistency Violation

Before Transaction:
Tate’s balance: $600
Zuck’s balance: $400

Tate’s failed to send $700 to Zuck’s

After Transaction:
Tate’s balance: $600
Zuck’s balance: $400

START TRANSACTION
    Initial State:
    Tate's Balance = $600
    Zuck's Balance = $400
    System Total = $1000 ✓

    // Tate's tries to send $700 to Zuck's
    Step 1: Deduct from Tate's
           $600 - $700 = -$100 ❌
           (Would violate no negative balance rule!)
    
    ROLLBACK

Final State:
    Tate's: $600 (Unchanged)
    Zuck's: $400 (Unchanged)
    System Total = $1000 ✓

When Tate’s became desperate enough to break free from the matrix, he tried to send $700, thinking he could bypass database rules just like he breaks real-world laws.
After the transaction, Tate’s balance should be (-ive)$100 & Zuck’s balance should be $1100 which still follows the $1000 sum rule. However, we have a greater than zero balance validation rule that doesn’t allow the transaction to go through.
So DB declined the transaction as it was violating the consistent state.

ISOLATION

Ensured concurrent transactions were always executed in a consistent manner. it should have the same effect as it’s executing sequentially.
Failure of any concurrent transaction should not move the DB to an inconsistent or incorrect state.
During concurrent transactions on common rows, only one transaction should be able to execute & others should wait for it to complete. if the failure happens during execution it should roll back to the previous consistent state.

Case 1: Concurrent Transaction Success

Before Transaction:
Tate’s balance: $1000
Zuck’s balance: $500

Tate’s send $100 & Zuck’s send $200 concurrently

After Transaction:
Tate’s balance: $1100
Zuck’s balance: $400

// Transaction 1 Starts and Completes First
START TRANSACTION T1
    Initial State:
    Tate's Balance = $1000
    Zuck's Balance = $500
    System Total = $1500 ✓

    // Tate's sends $100 to Zuck's
    Step 1: Deduct from Tate's
           $1000 - $100 = $900 ✓
    
    Step 2: Add to Zuck's
           $500 + $100 = $600 ✓
    
    Final Check: $900 + $600 = $1500 ✓
    COMMIT T1

// Transaction 2 Starts After T1 Completes
START TRANSACTION T2
    Initial State:
    Tate's Balance = $900
    Zuck's Balance = $600
    System Total = $1500 ✓

    // Zuck's sends $200 back to Tate's
    Step 1: Deduct from Zuck's
           $600 - $200 = $400 ✓
    
    Step 2: Add to Tate's
           $900 + $200 = $1100 ✓
    
    Final Check: $1100 + $400 = $1500 ✓
    COMMIT T2

Final State:
    Tate's: $1100
    Zuck's: $400
    System Total = $1500 ✓

During the concurrent transactions, isolation ensures only one transaction gets executed & others wait for its completion.
There are two transactions T1 & T2 to be processed concurrently to maintain isolation one of the transactions T2 waited for the T1 transaction to commit. after completion of T1, T2 executed successfully. the final state of DB looks consistent.

// Transaction 1 (Completes First)
Initial State:
   Tate's: $1000
   Zuck's: $500
   Total: $1500

T1: Tate's -> Zuck's $100
   1. Tate's: $1000 - $100 = $900
   2. Zuck's: $500 + $100 = $600
   3. Total Check: $900 + $600 = $1500 ✓
   4. COMMIT ✓

// Transaction 2 (Starts After T1)
T2: Zuck's -> Tate's $200
   1. Zuck's: $600 - $200 = $400
   2. Tate's: $900 + $200 = $1100
   3. Total Check: $1100 + $400 = $1500 ✓
   4. COMMIT ✓

Final State:
   Tate's: $1100
   Zuck's: $400
   Total: $1500 ✓

Case 2: Concurrent Transaction Invalid

Before Transaction:
Tate’s balance: $1000
Zuck’s balance: $500
Total balance: $1500

Tate’s send $100 & Zuck’s send $200 concurrently

After Transaction:
Tate’s balance: $1100
Zuck’s balance: $300
Total balance: $1400 ($100 lost 🤡)

// Both Transactions Start Together
START TRANSACTION T1
    Initial State:
    Tate's Balance = $1000
    Zuck's Balance = $500
    System Total = $1500 ✓

    Step 1: T1 - Deduct from Tate's
           $1000 - $100 = $900

    // T2 Interrupts!
    START TRANSACTION T2
        Step 2: T2 - Deduct from Zuck's
               $500 - $200 = $300
        
        Step 3: T2 - Add to Tate's
               $900 + $200 = $1100
        COMMIT T2

    Step 4: T1 - Try to Add to Zuck's
           $300 + $100 = $400
           ERROR! T1 FAILS! ❌

    // Problem: Can't rollback T1 because T2 already modified data!
    // T2 saw intermediate state from uncommitted T1
    // Data integrity compromised!

Corrupted State:
    Tate's: $1100
    Zuck's: $300    // Lost $100 in the system!
    System Total = $1400 ❌

If there is no isolation in concurrent transactions let’s check what happens in case of failure.
T1 sends $100 to T2.
T2 sends $200 to T1.
T1 $100 is deducted new balance is $900
T2 $200 is deducted new balance is $300
T1 $200 is credited new balance is $1100
T2 while crediting $100 network failure happened
Unable to rollback as T1 balance is updated by another transaction. now DB data is incorrect & $100 is lost in the system.

// Dangerous Interleaving (Both Start Together)
Initial State:
   Tate's: $1000
   Zuck's: $500
   Total: $1500

T1: Tate's -> Zuck's $100
   1. Tate's: $1000 - $100 = $900

T2: Zuck's -> Tate's $200 (Interrupts!)
   2. Zuck's: $500 - $200 = $300
   3. Tate's: $900 + $200 = $1100
   4. COMMIT T2 ✓

T1: Continues...
   5. Zuck's: $300 + $100 = $400
   6. FAILS! Can't safely complete! ❌

Corrupted State:
   Tate's: $1100
   Zuck's: $300
   Total: $1400 ❌ (Lost $100!)

Problems:
   • T2 saw uncommitted data from T1
   • Can't rollback T1 (T2 already committed)
   • System total no longer maintains $1500

DURABILITY

Ensured once the transaction is committed there should be no data loss in any kind of failure e.g. database crash, outage, .. etc.
It’s important as once the client gets a successful commit from DB the assumption is data is loaded in non-volatile memory. if we are loading data in volatile memory there can be a scenario where during power failure we can lose the updated data.

Transaction: Tate's sends $100 to Zuck's for matrix escape

1. Initial Database State:
   Tate's: $1000 
   Zuck's: $500
   
2. Transaction Committed Successfully:
   • Deduct from Tate's: $1000 -> $900
   • Add to Zuck's: $500 -> $600
   • System confirms: "Transaction Complete ✓"
   • Tate's: "Perfect! Zuck can escape matrix now!"

3. Suddenly... 💥 System Crashes! 

4. After System Recovery:
   WITH Durability ✓
   • Database recovers to committed state
   • Tate's: $900
   • Zuck's: $600
   • Matrix escape funds safe! ✓

   WITHOUT Durability ❌
   • Transaction lost
   • Tate's: Back to $1000
   • Zuck's: Still at $500
   • Matrix escape plan failed! 😱

Key Points:
- Committed transactions must survive crashes
- Changes should be permanent
- System recovery must maintain committed state
- Users must be able to trust transaction completions

END

Time to end this blog — I hope you enjoyed it! I’m planning to write more blogs in the future so I can get rich and buy a Bugatti. Feel free to follow me and leave some likes to keep this platform engaging.
And finally, I’ll leave you with the question:
What color is your Database?