ACID properties in MySQL - Deep Dive

Overview - ACID properties

What is it?

ACID properties are a set of rules that guarantee reliable processing of database transactions. They ensure that each transaction is handled completely and correctly, even if errors or failures happen. The four properties are Atomicity, Consistency, Isolation, and Durability. Together, they keep data accurate and safe.

Why it matters

Without ACID properties, databases could lose or corrupt data during updates, especially when multiple users work at the same time or if the system crashes. This could cause wrong information, lost money, or broken applications. ACID makes sure that every change is done fully or not at all, keeping trust in the data.

Where it fits

Before learning ACID, you should understand what a database and a transaction are. After ACID, you can learn about transaction isolation levels, locking, and how databases handle concurrency and recovery.

Mental Model

Core Idea

ACID properties ensure that database transactions are processed reliably, completely, and safely, no matter what happens.

Think of it like...

Imagine sending a package through a delivery service that promises: either the whole package arrives intact, or you get your money back; the package contents are exactly what you sent; no one else can tamper with it while it's being delivered; and once delivered, it stays safe and won’t disappear.

┌─────────────┐
│ Transaction │
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  Atomicity  │
│ (All or None)│
└──────┬──────┘
       │
       ▼
┌─────────────┐
│ Consistency │
│ (Valid Data)│
└──────┬──────┘
       │
       ▼
┌─────────────┐
│ Isolation   │
│ (No Interference)│
└──────┬──────┘
       │
       ▼
┌─────────────┐
│ Durability  │
│ (Permanent) │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

What is a Database Transaction

Concept: Introduce the idea of a transaction as a group of database actions treated as one unit.

A transaction is like a single task that involves multiple steps in a database. For example, transferring money from one bank account to another involves subtracting from one account and adding to another. Both steps must happen together or not at all to avoid mistakes.

Result

You understand that a transaction groups multiple database operations into one logical unit.

Knowing what a transaction is helps you see why we need rules like ACID to keep data safe during these grouped actions.

2

Foundation

Introducing Atomicity

Concept: Explain Atomicity as the 'all or nothing' rule for transactions.

Atomicity means that a transaction must complete fully or not happen at all. If any step fails, the whole transaction is undone. For example, if money is taken from one account but not added to another, Atomicity ensures the first step is reversed.

Result

You learn that Atomicity prevents partial changes that could cause errors.

Understanding Atomicity prevents data corruption from incomplete transactions.

3

Intermediate

Understanding Consistency

🤔Before reading on: do you think Consistency means data never changes, or that data always follows rules after a transaction? Commit to your answer.

Concept: Consistency means the database must follow all rules before and after a transaction.

Consistency ensures that data stays valid according to all rules, like constraints and triggers. For example, if an account balance cannot be negative, Consistency makes sure no transaction breaks this rule.

Result

You see that Consistency keeps data accurate and trustworthy after every transaction.

Knowing Consistency helps you trust that transactions won't break important data rules.

4

Intermediate

Exploring Isolation

🤔Before reading on: do you think Isolation means transactions run one after another, or that they can run at the same time without problems? Commit to your answer.

Concept: Isolation means transactions do not interfere with each other, even if they run at the same time.

When many users work on the database simultaneously, Isolation keeps their transactions separate. This prevents one transaction from seeing incomplete changes from another. For example, two people updating the same data won’t cause confusion or errors.

Result

You understand that Isolation protects transactions from each other's partial changes.

Understanding Isolation is key to managing multiple users safely in a database.

5

Intermediate

Durability Guarantees Data Safety

Concept: Durability means once a transaction is done, its changes are saved permanently, even if the system crashes.

After a transaction commits, the database saves the changes to stable storage like a hard drive. This way, if the power goes out or the system fails, the data won't be lost. Durability ensures your work is safe and permanent.

Result

You learn that Durability protects data from loss after successful transactions.

Knowing Durability builds confidence that committed data won't disappear unexpectedly.

6

Advanced

How ACID Works Together in Practice

🤔Before reading on: do you think ACID properties can conflict with each other, or do they always work perfectly together? Commit to your answer.

Concept: ACID properties work as a team to keep transactions safe, but balancing them can be complex.

In real databases, Atomicity, Consistency, Isolation, and Durability must all be enforced. Sometimes, improving one property can affect another, like making Isolation stronger might slow down performance. Database systems use techniques like locking and logging to balance these needs.

Result

You see that ACID is a careful balance that databases manage to keep data safe and efficient.

Understanding the trade-offs in ACID helps you appreciate database design and performance tuning.

7

Expert

Surprising Limits of ACID in Distributed Systems

🤔Before reading on: do you think ACID properties apply the same way in databases spread across many servers? Commit to your answer.

Concept: In distributed databases, enforcing ACID is harder and sometimes relaxed for speed or availability.

When data is spread over multiple servers, keeping all ACID properties perfectly is challenging. Systems may choose to relax Isolation or Consistency temporarily to stay fast and available. This is known as the CAP theorem trade-off. Experts design systems carefully to decide which ACID properties to prioritize.

