In this article, we learn about ACID property in SQL Server.
TRANSACTION
- A transaction is any action that reads from or writes to a database.
- A transaction is a logical unit of work that must be entirely completed or entirely aborted; no intermediate states are acceptable.
- The ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. It is used to avoid errors that occur while transactions and to maintain data consistency.
- The ACID property ensures data integrity during a transaction. During transaction execution, data may be inconsistent. When the transaction is committed, the data should be consistent.
ATOMICITY
- Atomicity means either all operations of a transaction be completed or the transaction is aborted.
- If any of the SQL statements in the transaction fails, the entire transactions are rolled back to the original database or else if all the SQL statements are executed successfully then the transaction is successful.
- Example- If a transaction T1 has four SQL requests, all four requests completed otherwise, the entire transaction is aborted.
CONSISTENCY
- Consistency indicates the data consistent format. It refers to the correctness of data.
- If the transaction completed successfully, the data must be in a consistent state.
- If there is an error in a transaction, then the entire transaction is aborted.
ISOLATION
- In isolation, one transaction should not aware of another transaction getting executed.
- Isolation means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed.
- Example- If transaction T1 is being executed and is using the data item X that data item cannot be accessed by any other transaction until T1 ends.
- Isolation property is useful in a multiuser database environment because several users can access and updates the data at the same time.
DURABILITY
- Once a transaction is completed successfully, then it cannot be undone or lost, even in the event of a system failure.
SERIALIZABILITY
- Serializability ensures that the schedule for the concurrent execution of the transaction yields consistent results.
- This property is important in multiuser and distributed databases in which multiple transactions are likely to be executed concurrently.
- Example- Transaction T1, T2, and T3 executing at the same time. To properly carry out transactions, the database must schedule the concurrent execution of the transaction’s operations. The schedule of such multiple transaction operations must exhibit the property of serializability.
- A single user database system automatically ensures serializability and isolation of the database because only one transaction is executed at a time
The Atomicity, Consistency, Isolation, and Durability of transactions must be a single-user database.
If you are a newbie to database learning- SQL Server recommended is the following must-watch video: -
