ACID Properties of Database Transactions

What is a Database Transaction?

Before we can understand ACID, we must first be crystal clear on what a transaction is. In the context of a database, a transaction is a sequence of one or more operations (like SELECT, UPDATE, INSERT) that are executed as a single, logical unit. The classic example is a bank transfer:

1. Check if Account A has sufficient funds.
2. Deduct $100 from Account A.
3. Add $100 to Account B.

All three of these steps must happen together. It would be catastrophic if the system debited Account A and then crashed before crediting Account B. A transaction bundles these steps, ensuring the database is never left in a nonsensical, partially-completed state.

Let's start with a video that introduces the concept of a transaction and the first ACID property.

Now that we have a shared understanding of transactions, let's break down each of the ACID properties.

The Four ACID Properties

ACID is an acronym for the four guarantees that transactions in a reliable database system provide.

1. Atomicity (The 'A')

As you saw in the video, atomicity guarantees that a transaction is indivisible and all-or-nothing. It's treated as a single "atom" of work.

• The Guarantee: All of the operations within a transaction will complete successfully, or none of them will. If any single operation fails, the entire transaction is rolled back, and the database is returned to the state it was in before the transaction began.
• Why it Matters: It prevents partial updates. Money can't simply vanish from one account without appearing in another. An order can't reduce inventory if the payment fails.
• How it's Implemented: Databases typically use a transaction log (often called an undo log or rollback segment). Before making a change, the database records the old value. If the transaction fails, it uses this log to reverse all the changes made so far.

2. Consistency (The 'C')

Consistency ensures that a transaction will bring the database from one valid state to another. The data must always adhere to all defined rules and constraints.

• The Guarantee: Any data written to the database must be valid according to all defined rules, including constraints, cascades, and triggers. If a transaction would violate these rules, it will be rolled back.
• Why it Matters: This property protects data integrity. It prevents you from creating an order for a product that doesn't exist, or assigning a negative stock quantity. It ensures the database remains structurally sound.
• How it's Implemented: Consistency is a shared responsibility between the database and the application.
  • Database-level: Enforcing constraints like NOT NULL, UNIQUE, FOREIGN KEY, and CHECK.
  • Application-level: Business logic that checks conditions before attempting a write (e.g., "is the user's shopping cart total greater than the minimum order value?").

The following video gives a clear explanation of consistency using an e-commerce inventory example.

3. Isolation (The 'I')

Isolation is arguably the most complex of the ACID properties. It addresses the challenges of running multiple transactions at the same time (concurrency).

• The Guarantee: Concurrent transactions will not interfere with each other's intermediate states. From the perspective of any single transaction, it appears as though it is the only transaction running on the system.
• Why it Matters: Without isolation, one transaction might read the partial, uncommitted work of another, leading to incorrect calculations or decisions. For example, a reporting query might sum up sales figures while another transaction is in the middle of processing a return, leading to an inaccurate total.
• The Problems Isolation Solves: When isolation is weak, several "read phenomena" can occur:
  • Dirty Read: A transaction reads data that has been modified by another transaction but has not yet been committed. If the other transaction rolls back, the first transaction is left with invalid, "dirty" data.
  • Non-Repeatable Read: A transaction reads the same row twice but gets different values because another transaction modified and committed the data in between the reads.
  • Phantom Read: A transaction runs the same query twice but gets a different set of rows back because another transaction inserted or deleted rows that match the query's criteria.

To truly appreciate isolation, it's essential to understand these potential issues. The next video provides detailed, step-by-step walkthroughs of each.

We'll dive much deeper into the specific isolation levels in the next lesson, but it's important to know now that they exist as the mechanism for enforcing the Isolation property.

4. Durability (The 'D')

Durability provides the guarantee of persistence.

• The Guarantee: Once a transaction is successfully committed, the changes it made are permanent and will survive any subsequent system failure, such as a power outage or crash.
• Why it Matters: When the database tells your application that a transaction is complete, you can trust that the data is safe. A successful bank transfer stays transferred. A confirmed order stays confirmed.
• How it's Implemented: This is primarily achieved through another type of transaction log, often called a Write-Ahead Log (WAL) or redo log.
  1. Before modifying the actual data files on disk, the database writes a description of the change to this log file.
  2. The transaction is only considered "committed" after this log entry is successfully saved to permanent storage (like an SSD).
  3. If the server crashes after the commit but before the main data files are updated, upon restart it will read the WAL and "redo" any committed changes that weren't fully saved, ensuring no data is lost.

This final video clip explains the concept of durability and how MySQL's redo log works.

Conclusion

You've now dissected the four pillars of transactional integrity in relational databases. Understanding ACID is non-negotiable for building reliable backend systems, as it defines the contract of trust between your application and your database.

Key Takeaways:

• Transaction: A sequence of operations treated as a single logical unit of work.
• Atomicity: All-or-nothing. The entire transaction either succeeds (commits) or is completely undone (rolls back).
• Consistency: The database moves from one valid state to another, always respecting data integrity rules and constraints.
• Isolation: Concurrent transactions don't interfere with each other. The system behaves as if transactions are running one after another.
• Durability: Once a transaction is committed, its changes are permanent and will survive system failures.

For a final review, the following articles provide excellent written summaries of everything we've covered. They are great resources to bookmark for future reference.

• The article "Acid Transactions" from AlgoMaster.io gives concise definitions and explains implementation details like WAL and MVCC.
• The "ACID Properties in DBMS" article from Great Learning provides a helpful summary table of the pros and cons of each property.

Preview of the Next Lesson:
In this lesson, you were introduced to the idea that Isolation isn't a single, absolute setting, but rather a spectrum of levels offering different trade-offs. In our next lesson, we will perform a deep dive into these transaction isolation levels, such as Read Committed and Serializable. You will learn exactly what guarantees each level provides, the performance implications of each, and how to choose the right level for your application's needs.