SQL Server ACID Properties of Transaction

ACID Properties

To understand transaction in depth, we must know all about its properties. We will look at ACID properties which are related to transaction. ACID is an acronym for Atomicity, Consistency, Isolation and Durability.  Now you look at each of them one by one. 

Atomicity means that a transaction is a unit of work.  It means that each statement of transaction will be considered as a component of the unit of work. If any statement fails then all statements will be rejected. In the above example, when salary is not updated then updated grade is cancelled. 

Now we understand about consistency.  If transaction is committed or rolls backed the state of database remains consistent. It means that if execution of any statement in transaction violates the rule or constraint then all statements of the transaction will be roll backed. In the above example, what if grade is updated without updating the salary. In this situation business rule will be violated and it will be in an inconsistent state. 

Now we look at the concept of isolation. Suppose that two users are performing some transactions on a table. Both the transactions will be independent and isolated to each other. The first transaction will not know anything about another transaction and vice versa. It means that one transaction will never see the intermediate state of another transaction. The concepts of isolation are important in the context of read and write operations upon data. 

Finally durability means when the transaction is committed it endures. The work performed by transaction is saved permanently and will be available even after rebooting the system.