ACID Properties in SQL
ACID (Atomicity, Consistency, Isolation, and Durability) are being used in SQL transaction. In this article, we will briefly discuss about these ACID properties with examples.
Author: Urvashi Saxena
Table of Content
Best-suited Data Analytics courses for you
Learn Data Analytics with these high-rated online courses
What is Transaction in Database?
Tasks are collectively referred to as a transaction. The smallest unit of processing that can never be subdivided further is a single task. Let us consider a simple example to understand the concept of a transaction. If you were sending Rs.1000 to a buddy from your bank account, the series of actions would go as follows:
- First, you need to read your account balance.
- Then subtract 1000rs from your account.
- After deducting the amount read the new balance.
- Now, read your buddy’s account balance.
- And, add the amount to his account balance, i.e. add 1000 to his balance.
- And finally, update his new account balance.
This set of steps is collectively known as Transaction. In a database management system, one has to write the above transaction in the following manner:
R(A) //read operationA = A - 1000W(A) //write operationR(B)B = B + 1000W(B)
The primary issue that can occur during a transaction is that it may fail before concluding all of the actions in the set. Power outages, system failures, and other factors may cause this. An inconsistent database could result from this major issue. Let’s say the third operation of the transaction fails, in which case the money would be taken out of your account but not given to your friend. When modifications are made to a database, the management of that data should continue to be integrated. The reason for this is that if the data’s integrity is compromised, the entire set of data will be disturbed and corrupted. As a result, the DBMS describes four properties known as the ACID properties in order to ensure the integrity of the data.
Must Read: Introduction to SQL
Must Read: SQL SELECT
What is ACID property?
To ensure that the database is in a consistent state both before and after the transaction, one needs to follow certain properties and those properties are known as ACID properties, where
- A – Atomicity
- C – Consistency
- I – Isolation
- D – Durability
In the next section of the blog, there will be a detailed explanation of each of the above properties.
Must Read: SQL Comparison and Arithmetic Operator
Must Read: Types of Keys in Database
Atomicity:
The concept of atomicity describes how the data stays atomic. In other words, if an action is performed on the data, it should either be executed, completed, or not executed at all. Additionally, it signifies that the process shouldn’t pause or run in segments. If an operation needs to be performed on the transaction, it should be carried out entirely and not in parts. It includes two operations in it and these are
- Commit: Changes are shown after a transaction commits.
- Abort: Changes to the database are invisible if a transaction aborts.
Let’s see an example of this property to understand its concept in detail. Consider there are two people Cacey and Jay and they have accounts A and B with 4000 and 2000 rupees respectively. Cacey wants to transfer 500 into Jay’s account, so after the transfer, the new balance in account A will be 3500, and in account, B will be 2500. So, the whole process of money transfer will occur in two operations i.e. first debit will happen in account A and then credit will happen in account B. Now suppose the first operation(debit) is executed successfully and account A balance comes to 3500 but the second operation(credit) fails due to any issue and account B’s balance remains the same, so it means it is not an atomic transaction but if both the transactions would have occurred successfully then it would be called as an atomic transaction.
Since the transaction failed after write(A), the database is in an inconsistent state. The transaction manager is responsible for making sure that the database is following the property of atomicity.
Must Read: What is the difference between SQL and MySQL?
Must Read: Difference between SQL and NoSQL
Consistency:
The idea behind consistency is that the value must constantly be maintained. The integrity of the data should be upheld in DBMS, which means that changes to the database should always be saved. When it comes to transactions, data integrity is crucial to ensuring that the database is consistent both before and after the transaction. Data must always be accurate.
Example: The total amount before and after the transaction must be maintained.
The total amount in accounts A and B before the transaction in the above example was 4000+2000=6000
The total amount in accounts A and B after the transaction should be 6000 only i.e. 3500+2500=6000
When both the amounts will be equal then the database will be in a consistent state. If they are not equal then the database will be considered in an inconsistent state. The application programmer is responsible for making sure that the database is following the property of consistency.
Must Read: SQL ORDER BY
Must Read: SQL WHERE
Isolation:
This characteristic guarantees that numerous transactions can take place simultaneously without causing the database state to become inconsistent. Transactions take place without interruption and independently. Changes made in one transaction are not visible to changes made in any other transaction until the change in question has been committed or written to memory, whichever comes first.
This property guarantees that the state created by concurrently running transactions will be the same as the state created by serially running them in some order. For example, let A = 200, B = 200
Assume two transactions T1 and T2
| T1 | T2 |
Read(A)A = A*10Write(A)Read(B)B = B - 20Write(B)
Read(A)Read(B)C = A + BWrite(C)
Assume T1 has been run through Read (B), and then T2 begins. Interleaving of processes occurs, as a result, causing T2 to read the right value of A but the wrong value of B and the sum calculated by T2 is:
(A + B = 2000 + 200 = 2200) that is not consistent as the sum at the end of the transaction should be T1: (A + B = 2000 + 180 = 2180). Due to the loss of 20 units, the database becomes inconsistent. Therefore, transactions must occur in isolation and modifications must only become apparent after being written to the main memory. The concurrency control manager is responsible for making sure that the database is following the property of isolation.
Must Read: Subqueries in SQL
Must Read: SQL CREATE TABLE
Durability:
Something is permanent if it is durable. Durability in DBMS refers to the guarantee that data remains in the database permanently once an operation has been successfully completed. Data stored in a database should be so perfectly durable that it can still function even in the event of a system failing, malfunctions, or crash. However, if it disappears, the recovery manager is now in charge of making sure the database is durable. Whenever changes are made in the database, you must use the COMMIT command to commit those changes and avoid the risk of losing that data.
Must Check: SQL Online Course and Certifications
Must Read: SQL COUNT
Conclusion
In this blog you came across what is a transaction in a database and what issues can occur while performing a transaction. This blog covers the ACID properties that give a means to guarantee the accuracy and consistency of a database so that each transaction is a collection of processes that functions as a single entity, yields consistent results, operates independently of other operations, and permanently stores any updates it makes.
This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio
Top Picks & New Arrivals
