ACID Properties in SQL

I'll try to write a short article that explains the ACID properties in SQL in a simple way. Here is what I came up with:

What are ACID Properties in SQL?

Imagine you have a piggy bank where you save your money. Every time you get some money, you put it in the piggy bank. Every time you want to buy something, you take some money out of the piggy bank. You also keep a notebook where you write down how much money you have in the piggy bank and what you did with it.

Now, suppose you want to buy a new toy that costs $10. You have $15 in your piggy bank, so you can afford it. You go to the store with your piggy bank and your notebook. You find the toy you want and you go to the cashier. You open your piggy bank and you take out $10. You give the money to the cashier and you get the toy. You also write down in your notebook that you spent $10 on the toy and you have $5 left in your piggy bank.

Everything seems fine, right? But what if something goes wrong in the middle of this process? For example, what if you drop your piggy bank and it breaks, and all your money falls out? Or what if you forget to write down in your notebook that you bought the toy? Or what if someone else takes your notebook and changes the numbers? Or what if the cashier gives you the wrong toy or no toy at all?

These are some of the problems that can happen when you deal with money or any other kind of data. To avoid these problems, you need to follow some rules that make sure your data is safe and correct. These rules are called ACID properties. ACID stands for Atomicity, Consistency, Isolation, and Durability. Let’s see what each of these words means and how they apply to your piggy bank example.

Atomicity

Atomicity means that you do everything or nothing. When you buy the toy, you have to do two things: take the money out of the piggy bank and write it down in the notebook. These two things are part of the same transaction, which is a fancy word for a group of actions that are related. You can’t do only one of them and leave the other one undone. That would make your data inconsistent, which means that it doesn’t match reality.

For example, if you take the money out of the piggy bank but you don’t write it down in the notebook, your notebook will say that you have more money than you actually have. Or if you write it down in the notebook but you don’t take the money out of the piggy bank, your notebook will say that you have less money than you actually have. Either way, your notebook is lying to you.

To prevent this, you need to make sure that your transaction is atomic, which means that it can’t be split into smaller parts. If something goes wrong in the middle of the transaction, you have to cancel everything and go back to the way things were before. For example, if you drop your piggy bank and it breaks, you have to pick up all the money and put it back in the piggy bank. You also have to erase what you wrote in the notebook. This way, you can start over and try again.

Consistency

Consistency means that you follow the rules. When you deal with data, you have to follow some rules that make sense and that don’t change. These rules are called constraints, which are like boundaries that limit what you can do with your data. For example, one constraint is that you can’t have negative money in your piggy bank. Another constraint is that you can’t spend more money than you have.

When you buy the toy, you have to check that you follow these constraints. If you don’t, you have to stop the transaction and fix the problem. For example, if you try to buy a toy that costs $20, but you only have $15 in your piggy bank, you can’t do it. You have to either find more money or choose a cheaper toy. Otherwise, you will break the rule that says you can’t spend more money than you have.

To ensure this, you need to make sure that your transaction is consistent, which means that it doesn’t violate any constraints. If it does, you have to reject the transaction and keep your data as it was before. For example, if you try to buy a toy that costs $20, but you only have $15 in your piggy bank, you have to say no to the cashier and keep your money and your notebook as they were.

Isolation

Isolation means that you mind your own business. When you deal with data, you may not be the only one who is using it. There may be other people or programs that are also reading or writing data at the same time. These are called concurrent transactions, which means that they happen at the same time or overlap with each other. You have to be careful not to interfere with them or let them interfere with you.

For example, suppose you share your piggy bank and your notebook with your sibling. You both have your own money and your own transactions. You want to buy a toy and your sibling wants to buy a book. You both go to the store with your piggy bank and your notebook. You find the toy you want and your sibling finds the book they want. You both go to the cashier and you both open your piggy bank and your notebook. You both take out some money and you both write it down in the notebook. You both give the money to the cashier and you both get what you want.

What could go wrong here? Well, a lot of things. For example, what if you and your sibling take out the same money from the piggy bank? Or what if you and your sibling write on the same page of the notebook? Or what if you and your sibling get confused and mix up your money or your items? These are some of the problems that can happen when you have concurrent transactions. They can make your data inaccurate, which means that it doesn’t reflect the truth.

To avoid this, you need to make sure that your transaction is isolated, which means that it doesn’t affect or get affected by other transactions. You have to act as if you were the only one using the data. For example, if you and your sibling share your piggy bank and your notebook, you have to take turns to use them. You can’t use them at the same time. You have to wait until the other one is done before you start. This way, you can keep your data accurate.

Durability

Durability means that you don’t lose anything. When you deal with data, you have to make sure that it is stored safely and permanently. You don’t want to lose your data because of a power outage, a system crash, a disk failure, or any other kind of disaster. You want to be able to access your data anytime and anywhere.

For example, suppose you buy the toy and everything goes well. You take the money out of the piggy bank, you write it down in the notebook, you give the money to the cashier, and you get the toy. You are happy and you go home with your toy. But what if something happens to your piggy bank or your notebook? What if they get stolen, burned, wet, or damaged? What if you lose them or forget where you put them? How will you know how much money you have left? How will you prove that you bought the toy?

These are some of the risks that can happen when you store your data in a physical way. They can make your data unavailable, which means that you can’t access it when you need it. To prevent this, you need to make sure that your transaction is durable, which means that it stays in the system even if something bad happens. You have to backup your data in a digital way. For example, if you have a piggy bank and a notebook, you can also have a bank account and a computer. You can transfer your money from your piggy bank to your bank account and you can save your notebook in your computer. This way, you can always check your balance and your history online.

I hope this article helps you understand the ACID properties in SQL. If you liked it, please share it with your friends and leave a comment below. Thank you for reading! ??