In this post I would like to go into a level of details regarding transaction isolation levels with simple explanations and advice on when to use each one.

Transaction isolation levels refer to multiple database processes interacting with each other in terms of reading and writing the data.  Isolation levels are there to deal with possible problems defined in database theory.  These problems are

• Dirty reads: A transaction reads data that has been written by another transaction that has not been committed yet.

• Non repeatable reads: A transaction rereads data it has previously read and finds that another committed transaction has modified or deleted the data.

• Phantom reads: A transaction re-runs a query returning a set of rows that satisfies a search condition and finds that another committed transaction has inserted additional rows that satisfy the condition.

ANSI (American National Standards Institute) defined the following isolation levels between transactions in SQL 92 standard.

Now, let’s talk about this in terms of reading and writing the data.  Let’s say a writer is a transaction that writes data.  Let’s say reader is a transaction that reads the same data.  Regardless of isolation level, readers never block readers.  By blocking I mean one transaction has to complete (commit) before another can continue.  In case of serializable transactions, writers block readers.  So, if transaction A updated row A, transaction B cannot read row A until transaction A commits (or rollbacks, reverting all the changes).  In case of repeatable read isolation level, writers only block writers, not readers.  In other words, if transaction A updated row A, transaction B can read row A, but will block on updating row A.  However, the version of row A that is read by transaction B will be prior to transaction A’s update statement.  In case of read committed isolation level, writers only block writers, not readers.  In other words, if transaction A updated row A, transaction B can read row A, but will block on updating row A.  However, the version of row A that is read by transaction B will always include changes made by transaction A priory to read.  Hence, if you read the same row twice, you can end up with non repeatable reads.  In case of read uncommitted isolation level, writers do not block readers, potentially causing all issues above.  Different database engine may vary slightly in terms of blocking, for example in case of Oracle writers do not block readers in Serializable isolation level.

Now, let’s talk about Transaction Scope construct in .NET.  Default for it is Serializable isolation level, but you can specify other levels.  So, you must be aware of this fact because serializable transactions are least friendly for high volume multi user systems because they cause the most amount of blocking.  Transaction Scope supports other isolation levels, but I am not talking about them since they are not mentioned in the standard.

Let’s talk about when to use which level.

Finally, if you have a situation where a resource can be a bottleneck, you have to be careful for a couple of reasons.  First of all, if you need exclusive access to this resource, you have to update it first, before reading the results of the update to avoid non repeatable reads.  You also should use serializable transactions in this case.  There is one exception to this rule.  If you make read and write atomic operation, where the transaction cannot be split into two distinct parts – read and write, you should always use this route.  Both Oracle and SQL Server support this type of exclusive atomic operation.  In SQL Server you can you Update statement with Output clause.  In Oracle you can use Update statement with Returning clause.  Second of all, you need to be aware of causing excessive locks or deadlock, if two transactions modify the same set of rows in multiple tables.

Thank you.