Let us learn about ACID properties in DBMS and SQL programming language. Here you shall find a comprehensive guide describing the ACID properties with example and a list of ACID compliant databases.
Before we get into ACID properties, let us try to understand what a DBMS is and what are database transactions.
What is a DBMS?
A DBMS is an abbreviation for Database Management System. It is basically a collection of data and a set of programs to store, modify and retrieve the data.
What is a database transaction?
A database transaction is a series of steps executed as a single logical unit of work. A transaction should always be an ACID transaction.
A transaction in DBMS can consist of the following states:
- Active
- Partially committed
- Committed
- Failed
- Aborted
We normally use SQL queries to access and manipulate a database and these ACID properties in database help 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
1 | commit; |
ROLLBACK SQL Syntax
1 | rollback to savepoint-name; |
Must Read: Difference Between DBMS and RDBMS
Why The Need For ACID Properties?
ACID is an abbreviation for four properties viz. Atomicity, Consistency, Isolation and Durability.
It is a set of properties that must be satisfied by database transactions. It is important for all the database management systems should be ACID compliant.
These ACID properties in DBMS helps to keep the data within the database to be in a consistent state without any loss to the organisation.
To ensure consistency and integrity of data in a DBMS, a database must maintain the ACID properties in database.
ACID PROPERTIES IN DBMS
Property #1: 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.
In other words, 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.
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 could be the sequence of operations in this transaction:
- Debit $1000 from Account No. 123
- 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.
Property #2: Consistency
The database 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.
Every transaction in an ACID compliant 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.
Example of Consistency in DBMS:
Consider a bank transaction where a person needs to transfer $500 in two different accounts.
This transaction could be described in the following way:
- Transfer $500 from Account No. 123 to Account No. 456
- Transfer $500 from Account No. 123 to Account No. 789
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.
Must Read: E.F. Codd’s 12 Rules For RDBMS
Property #3: 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.
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.
Property #4: 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 a power failure or a system crash, the transaction should still be prevalent in the database after a 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.
Let us also see few of the ACID database examples below:
- FoundationDB
- OrientDB
- Redis
- ZODB
If you have any doubts on ACID properties in DBMS or if you have any additional points to share about ACID properties in SQL, do let us know about it in the comment section below.
Find more about ACID properties in DBMS on Wikipedia.
Are these ACID properties valid in a relational database management system as well?
Yes. The ACID properties are definitely valid in an RDBMS.
What is a Transaction Manager in SQL?