ACID properties

The ACID properties of a transaction affect the constancy and concurrency of database systems. It's important to be aware of these properties and how different systems and options affect them.

ACID stands for Atomicity, Consistency, Isolation, Durability.

Atomicity - A transaction must be atomic.  It must be all or nothing.  In SQL Server, a data modification cannot partially succeed.

Consistency - A transaction must not leave the database in an invalid state.  Rules such as null-ability and constraints must be valid.  SQL Server will ensure consistency and integrity of the data before allowing the transaction to commit.  However, it can only work with the schema rules as defined.

Isolation - A transaction must be isolated from other transactions.  The result of a transaction should not be affected by other concurrent transactions.  SQL Servers default transaction isolation level is READ COMMITTED and uses a locking model to prevent one transaction from being affected by other concurrent transaction.  However, READ COMMITTED is not the strongest isolation level and can experience two well know phenomena (More on this in a later email.)

Durability - A committed transaction must not be lost and must be durable even in the event of a subsequent error, crash or power failure.  SQL Server achieves this using the transaction log.  In fact, when a transaction is committed, the data is not written to the data file, but only the log file.  Data changers are written to the data files either on checkpoint or by the lazy-writer.  SQL Server will re-do an necessary transactions during the crash recovery part of start-up.