ACID property in SQL
February 2, 2023 2023-02-04 11:54ACID property in SQL
Subhajit Transaction
A transaction is a set of SQL command that are treated as a single unit. A successful transaction must pass the ACID test. They are Atomicity, Consistency, Isolation and Durability.
ACID Property Explanation
Here PRODUCT is an inventory table, which contain information about the product and the quantity available. PRODUCTSALES is the stock table. Whenever we make an entry in the PRODUCT table an entry should be made in the PRODUCTSALES table as well.
Here in the PRODUCT table we are checking that if the stock is available, that is if there is value in the QtyAvailable column. If there is the value in that column, then the quantity will be deducted from the QtyAvailable column of the PRODUCT table and same quantity will be added into the QuantitySold column of the PRODUCTSALE table along with the ProductId.
Now here in the code below, in the transaction, we are updating the QtyAvailable column of he PRODUCT table and inserting the Sell quantity in the QuantitySold PRODUCTSALE table
In the query below, we can see that both the Update and the Insert query are wrapped inside the transaction statement.
CREATE PROCEDURE SP_MANAGESALES
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
UPDATE PRODUCT SET QtyAvailabe = (QtyAvailabe - 10)
WHERE ProductId = 1
INSERT INTO PRODUCTSALE VALUES (1,10)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
Atomicity – The entire transaction takes place at once or does not happen at all. So, here the atomicity states that both the transaction will happen at once, or will not take place at all. That is if an error occur while updating the PRODUCT table then the Insert will not happen in the PRODUCTSALE table and the query will get rolled back to its original state.
Consistency – The database must be consistent before and after the transaction. This means that in the above example if in the PRODUCT table the stock gets subtracted from the QtyAvailable column, then the same stock will be added into the QuantitySold column of the PRODUCTSALE table. So if we sell 10 Kinley water bottle, then quantity of 10 kinley bottles will get subtracted from the QtyAvailable column of the PRODUCT table and the same value will get inserted in the QuantitySold column of the PRODUCTSALE table along with the ProductId of Kinley in the PRODUCT table. And if an error occur while inserting data in the PRODUCTSALE table then the transaction will undo the update in the PRODUCT table. Otherwise we cant keep the tract of those 10 Kinley water bottles. That’s how the transaction will be consistent.
Isolation – Multiple transactions occur independently without interference. This prevents a transaction from making changes to data based on uncommitted information.
Suppose we have started a transaction to update the QtyAvailable column of the PRODUCT table as follows.
BEGIN TRANSACTION
UPDATE PRODUCT SET QtyAvailable = 80 WHERE ProductId = 1
Then it will update the RODUCT table as follows
Now suppose another developer tries to run an update query against the same PRODUCT table or the same product that is kinley bottles as follows while the previous transaction is still under progress,
UPDATE PRODUCT SET QtyAvailable = 70 WHERE ProductId = 1
Then we will not be able to execute that transaction as follows, since the previuos transaction is under process,
But if another user tries to updated the another product o the PRODUCT table that is the Bisleri bottles as follows,
UPDATE PRODUCT SET QtyAvailable = 10 WHERE ProductId = 2
Then we will be able to execute it, as this product is not getting updated by the update query in the transaction
So here the transaction for the kinley bottle will not be getting affected by another transaction, by another user, this means that each and every transaction in SQL is isolated, one transaction cannot interfere the other transaction
Durability – Ones a change is made, it is permanent. This that if a system error or a power failure occurs before a set of commands complete, then those commands are undone and that the data is restored to its original state once the system begins running again.
Please comment and share if you like this post and tell us about how we can enhance our posts in dotnetcoaching. Thanks.
