What is ACID?
Swipe to show menu
We will begin our exploration of advanced SQL topics with the concept of ACID principles.
ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These four properties are fundamental principles in database management and are designed to ensure the reliability of database transactions.
You may have noticed that we use the word transaction in the ACID definition. You might think of a bank transaction when you hear this word, but in the context of databases, a transaction is defined a bit differently.
A transaction refers to a sequence of one or more database operations that are treated as a single logical unit of work.
Imagine you are using an online shopping website.
You add items to your cart, proceed to checkout, and make a payment. This entire process can be thought of as a database transaction. Here’s how it works:
- Start Transaction: you begin the checkout process;
- Operations:
- The system verifies that the items in your cart are still in stock;
- Your payment information is processed;
- The inventory is updated to reflect the items you purchased;
- An order record is created in the database.
- Commit Transaction: once all these steps are successfully completed, the transaction is committed, meaning all the changes are saved to the database;
- Rollback (if needed): if any step fails (e.g., payment doesn’t go through), the transaction is rolled back, meaning none of the changes are saved. This ensures the database remains in a consistent state, as if the transaction never happened.
This example shows how a transaction groups multiple operations into a single, reliable process.
Why do we need ACID?
ACID properties are important in databases for three key reasons:
- Firstly, they guarantee transaction reliability by treating transactions as single, indivisible units, preventing partial updates and maintaining consistency;
- Secondly, they enable concurrency control by ensuring transactions are executed independently, preventing interference and preserving data consistency in multi-user environments;
- Lastly, they ensure data durability by permanently recording committed transactions, surviving system failures and ensuring data recoverability.
Let's consider a situation involving a bank account:
- Transaction 1 (T1): You transfer $100 from your savings account to your checking account.
- At the same time, Transaction 2 (T2): You check your savings account balance online.
- But then, T1 encounters an error—perhaps due to a network issue—and fails to complete the transfer.
- Meanwhile, T2 completes and shows your savings account balance, including the $100 transfer that didn't actually happen yet.
This violates the Isolation property of ACID. Even though the transfer wasn't completed (due to T1's failure), you've already seen the effect of it in your account balance. This inconsistency can confuse users and lead to incorrect financial decisions.
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
