Isolation levels can be quite useful when retrieving data and inserting data to maintain data integrity. Isolation levels control the locking and row versioning behaviour between SQL statements issued by a connection to SQL server. It defines the degree to which one transaction must be isolated from resource or other modification made by other transactions.
There are 5 types of isolation levels
- Read uncommitted
- Read committed
- Repeatable Read
This allows you to read rows that have been modified by other transaction but not yet committed. This is the lowest isolation level. Read Uncommitted does not use shared locks and is not blocked by exclusive locks. This option has the same effect as setting a NOLOCK on all tables in a SELECT statement in a transaction.
This allows you to only read records that have been modified and committed by other transactions. This prevents dirty reads from happening. The SQL server default will still allow non-repeatable reads and phantom reads; this is where the data ban be changed by other transactions between individual statements within the current transaction.
Statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Shared locks are placed on all data read and are held until the transaction completes. Therefore, no transactions can modify any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. Phantom reads will occur if the current statement tries to read the new rows.
Statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
This is exactly what it sounds like, this takes a snapshot of the data at time of execution. Any modification made to the data after the start of the transaction is not visible to the current transaction.
When a database is being recovered, snapshot transactions do not request lock transactions when reading data and they do not block other transactions from writing data.
Considering Isolation which level to use
When considering which isolation level to use, you need to think in terms of concurrency side effects, such as dirty reads, phantom reads or non-repeatable reads. You also need to consider the balancing of data integrity against the overhead of the isolation level.
The below table shows the concurrency side effects allowed by the different isolation levels.
Transaction isolation levels control the following:
- Whether locks are taken when data is read, and what type of locks are requested.
- How long the read locks are held.
- Whether a read operation referencing rows modified by another transaction:
- Block until the exclusive lock on the row is freed.
- Retrieve the committed version of the row that existed at the time the statement or transaction started.
- Read the uncommitted data modification.
Regardless of any isolation level set on a transaction, the transaction will always get an exclusive lock on any data it modifies and holds the lock until the transaction completes. If you choose to use a lower isolation level such as read uncommitted to increase the amount of possible users that can access the data at the same time, this shall also increase the number of concurrency effects such as dirty reads or lost updates that users may encounter.
A higher isolation level such as serializable will reduce the amount on concurrency effects, however it shall require more system resources and increases the possibility that one transaction will block another.
FILESTREAM-enabled databases support the following transaction isolation levels.
Look out for my next post on using row versioning-based isolation levels!