Database ACID Properties:
ACID properties are very old and important concept of database theory. I know that you can find lots of posts on this topic, but still I would like to start my journey with this topic as one of very important and my favorite theory of Database system.
Database System plays with lots of different types of transactions where all transaction has certain characteristic. This characteristic is known ACID Properties.
ACID Properties take grantee for all database transactions to accomplish all tasks.
Many times interviewers ask this question in an interview that, What is ACID Property in database?
Here I am going to explain with a simple example.
A = Atomicity
C = Consistency
I = Isolation
D = Durability
Atomicity: means either all or none, for example,
One Application is going to insert 30 records in one block of transaction. During this insertion any serious problem occurs and at this point only 12 records have been processed. In this state transaction is not going to insert only 12 records it will roll back this whole transaction so this will process either all or none.
Consistency: means to bring database from one valid state to another.
There should be always defined some data rule, constraint, trigger at database end as well as application end.
Whatever data is going to insert they all should be validated by defining rules and ensure that there are no invalid data is going to insert in database so this way it will manage consistency of the database. In any case running transaction violate rule at that same time entire transaction will be a rollback.
Isolation: means each transaction is unaware of another transaction.
In one shop seller is selling items very fast and due to this number of items is also decreasing in stocks and at other side one person is also adding new items in stocks.
Here, this both transactions is different and totally unaware of another. These properties ensure that one transaction is not interrupted by another transaction. For database transaction this one of the important properties because any database system is going with lots of concurrent and parallel transaction where Isolation property is very much require and ensure that all transactions is defined under proper isolation level. A different database technology has different type of default Isolation level like,
Oracle has READ_COMMITTED
MySQL has REPETABLE_READ
MSSQL has READ_COMMITTED
PostgreSQL has READ_COMMITTED
DB2 has READ_COMMITTED
Generally READ_COMMITTED Isolation level is most preferable, but many times it’s also required to show READ_UNCOMMITTED data in a data history kind of pages.
Very soon I will publish details on Isolation level with example for different RDBMS Products. I will update link over here.
Durability: means to keep committed data forever.
Application inserted 30 records in one transaction and this transaction is completed and committed successfully in the database that means this record persist forever in database until and unless it’s not deleted by any application users or database users.
ACID Property is one important concept of database system.