ACID Properties in DBMS and SQL

Complete Guide to ACID Properties in DBMS and SQL

Let us learn about ACID properties in DBMS and SQL. ACID is an abbreviation of Atomicity, Consistency, Isolation and Durability.  There are different types of DBMS applications and the ACID transactions play an important role for proper organisation of data within a database system.

We normally use SQL queries to access and manipulate a database and these ACID properties in SQL helps us to effectively execute DBMS queries. These ACID properties in DBMS require the usage of SQL operations such as COMMIT and ROLLBACK extensively.


COMMIT SQL Syntax:

ROLLBACK SQL Syntax:

ACID Properties in DBMS and SQL with ACID Databases explanation

Database Transaction

A database transaction is a series of steps executed as a single logical unit of work. It means that a particular transaction may or may not contain multiple operations within it. These operations of a database transaction may or may not be dependent on each other.

A database transaction should not violate database consistency constraints. To ensure consistency and integrity of data in a database management system, a database must maintain the ACID properties of DBMS.

A transaction in DBMS can consist of the following states:

  • Active
  • Partially committed
  • Committed
  • Failed
  • Aborted

Atomicity

The atomicity property states that every database transaction should be treated as an atomic unit. An atomic unit means that it cannot be divided or split into multiple parts. It means that every transaction in DBMS must be either executed completely or not at all.

The sequence of operations is, therefore, indivisible. No transaction can be partially completed. If a part of a transaction is completed and the remaining part is incomplete, then the entire transaction is considered to be a failed transaction.

A good DBMS must ensure atomicity in every possible situation such as system crash, power failure, etc. In short, the atomicity property can be summarised as All or Nothing.

An example of atomicity in DBMS:

Consider a bank transaction where a person is transferring an amount of $1000 from Account No. 123 to Account No. 789. The following would be the sequence of operations in this transaction:

  1. Debit $1000 from Account No. 123
  2. Credit $1000 to Account No. 789

As it can be seen, this database transaction consists of two separate operations. The database atomicity property will ensure that both the operations will execute or else the complete transaction will be failed.

Therefore, if only one of the transaction executes and the other remains in the failed state, the transaction will be failed. Both the operations should be executed completely for the transaction to complete.

Therefore, if only one of the transaction executes and the other remains in the failed state, the transaction will be failed. Both the operations should be executed completely for the transaction to complete.

Consistency

The consistency property states that after every database transaction, the overall database system should be in a consistent state. For a database to be consistent, every database transaction should be executed in isolation.

If the database was in a consistent state before a transaction, the database needs to be in a consistent state after the transaction as well. Therefore, a transaction cannot make the data within a database in an inconsistent state.


Every transaction in an ACID database will transform the database from one valid state to another valid state according to the property of consistency in DBMS.

The consistency property specifically indicates that all the integrity constraints such as foreign key, primary key, unique key, etc must be maintained after a database transaction.

If a database transaction leads to an inconsistent database state, then the database is rolled back to its previous consistent state.

An example of consistency in DBMS:

Consider a bank transaction where a person needs to transfer money in two different accounts. For instance, a person needs to transfer $500 in two different accounts. This transaction could be described in the following way.

  1. Transfer $500 from Account No. 123 to Account No. 456
  2. Transfer $500 from Account No. 123 to Account No. 789

Since this is a pair of a transaction, we assume that both of them executes simultaneously. Let’s assume that the balance in Account No. 123 is $700. So, both the operations read the balance in Account No. 123 as $500.

So, after the first transaction is executed, the second transaction should check the account balance after the first transaction. But, if it checks the initial account balance which is $700, the database will be in an inconsistent state which will lead to inconsistency in database records and a severe loss to the bank.

In order to prevent inconsistent database transaction, the consistency property in a DBMS ensures that if the database was in a consistent state before the transaction, it must be in a consistent state after the transaction as well. Isolation property (explained below) plays a major role here.

Isolation

The isolation property states that for every pair of a transaction, every single transaction should execute in such a way that it does not interfere with any other transaction. The results produced would, therefore, be independent of other transactions.

If more than one transaction starts concurrently without the implementation of isolation property, it could lead to an inconsistent database state as explained above.

Every transaction should be executed in isolation without interfering with a succeeding transaction. The concurrency control mechanism ensures that every transaction occurs separately without interfering with other transactions.

The modifications done by a transaction are not visible to other transaction unless it is updated to the database system.

An example of isolation in DBMS:

If you consider the previous example as explained in the consistency part, you will identify that there are two different transactions that will execute serially.

The first transaction will execute in isolation and will update the database system on its successful execution. The second transaction will, therefore, execute after the first transaction executes successfully and will refer to the account balance updated by the first transaction.

The second transaction will, therefore, throw an error saying that the account balance is less than $500.

Thus, we can say that the properties isolation and consistency are related to each other.

Durability

The durability property ensures that once the transaction is successfully executed, the changes made to the system by that particular transaction should be permanent.

So, even in the case of power failures or system crashes, the transaction should still prevalent in the database after backup.

When a transaction successfully executes and updates the modifications in the original system, it applies a COMMIT command. Therefore, the changes are permanently written to the system disk which allows us to retrieve database in the updated state even after power failures or system crashes.

The recovery management mechanism ensures the durability of a DBMS application. Every ACID database is, therefore, strong enough to handle system failures. An ACID database must be able to recover an inconsistent system into a consistent state.

It is, therefore, important for all the database management systems should be ACID compliant. These ACID properties of DBMS helps to keep the data within the database to be in a consistent state without any loss to the organisation.

If you have any doubts on ACID properties in DBMS or if you want to contribute to the ACID properties in SQL, do let us know about it in the comment section below.

Find more about ACID properties of DBMS on Wikipedia.

 

Tushar Soni

I am Tushar Soni, Co - Founder of CodingAlpha. I am a computer science student from India and passionate about Web Development and Programming. Connect with me on Facebook | LinkedIn | Google Plus

2 thoughts on “ACID Properties in DBMS and SQL

  • April 10, 2017 at 11:33 pm
    Permalink

    Are these ACID properties valid in a relational database management system as well?

    Reply
  • April 11, 2017 at 12:26 am
    Permalink

    Yes. The ACID properties are definitely valid in an RDBMS.

    Reply

Join The Discussion