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:
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.
In case of failure, a rollback will be performed
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;
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.
Read committed
2nd transaction can read data from 1st transaction if the 1st transaction is committed.
Read uncommitted
Also has known as dirty read. 2nd transaction can read data from 1st transaction even when the 1st transaction isn’t committed.
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.
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.
