[T-SQL] Database Transaction and ACID properties

Transaction:
A transaction is a sequence of operations performed as a single logical unit of work. This logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction.

1. Atomicity: Each transaction is said to be atomic if when one part of the transaction fails, the entire transaction fails and database state is left unchanged.
2. Consistency: If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction could be rolled back to the pre-transactional state - or if a DBMS allows fields of a record to act as references to another record, then consistency implies the DBMS must enforce referential integrity: by the time any transaction ends, each and every reference in the database must be valid.
3. Durability: When the DBMS's guarantee that once the user has been notified of a transaction's success the transaction will not be lost, the transaction's data changes will survive system failure, and that all integrity constraints have been satisfied, so the DBMS won't need to reverse the transaction.
4. Isolation: Refers to the requirement that other operations cannot access data that has been modified during a transaction that has not yet completed.