What is ACID? Baby don't hurt me. No more.
Tags: mysql, database, postgres, transactions
Published: 2021-04-25 15:31:02

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:
```sql
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:
```sql
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:
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/he0id64gssv60df4d7dj.png)

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](https://dev.to/tutelaris/introduction-to-mysql-replication-97c)).

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 😌