What is ACID Property and Database Transactions

Transaction is a sequence of steps that perform a logical unit of work. They exhibit ACID properties.

           ACID property stands for Atomicity, Consistency, Isolation and Durability.

Atomicity – either all the steps in the transaction must succeed or none of them should be performed.
Consistency – when the transaction is complete, data must be in a consistent state.
Isolation – changes made by a transaction must be isolated from other concurrent
transactions. No transaction affects other transaction.
Durability – when the transaction is complete, the changes must be made permanent
in the database and survive even system failures.

States of a transaction: 

A transaction may have in any of the following state.

  • Transaction StatesActive − When the transaction is being executed. This is the initial state of every transaction.
  • Partially Committed − When a transaction executes its final operation.
  • Failed − Any check from database recovery system has been failed transaction is called failed transaction and no longer proceed.
  • Aborted −Any checks failed and the transaction has reached a failed state, then the recovery manager rolls back all its write operations on the database to bring the database back to its original state where it was prior to the execution of the transaction. called aborted transaction. there there is only two option either to Restart the transaction, or Kill the transaction.
  • Committed − Transaction executes all its operations successfully, and effect now permanently established to the database system.

Transaction Log : Every transaction is recorded in a transaction log to maintain database consistency and to aid in transaction recovery. When changes are made to data in SQL Server, the changes are recorded in the transaction log on disk as they are executed and then written to the database.