Transaction Isolation Levels Explained in Details

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.

 

Isolation Level

Dirty Read

Non repeatable Read

Phantom Read

Serializable

No

No

No

Repeatable Read

No

No

Yes

Read Committed

No

Yes

Yes

Read Uncommitted

Yes

Yes

Yes

 

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.

Isolation Level System
Serializable Most suitable for environments where accuracy is critical, but transactions are short lived, update few rows, and usually different set of rows.
Repeatable Read Serializable transaction rules apply, but throughput is slightly better.  Typically, I have not seen this used in practice.
Read Committed Should be your default chose for most systems.  Most suitable for high performance environments, where the risk of two users modifying the same data is relatively low.  Also suited for environments with few transactions, thus greatly minimizing the risk of two users updating the same row.
Read Uncommitted Should not be used for writing the data.  However, it may be suitable for long running reports in order to avoid light-weight locks put on many rows by the report transaction, thus causing potential blocking during write processes in some database systems.

 

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.

6 Comments

  1. Pingback: Exam 70-487 Preparation, Part I: Accessing Data – All about Microsoft .NET development blog

Leave a Reply

Your email address will not be published. Required fields are marked *