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:
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 “dirty 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!