What are the Database Transactions, A.C.I.D.?

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties of database transactions (a transaction represents any change in the database) intended to guarantee the validity of a database even in the event of power failures or program errors.

 

ACID Visualization

Parts of ACID

1. Atomicity

Atomicity is when an update occurs to a database, either all or none of the update becomes available to anyone beyond the user or application performing the update. If an issue occurs with the hardware or software involved, the database will not be updated partially.

Example :

Consider a bank transaction where there are two operations:

  1. Money is deducted from a customer’s bank account
  2. The same amount is added to another account

If one of these two parts fail, then the entire transaction should be considered invalid. The transaction should be rolled back to the state before the transaction started.

2. Consistency

Consistency ensures that any programming error will not result in violation of predefined rules of the database. No transaction should have any adverse effect on the data residing on the database.

Example :

Consider a column in a database with the data type – “bigint” (integer). Let there be a transaction to add a row into the database where the same column has a “varchar” (character) data type. This transaction must fail as it would violate the existing rules of the database.

3. Isolation

The isolation property is required when there are concurrent transactions on a database. Concurrent transactions are ones that occur at the same time, such as shared multiple users accessing shared data sources.

Example :

Consider two bank transactions T1 and T2 executed at the same time which are trying to modify the same data. T1 wants to transfer $10 from account A to account B. T2 wants to transfer $10 from B to A. These can be summarized as four actions:

  1. T1 subtracts $10 from A
  2. T1 adds $10 to B
  3. T2 subtracts $10 from B
  4. T2 adds $10 to A

For isolation to be maintained, the transactions should occur sequentially in the order stated above. Even if T1 fails, T2 can be executed on valid data.

Let’s consider the case when the transaction does not occur sequentially and isolation property is not followed. In such a case, the sequence of actions may be:

  1. T1 subtracts $10 from A
  2. T2 subtracts $10 from B
  3. T2 adds $10 to A
  4. T1 adds $10 to B

In this case, if the transaction (T1) fails at Step 4, T2 would have already occurred, and it won’t be possible to revert the database back to the state before T1 started.

4. Durability

The durability property ensures that once the transaction has been committed, it will remain so, even in the event of power loss, crash, or programming error.

Example:

In a bank transaction, $10 is deducted from account A and added to account B. If this program is successfully executed, the results of the transaction will be stored permanently. Even in the event of a database crash or power failure, the database will not be affected.

Conclusion

ACID allows users to solely focus on their programming logic, without having to worry about additional failures a system might encounter. This database property ensures that the user does not have to write additional code to correctly implement detection and resolution of failures.

Rohan Joseph

About Rohan Joseph

Practicing the dark arts of data science. I am currently pursuing Master's in Operations Research at Virginia Tech and working with Chartio to democratize analytics in every organization.