What are ACID Properties in DBMS – Simplified

Introduction

Transaction Processing is an important feature of Relational Databases and makes them what they are generally used for – storing relational data and working in the form of transactions.

In simple terms, a transaction is a bunch of operations that read and write data to a database. It can be as simple as querying content from a Blog Post database to updating funds in a Bank Account.

Based on the ability to manage database operations as transactions, relational databases are classified as OLTP – Online Transaction Processing Databases; which can execute a number of transactions occurring concurrently.

But what makes a Transactional Database work that way?

There are 4 properties of a Transaction, with which a Database must ensure in the world of concurrency and system failures. These 4 properties are put together by the acronym – ACID

1. Atomicity
2. Consistency
3. Integrity
4. Durability

Popular Databases such as SQL Server, MySQL, Oracle or PostgreSQL are examples of OLTP Databases and these databases ensure these properties are maintained in transactions.

Now let’s understand each of these with some analysis.

Atomicity

This property of a Transaction ensures that all the actions performed within a transaction are atomic – meaning either all the actions are performed or none of it is performed. Users shouldn’t be worried about any incomplete transactions, which may occur due to system crashes or any internal errors.

How does Transaction Atomicity work?

Generally, an incomplete transaction could happen on three occasions –

• A transaction could be aborted or terminated by the Database system due to some internal issue, in which case the transaction is restarted and executed.
  
• A system crash could cause the transactions become incomplete, due to some external factors such as a power failure when one or more transactions are in progress.
  
• Some unexpected situation, such as an unexpected read error or a disk issue can cause the transaction to be aborted.

In all of these situations, it is the responsibility of the Database system to ensure there are no incomplete transactions – either complete the entire transaction till the end, or undo everything to the starting state.

Generally, Database systems maintain a log of all the writes that it performs to the database. This includes the starting state and the ending state of the data. When any of the above scenarios happens and the transaction fails before the changes are written to the disk, the Database system reverts to the old state using this log.

Consistency

Each transaction that is run on a database, must preserve the consistency of the database. Meaning, that at the end of any transaction the database must still be in a consistent state.

What does Consistency mean?

Let’s say for example, a user operation tries to transfer 100 dollars from a user account to another target account.

This sequence of operations happens in a single transaction –

1. GET Balance for Sender Account (to check if there are sufficient funds available)
2. UPDATE Balance for Sender Account (minus the balance with 100 dollars)
3. UPDATE Balance for Receiver Account (adds the balance with 100 dollars)
4. GET Balance for Sender Account (to show the final summary)

All four steps in the above sequence are considered a single transaction. Before the execution of Step 1, the Database has some funds available for both Sender and Receiver accounts. This is the existing consistent state.

In between Step 2 and 3, both the accounts have 100 dollars less (the total balance of both the accounts combined). Here we can call the database is in an inconsistent state.

After Steps 3 and 4, both the accounts combined will now have the amount same as the amount that was there before Step 1. This means that the database is now again in a consistent state.

Inconsistency means that due to the transaction, the state (in this context the total amount maintained across the database combined) can change.

Let’s say there’s a system failure that may happen between Steps 2 and 3, this can result in an incomplete transaction (it hasn’t gone past all the steps in the transaction), causing inconsistency. This will not happen, because the Database System ensures Atomicity of a transaction.

However, it is the user’s responsibility to hold consistency by ensuring that the actions performed during a transaction will be accurate and leave the database in a consistent state.

Isolation

Since a database handles millions of transactions in a real-world perspective (there will be millions of users making fund transfers at any given moment), a database system can execute multiple transactions at a time, for performance considerations.

With this background, users are ensured that transactions are isolated from one another, even though they are run concurrently at the same time by the system and one transaction is not affected by another transaction.

This is ensured by the Database System, and the user doesn’t need to worry about it.

How does a Database achieve Isolation?

A Database makes sure that actions of a transaction are executed such that even though multiple actions are performed across transactions – we call that interleaving – the summary of all these transactions are the same, no matter which other they are executed.

A Database creates a Schedule based on the actions of multiple transactions such that consistency is maintained.

The database system then executes these schedules in a serializable or concurrent manner, with a possibility of Read-Write, Write-Write or Write-Read conflicts in mind.

Atomicity, Isolation and Consistency at Transaction levels ensure a Database level Consistency.

Durability

Once the Database system informs the user that the transaction was successful, the changes should persist even if the system fails before all the changes are saved onto the disk.

This property is similar to atomicity in its implementation – remember the log that the Database system maintains to keep track of all the writes that happen onto the Database? When a system crashes due to some issue, the same log is used to restore these changes when the system is up again.

This is the work of a Recovery Manager, which is invoked in case of a crash and it takes care of the atomicity and durability of the transactions by undoing whatever is not completed and ensuring all the committed transactions survive crashes.

Conclusion

In this article we have explored the ACID properties—Atomicity, Consistency, Isolation, and Durability—that are crucial for reliable transaction processing in relational databases.

Atomicity ensures transactions are all committed or else nothing goes in, while Consistency maintains data integrity across states before and after a transaction is made.

Isolation allows concurrent transactions without interference, and Durability guarantees data persistence even after system failures.

We have also seen how each of these properties play into the picture, and how Database Systems employ different techniques – such as Scheduling, Concurrency and Recovery Manager to ensure each of these are guaranteed in an OLTP database.