What is ACID?
We will begin our exploration of advanced SQL topics with the concept of ACID principles.
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.
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;
- Thirdly, 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:
1. Transaction 1 (T1): You transfer $100 from your savings account to your checking account.
2. At the same time, Transaction 2 (T2): You check your savings account balance online.
3. But then, T1 encounters an error—perhaps due to a network issue—and fails to complete the transfer.
4. 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.
Everything was clear?
Course Content
Advanced Techniques in SQL
Advanced Techniques in SQL
What is ACID?
We will begin our exploration of advanced SQL topics with the concept of ACID principles.
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.
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;
- Thirdly, 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:
1. Transaction 1 (T1): You transfer $100 from your savings account to your checking account.
2. At the same time, Transaction 2 (T2): You check your savings account balance online.
3. But then, T1 encounters an error—perhaps due to a network issue—and fails to complete the transfer.
4. 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.
Everything was clear?
