Database ACID Properties. Transactions

The most usefull explanation I've found in this blog from Anvesh Patel

Transactions are tools to achieve the ACID properties.

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

First, 

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.

Second, 

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.

Consistency is a very general term, which demands that the data must meet all validation rules.If rules are not meet the entire transaction must be cancelled and the affected rows rolled back to their pre-transaction state.

Third, 

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.

Fourth,

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.