What is ACID? Baby don't hurt me. No more.

Hello, ultra devs! 👋🏻⚡️
A lot of developers currently use relational databases such as MySQL, PostgreSQL, and so on. And they are probably familiar with transactions. But what the "transaction" is and what properties does it have?

Today I would like to talk about transaction properties that were combined into the cool and scary word ACID and reveal the truth about each letter in this word 👆🏻

What is ACID?

ACID is an acronym of the following words:

1. Atomicity;
2. Consistency;
3. Isolation;
4. Durability.

If we have a certain operation in a database that satisfies these properties, we can call this operation "transaction".

Ok, that's nice, but these words are just words without an explanation. Let's dig deeper into each of them! 🚜

Atomicity

Atomicity is a nice property that guarantees, that transaction is atomic and cannot be broken into smaller parts. If something happens inside that transaction, it will be reverted for all affected fields described inside this transaction.

Imagine we have the following query:

START TRANSACTION;
INSERT INTO posts VALUES ('title', 'body', 'draft');
SELECT @counter := COUNT(id) FROM posts WHERE status = 'draft';
UPDATE posts_statistics SET posts_amount = @counter + 1  WHERE status = 'draft';
COMMIT;

This transaction basically inserts the post and increments the post_statistics.posts_amount field for draft posts.
If an UPDATE of the posts_statistics fails, the whole transaction will be rolled back and the post won't be inserted in the posts table as well. So, these two operations (actually three, SELECT is also an operation) are highly coupled and can be considered atomic.

One more nice property of atomicity is that all clients won't see the change in tables until the transaction gets committed. So, if we execute the transaction described above and during the transaction execution, we execute the following query SELECT COUNT(id) FROM posts WHERE status = 'draft' from another client, we will see a different value than the @counter variable has.

Consistency

This is a very ambiguous and unclear letter. You might hear about it in CAP theorem (leave a comment if you wanna read about CAP theorem and types of consistencies there 😌) or in Consistent hashing. This Consistency is more about data consistency on the application level. Basically, it means, that before committing the transaction system needs to make sure to satisfy all invariants that were set up beforehand and were valid at the moment of starting the transaction.

Imagine the situation when you buy something on dev.to shop. You reached the point when you need to pay for a cool T-shirt. You filled out all the credentials and clicked Pay. In order to satisfy consistency property, the sum value of your amount of money and dev.to's amount of money should be the same at the moment of the beginning of the transaction and the moment of committing it.

This sum value can be considered an invariant for this transaction.

Isolation

This property is very useful for the database to prevent so-called race conditions. You may wonder what is "race condition".

Imagine, you have multiple clients trying to access the same data and change it at the same time. Let's say, they concurrently insert posts using transaction that is described in the Atomicity chapter:

START TRANSACTION;
INSERT INTO posts VALUES ('title', 'body', 'draft');
SELECT @counter := COUNT(id) FROM posts WHERE status = 'draft';
UPDATE posts_statistics SET posts_amount = @counter + 1  WHERE status = 'draft';
COMMIT;

Let's say initially we have 42 posts (just because 42). Since they do it at the same time, here is what might happen:

Basically, they added their posts, but the total number of posts should be equal to 44, but they ended up with 43.

So, in an ideal world isolation property ensures that concurrent transactions look like they get executed one after another or serially. Therefore it is also known as serializability. In practice, the implementation of this property rarely serializable and some databases use so-called Weak isolation (please, leave a comment if you wanna read about it).

Durability

The last, but the very important property is Durability. This property means that once a transaction got successfully committed, it will remain committed and won't be forgotten even if the data gets corrupted due to the power or the database crash.

We can distinguish durability based on the database architecture:

1. Single-node database;
2. Replication database (you can read more about replication here).

For single-node database, durability makes sure that the data won't be corrupted by writing on the disk and has a recovery mechanism in case of disk corruption.

For replication database, durability makes sure that the data got written in a certain amount of replicas, so in case of a crash, it will be propagated to other replicas.

Summary

Today we've learned about transaction properties that are aggregated into the ACID word.

ACID is an acronym of the following words:

1. Atomicity – ensures that all transactions are atomic and cannot be broken down into small parts;
2. Consistency – ensures that the transaction does not violate application invariants;
3. Isolation – ensures that race conditions do not happen;
4. Durability – ensures that once the transaction is committed, it is committed forever.

That's it! Thank you for your attention! I hope you liked this post 😌