How to achieve ACID properties with Example in Sql Server

welcome to Appsloveworld technologies. In this session, we learn about the transaction ACID test before continuing with the session. I strongly recommend reading the below post.

• 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 – A Transaction need 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 think 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 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 happening. Update TblProducts and insert a row into TblProductSale. So that’s what below transaction 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. If one of them fails, the data that’s modified by the other states should be undone and rolled back. So that the database is in a state that is similar to before we have begin the transaction.

So either both of them should succeed or none of them, that’s what is the Atomicity of a transaction.

what is the consistency of a transaction?

The transaction should also be consistent, which means all data attached to the transaction is left in a logically consistent state.

For example, if quantity available numbers are decrement from TblProducts, then there has to be an associated entry into the TblProductSale. The inventory can’t just disappear.

So, for example, if I am selling 10 Books, the first update statements reduce that quantity from the Book quantity available.

And then let’s say when we are about to execute the insert statement in the TblProductSale table, the power has gone or something has happened in those circumstances. You know, a transaction should undo the first statement and put it back to 100.

Otherwise, we cannot account for those 10 Books. So that is what consistency says all data in the transaction is left in a logically consistent state.

Isolation or concurrency control

The transaction should also be isolated. What we mean by this, the transaction must affect data without interfering with other concurrent transactions or being interfered with by them.

so if there are two transactions, transaction A and transaction B. Then transaction A shouldn’t interfere with transaction B and similarly Transaction B shouldn’t interfere with transaction A.

They both of them,should work as a single unit of work and they shouldn’t interfere with each other.  let’s see how the transaction achieves isolation.

Now, let’s say I want to update the TblProducts, let’s say quantity available=150 where product ID =1. And 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 when I execute the above query. Now A Transaction is now 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 connected to the SQL server and he is also trying to update the same table.

So he issued let’s say, another transaction, or let’s just say he is trying to update the quantity.

Now before updating he wants to select the data and see records.

So he is issuing a select statement. So now when I say select * from TblProducts when I execute query, it is executing query but it 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 because another transaction is currently being processed. So one transaction is not interfering with other transactions.

So the transactions must be isolated. And how do the transactions achieve isolation?

You know, basically using the locking mechanism, so isolation actually 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 and  So let’s say, 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

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

The post How to achieve ACID properties with Example in Sql Server appeared first on Software Development | Programming Tutorials.