What is ACID Properties in SQL Server

Many times, ACID properties happen to be the most asked interview question and most of the DBAs fail to answer. If you are a DBA, then ACID property is something mandatory to understand not just the acronym but also the concept behind it.

ACID properties are an acronym that is in conjunction with the transactions happening in the SQL Server. A transaction is a group of tasks that perform one more operation. For every transaction to happen in the database without any issues, there is a need to satisfy ACID properties.

A – Atomicity

Atomicity can be considered as an atomic unit in the database, wherein any transaction happening in the database should be finished without leaving any operation in the partial state. In the layman scenario, it can be considered as a “Do or Die”.

C – Consistency

It happens that a lot of data are stored in the databases in both small and big organizations. When a transaction is running, always there shouldn’t be any sort of impact on the data stored in the databases. Consistency is an idea to keep the data consistent irrespective of any number of data retrieval and transactions happening for the databases.

I – Isolation

In common terms, Isolation is nothing but separating an entity from another. Similarly, in the database as well, any transaction happening in the database should not interfere with the simultaneous transaction happening parallelly in the same database system. Irrespective of any number of transactions happening in the database system, there should not be a clash between the transactions itself.

D – Durability

A lot of times, database crashes are very common. Durability places a vital role in keeping the committed data safe. If a transaction is committed in the database, then the data is never lost. Irrespective of the failure of the system, the database should be durable enough to hold the committed content until the last updates. 

0 comments: