Once again, firing up Mr. Peabody's WABAC Machine unearthed a valuable blog entry that I posted before my Simple-Talk blogging days. The post below was originally published on March 2008 and maintains its relevance. Enjoy!

I was watching a very interesting movie quite a few months ago. In this movie, the primary character hopped into his sports car in the middle of the day, backed out of his driveway and proceeded to travel down a deserted street in the middle of New York City. I have never visited the "Big Apple" but I would suspect that this occurrence would be quite an anomaly.

It would be reasonable to assume that having only a singular transaction occur within a SQL Server database would be an equivalent anomaly to the one presented in the movie previously mentioned. To this, the delicate balance between data concurrency and data consistency begins.

Transaction isolation level in stored procedures is the traffic cop on the street of transactions. They prevent those nasty collisions that backup the flow of data. While a detailed description of all transaction isolation levels is another blog entry, I wanted to explore the Evil-Read Trio that can occur if careful consideration of these transaction isolation levels does not occur.

THE DIRTY READ: These are not the ones that are found in the foot locker of your neighborhood teenager. These are transactions that read uncommitted data modifications that are made by another transaction. This can wreak havoc if aggregated calculations occur while transactions are being added or modified and especially of these modifications are rolled back. Using the READ COMMITTED isolation level will prevent the occurrences of dirty reads.

THE NON-REPEATABLE READ: Complex stored procedures may execute a SELECT statement more than once within the same transaction. If another transaction modifies the data prior to the completion of the first transaction different results may occur when the second occurrence of the SELECT statement is run. The utilization of REPEATABLE READ isolation level will prevent the occurrences of non-repeatable reads as well as dirty reads.

THE PHANTOM READ: Much like a non-repeatable read, this type of read depends upon a multiple execution of a SELECT statement within the same transaction and another transaction modifying the data in which the SELECT statement is querying. In the case of the phantom read the modification of the data is an INSERT or DELETE which causes the row in question to appear and/or disappear within the reading transaction. While Ghostbusters may not have a good solution for this, the use of SERIALIZABLE READ isolation level will prevent phantom reads as well as non-repeatable and dirty reads.

The question may arise to how it can be determined that these types of reads are occurring. The ol' handy-dandy SQL Server Profiler can be employed to expose these reads. Include the SQL:StmtStarting , SQL:StmtCompleted, SQL:BatchStarting and SQL:BatchCompleted event classes in
the trace to catch the occurrence.