ACID databases as guarantee data safety

3 min read
139

ACID (atomicity, consistency, isolation, durability) is a set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps.

Wikipedia

The transaction is a sequence of database operations such as select, insert, delete, or update as one single work unit.

Atomicity

Atomicity guarantees that each transaction is treated as a single “unit”, which either succeeds completely or fails completely: if any of the statements constituting a transaction fails to complete, the entire transaction fails, and the database is left unchanged.

Wikipedia

Let’s look at an example of transferring money from one account to another. Both accounts have 1000 monetary units. In SQL syntax, it looks similar:

ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

What will happen if your server broke after the first command? Money from the first account will disappear. I’m not sure that your customers will be happy after a similar event.

All right, how to resolve this problem? Swap these commands, and your customers will be happy. But one tiny problem, your company spent 100 monetary units. Okay, I’m kidding, special for such kind of issues was invented transactions. Let’s wrap code into transactions.

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

All commands within the transaction must be executed. Otherwise, if something happens between the commands, the data will return to its original state.

ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk

In case of failure, a rollback will be performed

ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk

Consistency

Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof

Wikipedia

For example, the balance field cannot be negative based on the database settings. After the transaction is completed, the field will not be negative (if the field becomes negative, then the transaction is not executed).

Okay, Let’s added to our balance column NOT NULL Constraint:

ALTER TABLE account MODIFY balance decimal NOT NULL;

And let’s try to run our transaction:

START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk

Isolation

Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.

Wikipedia

The crucial and hardest part of these properties. Actually, how will your transaction communicate with each other? All database engines have a configuration for this. Let’s dive into the main kind of isolations.

Let’s use the transaction to transfer money (were 1000 for both accounts by default) with another transaction for check balance for both accounts. Which result will for the second transaction selects?

START TRANSACTION;
SELECT balance FROM account WHERE id = 1;
...
SELECT balance FROM account WHERE id = 2;
COMMIT;

I don’t know, and nobody knows. Why? Because in modern database engines, you can choose behavior for the whole database and each transaction.

The most popular isolation types:

  • Repeatable read
  • Read committed
  • Read uncommitted
  • Serializable

Repeatable read

Snapshot is a representation of data at a particular time, which remains the same even as changes are committed by other transactions. Used by certain isolation levels to allow consistent reads.

MySQL documentation

On the first read within a transaction, a snapshot is created. On subsequent reads, the data is taken from the snapshot and not from the real database. That would be means if you started reading in the 2nd transaction when the 1st transaction didn’t commit yet, then the 2nd transaction hasn’t known about the 1st transaction.

This isolation type always by default in the modern relational databases.

ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk

Read committed

2nd transaction can read data from 1st transaction if the 1st transaction is committed.

ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk

Read uncommitted

Also has known as dirty read. 2nd transaction can read data from 1st transaction even when the 1st transaction isn’t committed.

ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk

Serializable

The clearest type of isolation. All transactions are lined up. It’s generally safe, but the performance is awful. All transactions for these data will be added to a queue and run one after one.

ACID databases guarantee data safety — everything you need to know about database transactions and ACID properties. - WP Punk

Durability

Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash). This usually means that completed transactions (or their effects) are recorded in non-volatile memory.

Wikipedia

Generally, after the COMMIT command, your entire transaction will be saved to persistent storage and guarantee your data integrity.

Conclusion

All relational databases must follow the AСID rules to produce the transaction mechanism. To use transactions, you need to understand how they work.

Leave a Reply

Your email address will not be published. Required fields are marked *

Subscribe to news. I promise not to spam :)
Follow me, don't be shy