This post originated from an RSS feed registered with .NET Buzz by Raymond Lewallen. Original Post: Database Basics Part Three - Isolation Feed Title: Raymond Lewallen Feed URL: /error.htm?aspxerrorpath=/blogs/raymond.lewallen/rss.aspx Feed Description: Patterns and Practices, OOP, .Net and Sql Latest .NET Buzz Posts Latest .NET Buzz Posts by Raymond Lewallen Latest Posts From Raymond Lewallen

In an effort to provide information for those of you who are newcomers to databases, especially with the wonderul new Sql Server Express, I have committed myself to about half a dozen posts or so targeted directly at you to help you understand and build better databases.  Stay tuned to this blog for weekly updates to this series, or subscribe to the rss feed.

Previous articles in this series:

• The ACID Model – the cornerstone of databases and database transactions.
• Normalization – the logical storage of data.

Today we are going to look at isolation levels, specifically isolation levels supported within Sql Server.  By definition, an isolation level determines the degree of isolation, or seperation, of data among concurrent transactions.  See the post on the ACID model for how isolation is a cornerstone of database design and its transactions.

There are 5 levels of isolation available in Sql Server, 1 of them being new in Sql Server 2005.

Read Uncommitted Isolation

This is also referred to as “dirty read” isolation.  This is the lowest level of isolation available and does nothing more than ensure that physically corrupt data cannot be read, but allows reads of logically corrupt data, if the data exists in that state.  Not even exclusive locks prevent other transactions from acting on this data.  Let’s take a look at what this means.

Take the following table:

Now let’s examine what occurs given the following series of events, which will result in what are called “diry reads”, which are a type of non-repeatable read; a type of data inconsistency.

• TransactionA starts a transaction.
• TransactionA updates the row to change the color to Red.
• TransactionB starts a transaction.
• TransactionB reads the row from the database.
• TransactionA rolls back the transaction, therefore leaving the color intact as Black.
• TransactionB ends.

What data does TransactionB have?  TransactionB shows the color of the car as Red, even though, logically and transactionally, this data never really even existed in the database because TransactionA never committed that change to the database.  This is known as a “dirty read”. 

You can also lose updates this way.  Let’s take the following scenario:

• TransactionA starts a transaction.
• TransactionA updates the row to change the color to Red.
• TransactionB starts a transaction.
• TransactionB updates the row to change the color to Blue.
• TransactionA commits its transaction, changing the color to Red.
• TransactionB commits its transaction, changing the color to Blue.

What color is the car?  Its blue.  This is also known as the “last in wins” rule.  If multiple transactions change the same data at the same time, the last one to commit is going to win.

You can see from this simple example what kind of troubles you can get into by using Read Uncommitted isolation levels.

Read Committed Isolation

This is the default isolation level used in Sql Server.  Sql Server issues shared locks under this isolation level which prevents dirty reads from occurring, like what we saw above in read uncommitted isolation levels.  In the same scenario given above, this time using read committed isolation, TransactionB would have in fact shown the color as Black, because it would be reading data as it existed in a committed state.

What read committed isolation does not do is prevent other transactions from from changing the same data at the same time the data is already being held by another transaction.  There are 2 primary results for this type of behavior, known as phantom data and again, like we saw in read uncommitted, non-repeatable reads, but this time they are not the special type of non-repeatable reads called “dirty reads” like we saw above.

• Phantom data occurs under the following circumstances under read committed
• TransactionA begins a transaction.
• TransactionA reads a row.
• TransactionB begins a transaction.
• TransactionB deletes the row read by TransactionA.
• TransactionB commits its transaction.
• TransactionA can no longer repeat its initial read in order to do an update, because the row no longer exists, resulting in phantom data.
• Non-repeatable reads (not dirty reads) occur under the following circumstances under read committed
• TransactionA begins a transaction.
• TransactionA reads a row.
• TransactionB begins a transaction.
• TransactionB changes the color of our car to red.  TransactionA read the color of the row to be black.
• TransactionB commits its transaction.
• TransactionA reads the row again.
• TransactionA has inconsistent data because the color now reads red instead of black, all within the scope of the same transaction that TransactionA began.

Repeatable Read Isolation

Here, Sql Server places locks on the data used in a query within the transaction, and this prevents other transactions from modifying data in that data set locked by the initial transaction.  This prevents non-repeatable reads, including dirty reads, from occurring.  However, another concurrent transaction can add new data to that scope of data of the original transaction, but the original transaction does not include the new row as part of its lock or data set, because it didn’t exist at the time the lock was issued.  The initial transaction will, however, get that inserted row in subsequent reads of the data.  Again, this is phantom data.

• Phantom data will occur under the following circumstances under repeatable read
• TransactionA begins a transaction.
• TransactionA reads all rows that have color = “Black”.
• TransactionB begins a transaction.
• TransactionB inserts new row with values “GM”, “Pontiac”, “Black”.
• TransactionB commits its transaction.
• TransactionA updates all the data from its query to color = “Red”.  This will also update the row that TransactionB inserted, because TransactionA must read the data again in order to update it.
• TransactionA commits its transaction.

Serializable Isolation

This is the most restrictive isolation level available, and not a commonly used level of isolation, because it is also referred to as “the perfect transaction”, and perfect transactions just aren’t always the best choice.  Once a serializable transaction has started working on a particular data set, no other transaction can do anything to it until the initial transaction releases its locks.  This means no changing data, no adding new rows into the same data set, nothing.  Phantom data cannot exist and non-repeatable reads cannot occur.  However, this affects your performance because this isolation level reduces your concurrency levels because of the restrictiveness of the isolation.  Other transaction must wait for the locks to release. 

Snapshot Isolation – New to Sql Server 2005

Other databases have had a snapshot isolation level for awhile now.  Sql Server just caught up.  Snapshot isolation is an optimistic locking approach to the pessimistic locking schemes of read committed and serializable isolation levels.  There are 2 types of snapshot isolation levels:  Transaction-level snapshots provide the same level of isolation as serializable transactions, and statement-level snapshots provide read committed levels of isolation.

Snapshot isolation basically works by row versioning.  Row versioning is nothing more than Sql Server keeping up with changes made to the database while a transaction is occuring.  What happens, and this is the beauty of snapshot isolation, a copy of a row is made for the transaction is that is where the read occurs.  This prevents a lock on the actual row in the database.  When the transaction wants to write the data, it checks to see if the actual data matches the copy of the data, and if it does, it commits the write.  If not, then the snapshot transaction fails.  This is the same as serializable isolation, except we have better performance because we didn’t have to place locks on the data that we read.  This helps to support more concurrent transactions.

I haven’t played too much with snapshot isolation in Sql Server 2005, so please experiement for yourselves and as always, do your own research.  If you want to read what is probably the best source of information on snapshot isolation, read this 60 page whitepaper on it written by Kimberly L. Tripp.  That’s how much info there is to cover on the topic of just one isolation level, much less all 5.  Again, this is a series for newcomers to databases, so I don’t go into that level of depth.  Not to mention the fact that I can never go into Sql Server as deeply and with as much knowledge as the wonderful Kimberly L. Tripp!