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
Let’s understand the ACID properties in SQL Server with an example.
We are going to use the following two tables
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 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
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.
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.
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.
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.
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.