ACID Properties in SQL Server

ACID Properties in SQL Server

In this article, I will discuss the ACID Properties in SQL Server with an example. A transaction in SQL server is a group of SQL statements that are treated as a single unit and they are executed by applying the principle of do everything or do nothing and a successful transaction must pass the ACID test.

In the context of transaction processing, the acronym ACID refers to the four key properties of a transaction, such as

  1. Atomicity
  2. Consistency
  3. Isolation
  4. Durability.
Let’s understand the ACID properties in SQL Server with an example.

We are going to use the following two tables

ACID Properties in SQL Server

Please use below SQL scripts to create and populate the Product and ProductSales table with the test data.

-- Create the Product Table
CREATE TABLE Product
(
  ProductID INT PRIMARY KEY, 
  Name VARCHAR(40), 
  Price INT,
  Quantity INT
 )
 GO

 -- Populate the Product Table with some test data
 INSERT INTO Product VALUES(101, 'Laptop', 15000, 100)
 INSERT INTO Product VALUES(102, 'Desktop', 20000, 150)
 INSERT INTO Product VALUES(103, 'Mobile', 3000, 200)
 INSERT INTO Product VALUES(104, 'Tablet', 4000, 250)

 -- Create the ProductSales table
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
) 
GO

-- Populate the ProductSales table with some test data
INSERT INTO ProductSales VALUES(1, 101, 10)
INSERT INTO ProductSales VALUES(2, 102, 15)
INSERT INTO ProductSales VALUES(3, 103, 30)
INSERT INTO ProductSales VALUES(4, 104, 35)
GO

Let us discuss each of these properties one by one with the example

Atomicity

Atomicity ensures that either all the operations (insert, update, delete) inside a transaction are completed successfully or all of them are rolled back. That means the task that the set of operations (Insert, Update and Delete) represents is either accomplished or not, but in any case, not left half-done.

For example, in the spSellProduct stored procedure, both the UPDATE and INSERT statements should succeed. If the Update statement succeeds and the Insert statement fails, the database should undo the change made by the UPDATE statement, by rolling it back. In short, the transaction should be ATOMIC.

CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN
  -- First we need to check the stock available for the product we want to sell
  DECLARE @StockAvailable INT

  SELECT	@StockAvailable = Quantity
  FROM	Product 
  WHERE	ProductId = @ProductId

  -- We need to throw an error to the calling application 
  -- if the stock is less than the quantity we want to sell
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('Enough Stock is not available',16,1)
  END
  -- If enough stock is available
  ELSE
  BEGIN
    BEGIN TRY
      -- We need to start the transaction
      BEGIN TRANSACTION

      -- First we need to reduce the quantity available
      UPDATE	Product SET 
          Quantity = (Quantity - @QuantityToSell)
      WHERE	ProductID = @ProductID

      -- Calculate MAX ProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT	@MaxProductSalesId = CASE 
          WHEN	MAX(ProductSalesId) IS NULL THEN 0 
          ELSE	MAX(ProductSalesId) 
          END 
      FROM	ProductSales

      -- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- We need to insert the quantity sold into the ProductSales table
      INSERT INTO ProductSales(ProductSalesId, ProductId, QuantitySold)
      VALUES(@MaxProductSalesId, @ProductId, @QuantityToSell)

      -- Finally Commit the transaction
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
    END CATCH
  End
END
Consistency

The consistency property in SQL server ensures that the database data is in a consistent state before the transaction and also left in a consistent state after the transaction. If the transaction violates the rules it must be rolled back.

For example, if stocks available are decremented from Product table then there has to be a related entry in the ProductSales table.

In our example, say, the transaction has updated the quantity available in the product table, and suddenly there is a system failure (right before the insertion into the ProductSales table or in the middle). In this situation the system will roll back the updates Otherwise, we can’t trace out the stock information.

Isolation: 

The isolation property in SQL server ensures that the intermediate state of a transaction is invisible to other transactions. This prevents transactions from making changes to data based on the uncommitted information. Data modifications made by one transaction must be isolated from the data modifications made by all other transactions. Most databases use locking to maintain transaction isolation.

Let us see an example for better understanding

To understand Isolation, we are going to use two separate instances

First Instance: we started the transaction and updating the record in the Product table but we haven’t committed or rolled back the transaction.

Second Instance: We are using the Select statement to select the records present in the Product table.

ACID Properties in SQL Server

As you can see from the above screenshot, the select statement is not returning any information because we can’t access the intermediate state of a transaction by default. In a later article, we will discuss how to see the intermediate state of a transaction in SQL Server.

Let execute the Rollback transaction. This will immediately show the result of the Select statement because the lock is released from the Product table as shown in the below image.

ACID Properties in SQL Server

Durable: 

Once the transaction is successfully completed, then the changes it has made to the database will be permanent. Even if there is a system failure or power failure or any abnormal changes also, it will safeguard the committed data.

The acronym ACID was created by Andreas Reuter and Theo Härder in the year 1983, however, Jim Gray had already defined these properties in the late 1970s. Most of the popular databases such as SQL Server, Oracle, MySQL, Postgre SQL follows the ACID properties by default.

In the next article, I will discuss Partial RollBack in SQL Server using Save Point.

SUMMARY

In this article, I try to explain the ACID Properties in SQL Server step by step with an example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *