From the lowest level of isolation to the highest, they are…

  • read uncommitted
  • read committed
  • repeatable read
  • snapshot
  • serializable

Read uncommitted is the lowest level and only keeps transactions isolated enough to ensure that they aren’t reading physically corrupt data.They will allow transactions to read data that has been modified by another transaction, but not fully committed. Transactions with this isolation level don’t issue locks on the data that they have read, nor do they get blocked by exclusive locks issued by other transactions.They will allow dirty reads, nonrepeatable reads and phantom reads.

Read committed will not allow reading data that has been modified by another transaction but not yet committed. This prevents dirty reads from occurring. However, nonrepeatable and phantom reads can still occur. This is the SQL server database engine’s default level of transaction isolation. 

Setting the isolation level to repeatable read means that transactions cannot read data that has been modified but not committed by another transaction and it also locks data previously read from being modified from other transactions as well.This prevents dirty reads and nonrepeatable reads, but will allow phantom reads as other transactions can still insert new records into the tables that have been read. 

The snapshot isolation level uses row versioning to work only with a version of the data as it existed when the transaction began. Other transactions can read and modify the same records, but the snapshot isolated transaction effectively works on its own separate copy of the data that’s not visible to other transactions. With a snapshot isolation level, dirty reads, nonrepeatable reads and phantom reads are all prevented.

Finally, serializable is the highest isolation level available, where transactionsare completely isolated from one another and effectively run one at a time. When serializable transactions are executed, they will only read committed data, no other transactions can modify data previously read, and other transactions cannot insert records within the range of values already read. Like snapshot, the serializable isolation level will not allow dirty reads, nonrepeatable reads and phantom reads.

Sources:

https://www.linkedin.com/learning/designing-database-solutions-for-sql-server-2016/transaction-isolation-strategy

Last modified: March 19, 2019

Author

Comments

Write a Reply or Comment