ACID Properties

ACID Properties

 DATABASE TRANSACTIONS

A transaction is a unit of work in a database that typically contains several commands that read from and write to the database. The most well-known feature of a transaction is that it must complete all of the commands in their entirety or none of them. This feature, called atomicity, is just one of four properties defined in the early days of database theory as requirements for a database transaction, collectively known as ACID properties.

Transaction:

Set of T-SQL statements which have some dependencies, whenever changes happened this changes should effect on all dependencies of data.  Transaction should process all ACID properties.  Then only changes move from ldf to mdf files.

 ACID Properties

The four required properties of a database transaction are atomicity, consistency, isolation, and durability.

 Atomicity

Atomicity means that all the effects of the transaction must complete successfully or the changes are rolled back. A classic example of an atomic transaction is a withdrawal from an ATM machine; the machine must both dispense the cash and debit your bank account. Either of those actions completing independently would cause a problem for either you or the bank.

(Atomicity is a feature of databases systems dictating where a transaction must be all-or-nothing. That is, the transaction must either fully happen, or not happen at all. It must not complete partially.)

(SQL Server should execute all statements inside transaction at once.  There is no partial execution of transactions.  Whatever statement given has to be executed full or none of the statements has to be executed.)

 Consistency

The consistency requirement ensures that the transaction cannot break the integrity rules of the database; it must leave the database in a consistent state. For example, your system might require that stock levels cannot be a negative value, a spare part cannot exist without a parent object, or the data in a sex field must be male or female. In order to be consistent, a transaction must not break any of the constraints or rules defined for the data.

(Whenever any change (modification) happened on parent table it automatically reflect on dependent child table.)

 Isolation

Isolation refers to keeping the changes of incomplete transactions running at the same time separate from one another. Each transaction must be entirely self-contained, and changes it makes must not be readable by any other transaction, although SQL Server does allow you to control the degree of isolation in order to find a balance between business and performance requirements.

(Isolation means independent. One transaction do not depend on other transaction. Users able to access multiple transactions at a time.)

 Durability

Once a transaction is committed, it must persist even if there is a system failure — that is, it must be durable. In SQL Server, the information needed to replay changes made in a transaction is written to the transaction log before the transaction is considered to be committed

(Once any transaction is updated it should be a permanent change in .mdf file.)