🔧 ACID or BASE

When dealing with databases, maintaining the integrity and reliability of any transactions is crucial.

This is where ACID properties come into play.

ACID stands for Atomicity, Consistency, Isolation, and Durability, which collectively ensure that database transactions are executed safely and reliably.

Let’s dive deep into each property and see how they work with practical SQL examples, including a classic money transfer scenario.

Understanding ACID Transactions

A transaction in a database refers to a sequence of operations performed as a single unit of work.

To ensure the correctness of data, databases enforce ACID properties:

1. Atomicity (All or Nothing)

Atomicity ensures that a transaction is either fully completed or entirely rolled back if any part of it fails.

This prevents partial updates that could leave the database in an inconsistent state.

👉 Example: Suppose we are transferring $100 from Account A to Account B. If the deduction from Account A succeeds but the addition to Account B fails, the transaction should be rolled back completely.

BEGIN TRANSACTION;

-- Deduct $100 from Account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Add $100 to Account B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;

If any of these operations fail (e.g., due to a network issue or insufficient funds), the database rolls back the transaction, ensuring that no partial changes occur.

2. Consistency (Valid State Transition)

Consistency ensures that a transaction brings the database from one valid state to another, adhering to all defined rules, constraints, and relationships.

If a transaction violates any integrity rules, it is rolled back.

👉 Example: If an account cannot have a negative balance, the transaction must enforce this rule.

BEGIN TRANSACTION;

-- Check balance before deducting
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1 AND balance >= 100;

-- Verify that the row was updated
IF @@ROWCOUNT = 0
  ROLLBACK;
ELSE
  COMMIT;

If the account has less than $100, the transaction is rolled back instead of allowing an invalid negative balance.

3. Isolation (Transactions Should Not Interfere)

Isolation ensures that concurrent transactions do not affect each other in a way that leads to inconsistencies.

Depending on the database system, different isolation levels (e.g., Read Uncommitted, Read Committed, Repeatable Read, Serializable) control how transactions interact.

👉 Example: Consider two transactions running simultaneously, both trying to withdraw money from the same account.

Without proper isolation, both might read the same balance and withdraw funds, leading to an overdraft.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

-- Read balance
SELECT balance FROM accounts WHERE account_id = 1;

-- Deduct amount
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

COMMIT;

By setting Serializable isolation, the database ensures that no two transactions interfere and execute sequentially.

4. Durability (Changes Persist After Commit)

Durability ensures that once a transaction is committed, the changes are permanently stored, even in case of a system crash.

👉 Example: After transferring money between accounts, the changes must be stored in a way that they survive power failures.

Modern databases achieve durability through Write-Ahead Logging (WAL), ensuring that changes are first written to disk before being committed.

COMMIT;

Once committed, the transaction is guaranteed to persist, even if the system crashes.

Money Transfer Example

To bring all ACID properties together, here’s a complete SQL transaction for transferring $100 from Account A to Account B safely:

BEGIN TRANSACTION;

-- Deduct from Account A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- Check if deduction was successful
IF @@ROWCOUNT = 0
BEGIN
  ROLLBACK;
  PRINT 'Transaction Failed: Insufficient Funds';
  RETURN;
END

-- Add to Account B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- If all good, commit changes
COMMIT;

This ensures that:

• If any step fails, the entire transaction is rolled back (Atomicity).
• The database remains valid before and after the transaction (Consistency).
• Other transactions do not interfere while this runs (Isolation).
• Once committed, changes persist permanently (Durability).

When to Use ACID Transactions?

ACID transactions are critical in applications where data integrity is paramount:

✅ Banking Systems: Ensuring fund transfers are accurate.
✅ E-commerce Platforms: Preventing issues like double-ordering.
✅ Healthcare Systems: Maintaining accurate patient records.
✅ Stock Trading Platforms: Avoiding race conditions when buying/selling shares.

However, in high-scale distributed systems, ACID transactions can impact performance and scalability.

Some systems use alternatives like BASE (Basically Available, Soft state, Eventually consistent) to achieve higher availability with relaxed consistency.

Additionally, other alternatives such as the CAP theorem (Consistency, Availability, Partition tolerance) help developers understand trade-offs in distributed systems.

NoSQL databases, which often follow BASE principles, prioritize scalability and availability over strict consistency, making them suitable for large-scale, high-performance applications where data consistency can be relaxed.

Conclusion

ACID transactions play a vital role in ensuring consistent database operations.

Whether handling financial transactions or critical data updates, adhering to ACID principles guarantees the integrity of your system.

By understanding ACID and implementing transactions correctly, you can build accurate, fault-tolerant database applications that stand the test of time.

I’ve been working on a super-convenient tool called LiveAPI.

LiveAPI helps you get all your backend APIs documented in a few minutes

With LiveAPI, you can quickly generate interactive API documentation that allows users to execute APIs directly from the browser.

If you’re tired of manually creating docs for your APIs, this tool might just make your life easier.