SQL Server : ACID Properties in SQL

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

ACID properties are used to avoid errors that occur while transactions in sql

ACID is an abbreviation for Atomicity, Consistency, Isolation, and Durability which are properties to maintain data consistency

Transactions are a set of SQL statements used to modify the data in the database

Transactions perform read and write operations on the database.

If multiple transactions are occurring on the single database then the data accessed by different users seem to be different. Therefore having such properties becomes a compulsion.

Atomicity

Transactions include different SQL statements together. Atomicity is used to make sure that each transaction is treated as a set.

In Atomicity transactions, either get executed or fail

If any of the statements in the transaction fails the whole transactions fail or else if all the statements are executed successfully then the transaction is successful.

Atomicity prevents the updates to the database that are occurring only partially

Following two operations are included in atomicity:

Abort: If a transaction is aborted then the changes made to the database are not reflected on the database

Commit: If a transaction is successful then the data is effected in database

Consistency

Consistency as to be maintained in a database so that the data in a database is transferred from one valid state to another valid state

It refers to the correctness of the data

Consistency is used to make sure that the data which is been modified in the database is not corrupted

For example, in an application that transfers funds from one account to another, the consistency property ensures that the total value of funds in both the accounts is the same at the start and end of each transaction.

Isolation

Isolation is used to ensure that multiple transactions that occur concurrently don’t leave the database in an inconsistent manner.

The transaction occurring do not interfere and get executed independently

Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed.

This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order.

Durability

Durability is used to ensure that once the data is committed it will remain the same and not get affected by system failure.

This usually means that completed transactions (or their effects) are recorded in memory

For example, in an application that transfers funds from one account to another, the durability property ensures that the changes made to each account will not be reversed.