What is ACID? Why is it important?
A transaction is a single unit of logic or work made up of read and/or write operations to access and/or modify the contents of the database.
To ensure consistency and integrity, a transaction must satisfy the following properties:
A transaction must either complete fully, and in its entirety, or it does not happen at all.
Let’s take for example the transaction of 1000 from account A, which has 10000, to account B, which has 5000:
A = A - 1000
B = B + 1000
If the system fails in step 3, we will be left with the inconsistency of the amount subtracted from account A, and not transferred to account B, leading to inconsistency.
Having this all or nothing approach, Atomicity ensures that if the system fails in step 3, the updates will not be reflected in the database.
For data to be committed and saved, it must maintain integrity constraints and data validation rules. Let’s go back to the transfer example above, the sum of the amount stored in the two accounts is 10000 + 5000 = 15000. If the result of the transaction leads to just subtracting 1000 from A and not adding it to B, we will be left with a sum of 14000 which is not permitted. Or, if 1000 is added to B but not subtracted from A, we will be left with a sum of 16000, which also is not permitted. In a case like this, the transaction will be rolled back into the previous consistent state.
A consistent transaction is where 1000 is both subtracted from A and added to B leading to a sum of 15000, which leads to the same state as before.
Isolation means that concurrent transactions should be running in a distinct and isolated way. The evolution of the state in a transaction in progress should not be visible not to affect the evolution of another transaction happening simultaneously.
For example, an e-commerce site has a product for sale, and the available quantity is 6. Two users place orders at the same time, one for 4 units and the other for 3. The two transactions must occur in some order, meaning that the two users cannot both order with the selected options, since the total would be 7 items, more than the actual stock. Either user one’s order is complete and he ends up getting 4 units and user one is informed that only 2 units remain, or user two’s order is complete and user one is informed that only 3 units remain.
Isolation has four layer levels: Serializable, Repeatable reads, Read committed, and Read uncommitted. The higher the level, the more system resources needed. However, the trade of system resources for assured integrity is worth it.
Durability ensures that when transactions are committed they will be permanently reflected in the database, even if a system failure occurs.
This is usually achieved by implementing transaction logs where transactions are logged upon their completion. Thus, in case of a system failure or a crash, the database reads the log and continues the persistence in non-volatile memory for data to be saved, permanently.
ACID properties of transactions help achieve a reliable database system and overall software architecture. That being said, there is always a sweet spot to aim for in terms of balance between resource optimization and security and integrity. Use ACID properties the way fits your use case best.