ACID Properties And SQL Server Transactions

ACID Properties:

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.

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.

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 notbe readable by any other transaction

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.

SQL Server Transactions:

There are two types of transactions in SQL Server that are differentiated only by the way they are created: implicit and explicit. Implicit transactions are used automatically by SQL Server to guarantee the

ACID properties of Single commands. For example, if you wrote an update statement that modified 10 rows, SQL Server would run it as an implicit transaction so that the ACID properties would apply, and all 10 rows would be updated or none of them would be updated or none of them would.  Explicit transactions are started by using the BEGIN TRANSACTION T-SQL command and are stopped by using the COMMIT TRANSACTION or ROLLBACK TRANSACTION commands. Committing a transaction effectively means making the changes within the transaction permanent, whereas rolling back a transaction means undoing all the changes that were made within the transaction.