Transaction Isolation Levels

TSQL - Transaction Isolation Levels

The concept of isolation levels was first introduced in under the name Degrees of Consistency. We know that commercial databases support different isolation levels to allow programmers to trade off consistency for a potential gain in performance.

The most important thing is to know that which appropriate isolation level is set for query to access the result from relational database by avoiding deadlocks, dirty reads, non-repeatable reads and Phantom read or poor performances.

Before the elaboration of Transaction Isolation levels, we will go through the some important topics which play the most important in isolation levels.

Transactions

A transaction is nothing but it is a sequence of database operations with the following properties which are normally known as ACID:

Atomic: Operations of a transaction are executed all-or nothing, and are never left “half-done”.

Consistency: Assume all database constraints are satisfied at the start of a transaction, they should remain satisfied at the end of the transaction.

Isolation: It determines how transaction integrity is visible to other users and systems.  Transactions must behave as if they were executed in complete isolation from each other.

Durability: If the DBMS crashes after a transaction commits, all effects of the transaction must remain in the database when DBMS comes back up.

SQL Transactions

A transaction is automatically started whenever a user executes an SQL statement and subsequent statements in the same session are executed as part of this transaction. SQL transactions must have two following commands -

COMMIT command commits the transaction effects are made final and visible to subsequent transactions

ROLLBACK command aborts the transaction effects are undone.

Apart of this, many DBMS support an AUTOCOMMIT feature, which automatically commits every single statement.

Read phenomena

The ANSI/ISO standard SQL 92 refers to three different read phenomena when Transaction 1 reads data that Transaction 2 might have changed. 

·                     Dirty reads - A dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed. Dirty reads work similarly to non-repeatable reads; however, the second transaction would not need to be committed for the first query to return a different result. The only thing that may be prevented in the READ UNCOMMITTED isolation level is updates appearing out of order in the results; that is, earlier updates will always appear in a result set before later updates. 

·                     Non-repeatable reads  - A 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 multisession 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 reads - A 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.

Transaction Isolation Levels

A lower isolation level or weaker Isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter.

Conversely, a higher isolation level or stronger isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another.

We can change the level of isolation that a particular connection is operating in by using the SET TRANSACTION ISOLATION LEVEL command. 

Transaction Isolation levels are responsible to controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. Transaction Isolation has the following isolation levels which behave differently with dirty reads, non-repeatable reads andPhantom reads-

Weaker Isolation Levels

Read Uncommitted

Read Committed – This is the default isolation level for all SQL Server databases.

Repeatable Read

Stronger Isolation Levels

Serializable

Snapshot

Isolation level	Dirty Reads	Non-repeatable Reads	Phantoms
Read uncommitted	Possible	Possible	Possible
Read committed	Impossible	Possible	Possible
Repeatable Read	Impossible	Impossible	Possible
Serializable	Impossible	Impossible	Impossible
Snapshot	Impossible	Impossible	Impossible

Note: Concurrency issues such as dirty reads and phantom reads, as their names suggest, apply to read operations, not write.

Read Uncommitted: This is a lower or weaker isolation level. If in the same time, two transactions request are running on the database. One transaction request can read data modified within another transaction but still not committed. This is because database engine does not apply shared locks when Read Uncommitted is specified, making this the least restrictive of the isolation levels.

When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. It’s also possible for data to be modified by another transaction between issuing statements within the current transaction, resulting in non-repeatable reads or phantom reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. 

This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction.

See the explanation of Read Uncommitted functionality here.

Read committed: This is also a lower or weaker isolation level. If in the same time, two transactions request are running on the database. One transaction request cannot read data modified within another transaction but still not committed. So, dirty data will not appear in the result set.  Data can be changed by other transactions between individual statements within the current transaction, resulting in non-repeatable reads or phantom data.

The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled. Read Committed is the default isolation level for all SQL Server databases.

See the explanation of Read Committed functionality here.

Repeatable Read: This is also support a lower or weaker isolation level. If in the same time, two transactions request are running on the database. One transaction request cannot read data modified within another transaction but still not committed, thus preventing dirty reads.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction.

However, if another transaction inserts new rows that match the search condition in the current transaction, in between the current transaction accessing the same data twice, phantom rows can appear in the second read.

See the explanation of Repeatable Read here.

Serializable: This isolation supports to a higher or stronger isolation level. If in the same time, two transactions request are running on the database. One transaction request cannot read data modified within another transaction but still not committed. No other transaction can modify data being read by the current transaction until it completes, and no other transaction can insert new rows that would match the search condition in the current transaction until it completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. 

As a result, the Serializable isolation level prevents dirty reads, non-repeatable reads, and phantom reads. However, it can have the biggest impact on performance, compared to the other isolation levels.

See the explanation of Serializable here.

Snapshot: This isolation supports to a higher or stronger isolation level. If in the same time, two transactions request are running on the database. One transaction request cannot read data modified within another transaction but still not committed. If another transaction modifies data after the start of the first transaction, the data is not visible to the first transaction.

The first transaction works with a snapshot of the data as it existed at the beginning of that transaction. Snapshot transactions do not request locks when reading data, nor do they block other transactions from writing data. In addition, other transactions writing data do not block the current transaction for reading data. As with the Serializable isolation level, the Snapshot level prevents dirty reads, non-repeatable reads and phantom reads.