ACID properties in SQL Server

The ACID properties in SQL are a set of characteristics that ensure the reliability and consistency of transactions in a relational database. It is an acronym that stands for Atomicity, Consistency, Isolation, and Durability.

• Atomicity: All the operations (Insert, Update, Delete) in a transaction are considered as a single unit, that means either all the operations succeed or none of them, or you can say either all the operations inside a transaction are completed or rolled back.
• Consistency: It ensures that a transaction takes the database from one consistent state to another, in this way it maintains the integrity in database. Means, if transaction is completed successfully then all the required changes are made to the database are committed. If any error occurred in transaction then all the changes that have already been made to the database are rolled back automatically and the database will not be left in an inconsistent state.
• Isolation: It ensures that the operations of one transaction are isolated from the operations of other transactions, so that changes made by one transaction are not visible to other transactions until the first transaction commits.
• Durability: It ensures that once a transaction is committed, its changes are permanent and cannot be rolled back. Even if the database crashes after you commit a transaction, your changes will still be there when the database restarts.

Let’s understand the ACID properties with the help of an example.

As you can see here, we have a two sample tables named DimCustomer and DimCustomerLog.

DimCustomer table contains customer details while DimCustomerLog table captures the log details of DimCustomer table.

SELECT * FROM DimCustomer

SELECT * FROM DimCustomerLog

Atomicity: 

Example 1:

In below example there are one Insert and Update statements under transaction, here we are updating a YearlyIncome to 40,000 for customer 11001 in table DimCustomer and inserting a log details in table DimCustomerLog.

BEGIN TRANSACTION

UPDATE DimCustomer SET YearlyIncome =40000 WHERE CustomerKey =11001;

INSERT INTO DimCustomerLog 
SELECT CustomerKey, FirstName, MiddleName, LastName, MaritalStatus, BirthDate, YearlyIncome, 'Update', GetDate()
FROM DimCustomer WHERE customerKey =11001;

COMMIT TRANSACTION

Let’s execute the query and you can see it runs successfully without fail.

As query runs successfully, let’s check the records in table.

You can see, YearlyIncome is updated for customer 11001 also log is inserted in log table.

SELECT CustomerKey, FirstName, MiddleName, LastName, BirthDate, 
YearlyIncome FROM DimCustomer WHERE CustomerKey =11001;

SELECT * FROM DimCustomerLog WHERE CustomerKey =11001;

Example 2:

Now, this time we will produce an error deliberately during an insertion to fail the transaction then see what happens.

In update statement, we are updating a YearlyIncome to 40,000 while in insert statement, we have provided the CustomerKey value to ‘AZZ’ .  As CustomerKey value should be an integer so, it will return an error during insertion and fail the transaction.

BEGIN TRANSACTION

UPDATE DimCustomer SET YearlyIncome = 60000 WHERE CustomerKey =11001;

INSERT INTO DimCustomerLog
SELECT ‘AZZ’, FirstName, MiddleName, LastName, MaritalStatus, BirthDate, YearlyIncome, ‘Update’, GetDate()
FROM DimCustomer WHERE customerKey =11001;

COMMIT TRANSACTION

You can see, update statement runs successfully while insert statement returns an error.

Now, lets check the  records in tables. You can see, the changes are made by updated statement is rolled back. And yearly income for customer is still showing 40,000.

SELECT CustomerKey, FirstName, MiddleName, LastName, BirthDate, 
YearlyIncome FROM DimCustomer WHERE CustomerKey =11001;

SELECT * FROM DimCustomerLog WHERE CustomerKey =11001;

 

Which ensures automaticity in transaction, means either ensures that either all the operations inside a transaction are completed or rolled back.

Consistency: 

In above example 1, we have seen when transaction runs successfully then all the changes made by operations such as Update and Insert statements are committed permanently in database.

While in example 2, where we had produced an error deliberately during an insertion. We have seen that when an error occurred under transactions in Insert statement then changes that have already been made by Update statement are rolled back automatically which ensures the consistency in transaction.

Isolation:

It ensures that the operations of one transaction are isolated from the operations of other transactions, so that changes made by one transaction are not visible to other transactions until the first transaction commits.

Let’s open two query window separately, In first query window run a below update statement under transaction called it as session 1.

BEGIN TRANSACTION

UPDATE DimCustomer SET YearlyIncome = 60000 WHERE CustomerKey =11001;

 Note that, we will not commit or roll back the transaction.

Now, open the second Query window and run the below select statement called it as session2.

SELECT CustomerKey, FirstName, MiddleName, LastName, BirthDate, 

YearlyIncome FROM DimCustomer WHERE CustomerKey =11001;

Now, you will see that select statement does not return any result as the first transaction is updating a record for customer 11oo1 in a table DimCustomer in session1 and has not completed yet.

While the second transaction which trying to fetch the details for same customer from same table DimCustomer in session2 but unable to fetch the data due to isolation nature of transactions which means changes made by one transaction are not visible to other transactions until the first transaction is committed.

Now, let’s commit the first transaction in session 1. Just execute the below statement in session 1.

COMMIT TRANSACTION;

Now, as soon as you commit the transaction in session 1. You will see that select statement in session 2, immediately returns the result. Because, the first transaction has completed it’s processing.

SELECT CustomerKey, FirstName, MiddleName, LastName, BirthDate, 

YearlyIncome FROM DimCustomer WHERE CustomerKey =11001;

Durability:

It ensures that once a transaction is committed, its changes are permanent and cannot be rolled back.

As we have already seen in above examples once a transaction is committed changes are made permanently to database.

 

Also Read..

Dirty Reads in concurrent transactions

Lost update problem in concurrent transactions