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:

1. Start Transaction: You begin the checkout process;
2. 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.
3. Commit Transaction: Once all these steps are successfully completed, the transaction is committed, meaning all the changes are saved to the database;
4. 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.