SQL: ACID Transactions Simplified
Firstly, I'll explain what a transaction is, then what ACID is.
So what's a Transaction 💰🤝
No 😅, not money transactions.
A transaction is a set/group of database operations that either read or modify a database, and if any of the operations fails, the rest will fail. Let's get into what this means.
Let's use this code as a case study:
INSERT INTO users VALUES ("name", "email", "password"),
GET * FROM users
The code doesn't represent what an actual transaction in SQL looks like, but I'll use it to explain so that you can understand 😉
From the code above, if the INSERT command fails, the GET command will also fail automatically. Also, if the INSERT passes, but the GET command fails for some reason, then the INSERT will also be rendered invalid (Won't be adding/committing the user to the database).
Easy, right? You know it 😉. And if you're new to my blogs, welcome 😁. Now, let's move on.
So what is ACID 🤔
This isn't chemistry class 🤣, trust me. ACID is simply the acronym for Atomicity, Consistency, Isolation & Durability. So let's talk about each one of them separately.
Atomicity is simply saying each of the commands in a transaction will be treated as a single unit and will either fail or succeed together.
Using our case study: The INSERT and GET commands will be a single unit. So if the INSERT fails, everything fails, the whole transaction, including the GET command, or if the GET command fails, vice versa. Transactions do not occur partially.
A transaction could either abort or commit.
- Abort - The transaction doesn't complete, and all of its commands fail.
- Commit - The transaction completes successfully.
Consistency means that the data added to a database must match its constraints. That's why you'll get an error if you try to add an integer to a VARCHAR field in some databases (most usually).
Yes, that's all about consistency, yes, really 🙀😅.
Okay, Isolation 🚶♀️
Just as it implies, it means all transactions run independently, so concurrent transactions (transactions running simultaneously) do not interfere with each other. The transactions do not know each other and are only committed to the database when their operations are successful. So they seem sequential.
Durability indicates that after a transaction completes, the changes made to the data during that transaction will persist, meaning the update or modifications to the database are stored even if there is a power failure or system failure immediately after a successful transaction.
And that wraps it up 🎊 for ACID transactions.
Here is a list of links you can visit to learn more about ACID transactions
Love you all, Peace ❤️✌️