SQL Server ACID Properties


ACID properties are defined as Atomicity, consistency, isolation, durability. Sql server follow these properties religiously. If these properties were not followed can cause database crash.

Atomicity: It is an all-or-none proposition. It states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails.

Consistency: This states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.
The database must be in a consistent state both before the transaction begins and after the transaction has completed, irrespective of whether the transaction was a success (and therefore committed) or a failure Example :
If an insert violates a check constraint or the referential integrity of the table in question, then the transaction must be rolled back or a compensating action 

Isolation: This keeps transactions separated from each other until they’re finished. It requires that multiple transactions occurring at the same time not impact each other’s execution.  The isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other

Durability: It guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. It ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures

No comments:

Post a Comment