Welcome to Appsloveworld Technology. In this session we will learn more about testing transactional ACIDs before continuing the session. I strongly advise you to read the following article.
We know that a transaction is a group of database commands that are treated as a whole. A business has to succeed.
ACID test – The transaction must be atomic, consistent, isolated and permanent. Let’s see what we mean by each of them.
What is the nuclear agreement?
The transaction must be atomic, i.e. all participants in the transaction are either completed or all reports, i.e. the whole transaction is either completed or not completed, but under no circumstances half completed.
Let’s take a case study. I’ve got two tables here. TblProducts and TblProductSale.
Table Scenario
USAGE [Test Database]
GO
/****** Subject : Table [dbo]. Date of the scenario : 10/20/2020 9:52:33 ******/
ANSI_ZERO SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblProducts](
[Id] [int] NOT ZERO,
[product name] [nvarchar](100) NOT ZERO,
[quantity] [int] NOT ZERO,
[price] [margin] ZERO,
CONSTRUCTION [PK_TblProducts] APPLICABLE
(
[Id] ASC
)
) ON [PRIMARY]
GO
/****** Object : Table [dbo]. Date of the scenario : 10/20/2020 9:52:33 ******/
ANSI_ZERO SETTINGS ON
GO
QUOTED_IDENTIFIER SETTINGS ON
GO
CREATE TABLE [dbo].TblP Product Sales](
[Id] [int] NOT NULL,
[ProductId] [int] NULL,
[Quantity Sold] [int] NULL,
[Datetime] NULL,
CONSTRUCTION [PK_TblP Product Sales] APPLICABLE
(
[Id] ASC
)
) SETTINGS ON [PRIMARY]
YEAR
[dbo].[TblProducts] ([Id], [product name], [quantity], [price]) Price (1, N Books, 0, 100)
GO
INSERT [dbo]. [TblProducts] ([Id], [product name], [quantity], [price]) Price (2, N Mobile phone, 100, 15000)
GO
INSERT [dbo].TblP Product Sales]. ([Id], [ProductId], [QuantitySold], [DateTime]) Price (1, 1, 10, CAST(N’2020-10-16T17:16:57.953′ AS DateTime).
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (2, 2, 5, CAST(N’2020-10-16T17:16:57.953′ AS DateTime).
GO
INSERT [dbo] [TblProductSale] ([Id], [ProductId], [QuantitySold], [DateTime]) Price (3, 1, 10, CAST(N’2020-10-16T17:32:44.040′ AS DateTime).
GO TO
TblProducts is comparable to an inventory table with information about products and available quantities. Now every time we sell a product, we have to make an entry in the TblProductSale sales table.
So if we sell one product, we think we should sell two first, we should check the number of products available. Suppose I sell 10 pounds, for example.
The first thing I have to do here is see how many books are available. And then I have to deduct the ten pounds I’m selling from that amount.
So we need to update the available number to 100-10=90. And then we also have to make a mention in TblProductSale.
In general, two things happen when we sell a product. Update TblProducts and add the chain to TblProductSale. This is what the agreement described below does here.
spDoP productSales procedure as Create StartTransaction StartRecord TblUpdate products Quantity = (Quantity – 10) with ID = 1
Insert the values in TblProductSale(3, 1, 10,GETDATE()).
Mandatory transaction
End of interception
Start of interception
Withdrawal of transaction
End of interception
End of interception
The first time the TblProducts is updated and the next time a line is inserted in the TblProductSale table. And these two statements are combined into a single agreement.
The nuclear agreement therefore provides that these two declarations are to be considered as a whole. It’ll all work out. If one of these data fails, the data amended by other States must be deleted and taken over. The database is therefore in the same state as before the start of the transaction.
So either both have to succeed or the nuclear deal is neither.
What is the order of the transactions?
The transaction must also be consistent, i.e. all data linked to the transaction must remain in a logically consistent state.
If, for example, the number of units available is reduced by TblProducts, there must be a corresponding entry in TblProductSale. The inventory can’t just disappear.
For example, if I sell 10 books, at the first update I will be asked to reduce this number compared to the number of books available.
And then, let’s say, when we do the TblProductSale insertion statement, the power is cut off or something happened under those circumstances. You know the deal was to cancel the first request and put it back to 100.
Otherwise we can’t count the 10 pounds. This means a logical order if all data in a transaction remain in a logical order.
Insulation or parallel control
The company also needs to be isolated. By this we mean that a transaction should influence the data without disrupting or hindering other parallel transactions.
So if there are two agreements, then agreement A and agreement B. Agreement A may not conflict with agreement B, and agreement B may not conflict with agreement A.
The two must function as one and must not interfere with each other. Let’s see how this agreement leads to isolation.
Let’s say I want to update TblProducts, that the number is available=150, where the product ID =1. Let’s make this update announcement part of the agreement now.
Start of transaction
Update of TblP product sets Quantity=150, where id=1
How can I start a transaction when I execute the above query The transaction will now be processed in the TblProducts table. We did not execute this transaction and did not reverse it, so this transaction is still being processed.
Now suppose another person has logged on to the SQL server and is also trying to update the same table.
So he’s made, let’s say, another deal, or, let’s say, he’s trying to update the whole thing.
He now wants to select the data and view the records before updating them.
So he’s making a selective statement. So now, when I say select * in TblProducts, when I launch a search, it is executed, but the table is never consulted.
What for? Because there’s still a transaction going on. It’s not over yet. Other users cannot see the default data, they simply wait for the transaction to complete.
On the other hand, we have to cancel it. And when I say select * from TblProducts, where id=2. I select the line where the product ID is 2. Take a look at this. As soon as I execute, I get this row immediately.
But if I try to get a string, the identifier is 1, which is updated by another transaction. Take a look at this in another login window. It says here that the application is closed.
Then why can’t I see him? Because this line is locked by the database because another transaction is being processed. One transaction does not interfere with other transactions.
Therefore, the work must be insulated. And how do agreements lead to isolation?
As you know, the closing mechanism is the most commonly used, so segregation prevents transactions based on secret information from being changed.
What is the lifespan of transactions?
The agreement must be permanent, and it is quite easy to understand. As soon as changes are made to the database, it becomes permanent.
Suppose we have a database in the banking system, we do a long transaction, half the data is changed and suddenly there is a power failure.
When the power returns, the database must therefore be in that position, cancelling the changes already made and leaving the database in a consistent and permanent state.
That’s what longevity means.
Related Tags:
acid properties in sql w3schools,isolation in sql,acid properties in sql javatpoint,sql server transactions acid,acid t sql,microsoft sql server atomicity,base database,atomic in sql server,acid principles in sql by c# corner,sql server real time scenarios,data consistency in sql,consistency sql example,initial state of transactions is,cap properties in nosql,rdbms primer,define domain constraint.,acid normalization and transactions,atomic consistency,acid properties in big data,the most important property of a database,ssis acid,acid properties in ssis,durability in sql,serialized transaction sql,snapshot isolation update conflict,isolationlevel serializable (4),serializable in ssis,isolation in dbms,transaction states in dbms,properties of transaction in dbms,serializability in dbms,database acid pdf,explain acid properties of transaction with suitable example,acid properties in sql with examples,acid properties in dbms with examples,acid properties explain with an example,list the acid properties. explain the usefulness of each,isolation levels in sql server