ACID transactions in databases

ACID is an acronym that refers to 4 properties. If a database transaction has these 4 properties, it is said to be an ACID transaction.

Let’s take a look at these 4 properties.

1. Atomicity

   • All the operations within a transaction execute as an atomic unit. This means that either all of them succeed or none of them do. This prevents data from being corrupted as invalid data is not saved in the database.

   • e.g. Consider a transaction that debits an account A and credits an account B. If the credit operation has failed, we would want the debit operation to also be rolled back.

2. Consistency

   • This specifies that the database state should be consistent before and after the transaction and even in the case of failures.

   • e.g. Consider a transaction that debits an account A by Rs. 100 and credits an account B by Rs. 100. However, the sum of the amounts in both the accounts would remain the same before and after the transaction.

3. Isolation

   • This specifies that when multiple transactions occur concurrently in the database, they must occur in isolation. Even if the transactions are occurring concurrently, the result so computed should be the same as what we would get if the transactions were executed in a serial order. Different protocols may be used to implement transaction isolation such as lock based protocols or time based protocols.

   • An example of a transaction not occurring in isolation can be seen by looking at the following sequence of interleaved operations in 2 concurrent transactions, T1 and T2.

   • (T1) Reads A as 100, (T2) Writes A as 150, (T1) Writes A as 200.

   • As the end result, we have the value of A as 200.

   • Now, here T1 is the older transaction and T2 is the younger transaction. So if they were to occur serially, T1 would occur before T2. Consequently, we would have the value of A as 150 at the end.

   • Therefore, as provided, these transactions are not occurring in isolation, as the value that we get is not equal to the value that is obtained by executing these transactions serially.

   • This is just an example, different isolation levels may allow varying degrees of concurrent behavior.

4. Durability

   • This specifies that the changes made by successful transactions should be saved even in the event of a system failure. A transaction can be considered successful if it has committed its changes to the database. This can be implemented using different mechanisms such as write-ahead logging (WAL).

   • An example of a non-durable transaction would be when a transaction is committed but before the changes could be persisted to the disk from the memory, a power failure occurs. In absence of a recovery mechanism, the changes made by the transaction are lost even though the transaction had committed.

References

• https://www.databricks.com/glossary/acid-transactions

• https://www.geeksforgeeks.org/acid-properties-in-dbms/