How to achieve ACID properties with Example in Sql Server

Welcome to Quickpickdeal Technologies. In this session, we will learn about the transaction ACID test. Before continuing with the session, I strongly recommend reading the post below.

• What is transaction in sql server with example

We know that a transaction is a group of database commands that are treated as a single unit. A successful transaction must pass the ACID test – it must be Atomic, Consistent, Isolated, and Durable. Let’s see what we mean by each one of these.

What is the Atomicity of a transaction?

A transaction must be atomic, meaning all statements in the transaction either completed successfully or they were all rolled back. That means the set of operations in a transaction is either finished or not, but in any case not left half done.

Let’s understand with an example. I have two tables here: TblProducts and TblProductSale.

Table Script

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[TblProducts]    Script Date: 10/20/2020 9:52:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProducts](
	[Id] [int] NOT NULL,
	[ProductName] [nvarchar](100) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Price] [float] NULL,
    CONSTRAINT [PK_TblProducts] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[TblProductSale]    Script Date: 10/20/2020 9:52:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblProductSale](
	[Id] [int] NOT NULL,
	[ProductId] [int] NULL,
	[QuantitySold] [int] NULL,
	[Datetime] [datetime] NULL,
    CONSTRAINT [PK_TblProductSale] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (1, N'Books', 0, 100)
GO
INSERT [dbo].[TblProducts] ([Id], [ProductName], [Quantity], [Price]) VALUES (2, N'Mobile Phone', 100, 15000)
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (1, 1, 10, CAST(N'2020-10-16T17:16:57.953' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (2, 2, 5, CAST(N'2020-10-16T17:16:57.953' AS DateTime))
GO
INSERT [dbo].[TblProductSale] ([Id], [ProductId], [QuantitySold], [Datetime]) VALUES (3, 1, 10, CAST(N'2020-10-16T17:32:44.040' AS DateTime))
GO

TblProducts is like an inventory table that contains information about the products and the quantity available. Now, whenever we sell a product, an entry should also be made into the TblProductSale table.

So when we sell a product, two things should happen. First, we should check the quantity available. Let’s say, for example, I’m selling 10 Books.

The first thing that I have to do here is to check what’s the quantity available for Books? And then from that quantity available, I will have to deduct the 10 Books that I am selling.

So we have to update the quantity available to 100-10=90. And then we also have to make an entry into TblProductSale.

Basically when we sell a product, two things are happening: updating TblProducts and inserting a row into TblProductSale. So that’s what the transaction below is doing here.

Create Procedure spDoProductSale
as
Begin
Begin Try
Begin Transaction
Update TblProducts set Quantity = (Quantity - 10)
where Id = 1

Insert into TblProductSale values(3, 1, 10,GETDATE())
Commit Transaction
End Try
Begin Catch
Rollback Transaction
End Catch
End

The first statement updates TblProducts, and the next statement inserts a row into the TblProductSale table. And these two statements are wrapped inside a transaction.

So the Atomicity of a transaction states that both of these statements should be treated as one unit. Either both of them succeed, or if one of them fails, the data modified by the other statements should be undone and rolled back. So that the database is in a state that is similar to before we began the transaction.

So either both of them should succeed or none of them. That’s the Atomicity of a transaction.

What is the consistency of a transaction?

The transaction should also be consistent, meaning all data affected by the transaction is left in a logically consistent state.

For example, if the quantity available is decremented in TblProducts, there must be a corresponding entry in TblProductSale. The inventory cannot simply disappear.

For instance, if 10 Books are sold, the first update statement reduces the quantity available for Books. If an error occurs before executing the insert statement in the TblProductSale table, the transaction should undo the first statement and restore the quantity back to 100. Otherwise, we cannot properly account for those 10 Books. This is what consistency entails: ensuring all data in the transaction is left in a logically consistent state."

Isolation or Concurrency Control

The transaction should also be isolated. This means that the transaction must affect data without interfering with other concurrent transactions or being interfered with by them.

If there are two transactions, transaction A and transaction B, then transaction A shouldn’t interfere with transaction B, and vice versa. They should each work as a single unit of work and not interfere with each other. Let’s see how transactions achieve isolation.

For example, let’s say I want to update TblProducts, setting quantity available to 150 where product ID equals 1. Now, let’s make this update statement part of the transaction.

Begin Transaction
update TblProducts set Quantity=150 where id=1

To begin the transaction, I execute the above query. Now, a transaction is being processed on the TblProducts table. We haven’t committed this transaction or rolled it back, so this transaction is still being processed.

Now, let’s say there is another person who is connected to the SQL server and is also trying to update the same table. 

So, they issue another transaction or, let’s just say, they are trying to update the quantity. Before updating, they want to select the data and view records.

So, they issue a select statement. Now, when they execute the query 'select * from TblProducts', the query executes, but they will never get access to that table.

Why? Because there is a transaction that is still being processed. It’s not completed yet. That’s why other users, by default, will not be able to see the data. It’s just waiting for the transaction to complete.

On the other hand, let’s cancel that. And when I say 'select * from TblProducts where id=2', I’m selecting a row where the product ID is equal to 2. Look at this. The moment I execute that, I immediately get that row.

But then when I try to get the row, Id equal to 1 that is being updated by the other transaction. In the other connection window, look at this. It says executing the query.

So why am I not able to see that? Because that row is being locked by the database since another transaction is currently being processed. So one transaction is not interfering with other transactions.

So transactions must be isolated, and how do transactions achieve isolation? Basically, they use the locking mechanism. Isolation prevents transactions from making changes to data based on uncommitted information. Most databases use locking to maintain transaction isolation.

What is Transaction Durability?

A transaction should be durable, and this is pretty simple to understand. Once a change is made to a database, it’s permanent.

Let’s assume that we have a banking system database. So, for example, we are executing a long-running transaction, and let’s say half of the data is modified, and then all of a sudden there is a power failure.

When that happens, when the power comes back, the database should be in such a position that it has to roll back and undo the changes that it has already done, leaving the database in a consistent and durable state. That’s what durable means.