ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved. Atomicity is an all-or-none proposition. Consistency guarantees that a transaction never leaves your database in a half-finished state. Isolation keeps transactions separated from each other until they’re finished. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
Types of Isolation Levels
The ISO standard defines the following isolation levels, all of which are supported by the SQL Server Database Engine: 1. Read uncommitted : (the lowest level where transactions are isolated only enough to ensure that physically corrupt data is not read) 2. Read committed : (Database Engine default level) 3. Repeatable read 4. Serializable : the highest level, where transactions are completely isolated from one another) 5. Snapshot : The snapshot isolation level uses row versioning to provide transaction-level read consistency. Read operations acquire no page or row locks; only SCH-S table locks are acquired. When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. You can only use Snapshot isolation against a database when the ALLOW_SNAPSHOT_ISOLATION database option is set ON. By default, this option is set OFF for user databases.Read phenomena
The ANSI/ISO standard SQL 92 refers to three different read phenomena. Dirty read (uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed. Non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. Non-repeatable reads phenomenon may occur in a lock-based concurrency control method when read locks are not acquired when performing a SELECT, or when the acquired locks on affected rows are released as soon as the SELECT operation is performed. Under the multi-version concurrency control method, non-repeatable reads may occur when the requirement that a transaction affected by a commit conflict must roll back is relaxed. Phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first. This can occur when range locks are not acquired on performing a SELECT ... WHERE operation. The phantom reads anomaly is a special case of Non-repeatable reads when Transaction 1 repeats a ranged SELECT ... WHERE query and, between both operations, Transaction 2 creates (i.e. INSERT) new rows (in the target table) which fulfill that WHERE clause.Isolation Levels vs. Read Phenomena
| Locks | Isolation Levels | Dirty reads | Non-repeatable reads | Phantoms |
|---|---|---|---|---|
| Nolock | Read Uncommitted | May Occur | May Occur | May Occur |
| Shared Locks | Read Committed | -- | May Occur | May Occur |
| Exclusive Range | Repeatable Read | -- | -- | May Occur |
| Exclusive lock on entire table | Serializable | -- | -- | -- |