Result

You learn that ACID is not absolute in all systems and must be adapted for distributed environments.

Knowing ACID’s limits in distributed systems prepares you for real-world database challenges and design decisions.

Under the Hood

Databases implement ACID using transaction logs, locks, and checkpoints. Atomicity uses rollback logs to undo partial changes. Consistency relies on constraints and triggers checked during transactions. Isolation is enforced by locking data or using multi-version concurrency control to prevent conflicts. Durability is ensured by writing changes to stable storage before confirming success.

Why designed this way?

ACID was designed to solve problems from early database systems where crashes or concurrent users caused data loss or corruption. The rules balance safety and performance. Alternatives like eventual consistency were rejected for systems needing strict correctness, such as banking.

┌───────────────┐
│ Client sends  │
│ transaction   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Transaction   │
│ Manager       │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Lock Manager  │
│ (Isolation)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Log Manager   │
│ (Atomicity &  │
│ Durability)   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Storage       │
│ (Data &       │
│ Constraints)  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does Atomicity mean each step in a transaction is saved separately? Commit yes or no.

Common Belief: Atomicity means each individual step in a transaction is saved immediately and separately.

Reality: Atomicity means the entire transaction is saved as one unit; partial steps are not saved if the transaction fails.

Why it matters: Believing this can cause developers to trust partial data changes, leading to inconsistent or corrupted data.

Quick: Does Isolation mean transactions always run one after another? Commit yes or no.

Common Belief: Isolation means transactions never run at the same time; they are always sequential.

Reality: Isolation means transactions can run concurrently but appear as if they run one after another, hiding partial changes.

Why it matters: Misunderstanding this can lead to inefficient database use or incorrect assumptions about performance.

Quick: Is Durability guaranteed immediately after a transaction commits? Commit yes or no.

Common Belief: Durability means data is instantly saved to permanent storage the moment a transaction commits.

Reality: Durability is guaranteed by writing to logs and stable storage, but some systems may delay actual disk writes briefly for performance.

Why it matters: Assuming instant durability can cause data loss in rare crash scenarios if the system uses delayed writes.

Quick: Do ACID properties always apply fully in distributed databases? Commit yes or no.

Common Belief: ACID properties are always fully enforced, no matter how many servers the database uses.

Reality: In distributed systems, some ACID properties may be relaxed to improve speed or availability.

Why it matters: Ignoring this can cause wrong expectations and design mistakes in large-scale systems.

Expert Zone

1

Isolation levels vary and can trade strictness for performance; understanding these subtleties is key for tuning.

2

Durability depends on hardware and configuration; for example, using SSDs or RAID affects how safe data really is.

3

Atomicity rollback can be complex with nested transactions or savepoints, which many overlook.

When NOT to use

ACID is not ideal for highly distributed or real-time systems where speed and availability matter more than strict consistency. Alternatives like BASE (Basically Available, Soft state, Eventual consistency) or NoSQL databases are better suited there.

Production Patterns

In production, ACID is used in banking, e-commerce, and inventory systems where data correctness is critical. Techniques like two-phase commit coordinate transactions across multiple databases. Developers also use transaction isolation levels to balance concurrency and consistency.

Connections

Distributed Systems

ACID properties are challenged and adapted in distributed systems due to network delays and failures.

Understanding ACID helps grasp why distributed databases often relax some guarantees to remain available and scalable.

Software Transactions in Programming

ACID principles inspired software transaction models that manage changes in memory safely.

Knowing ACID clarifies how software transactions ensure consistency and rollback in complex applications.

Legal Contracts

Like ACID transactions, legal contracts require all parties to fulfill terms completely or not at all.

Seeing this connection helps understand the importance of atomicity and consistency in agreements and data.

Common Pitfalls

#1 Trying to manually handle partial failures without using transactions.

Wrong approach:

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Then some error occurs
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Correct approach:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Root cause:

Not using transactions leads to partial updates and inconsistent data if errors happen.

#2 Assuming all isolation levels prevent all concurrency issues.

Wrong approach:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Then reading data that another transaction is still changing

Correct approach:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Prevents reading uncommitted changes

Root cause:

Misunderstanding isolation levels causes unexpected dirty reads or phantom reads.

#3 Ignoring durability by disabling transaction logs for performance.

Wrong approach:

SET GLOBAL innodb_flush_log_at_trx_commit = 0;
-- Improves speed but risks data loss on crash

Correct approach:

SET GLOBAL innodb_flush_log_at_trx_commit = 1;
-- Ensures durability by flushing logs on commit

Root cause:

Trading durability for speed without understanding risks leads to data loss.

Key Takeaways

ACID properties guarantee that database transactions are processed reliably and safely.

Atomicity ensures transactions happen fully or not at all, preventing partial updates.

Consistency keeps data valid according to rules before and after transactions.

Isolation protects transactions from interfering with each other during concurrent access.

Durability makes sure committed changes are saved permanently, even after crashes.