This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Database ACID Properties:
ACID properties are an ancient and valuable concept of database theory. I know that you can find lots of posts about this topic, but still, I would like to start my journey with this subject as one of very important and my favourite theory of Database system.
Database System plays with lots of different types of transactions where all transaction has a particular 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
First,
Atomicity: means either all or none, for example,
One Application is going to insert 30 records in one transaction. During this insertion, any problem occurs, and at this point, only 12 records have 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 valid state.
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 setting rules and ensure that there are no invalid data is going to insert in the 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 rollback.
Isolation: means each transaction is unaware of another transaction.
One shop seller is selling items very fast, and the stock is also decreasing. At the same time, another person is also adding a new item in the stock.
Here, this both transactions is different and unaware of each other.
Isolation level ensures 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 transactions where Isolation property is very much required and ensure that all transactions are 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
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.
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 records persist forever in the database until and unless it’s not deleted by any application users or database users.
