IsolationLevel

[Francois Malgreve]

Hi,

I am interested in gaining more knowledge about what is an isolation level.
Indeed I need to use transactions in my application ( I believe any
professional software would need to use it to preserve data integrity). But
in the same time i am very unfamiliar with the DB related jargon as what is
an isolation level, what is a dirty read, a phantom row, a non repeatable
read.

All those terms come from the .Net framework reference at the page detailing
the different values for the enum IsolationLevel.

As I do not understand much of them but want (and need) to understansd them,
is there anyone whou could give me a difinition for the one i mentioned
above or redirect me to some tutorial or article explaining the main terms
related to concurential DB access and isolation levels?

many thanks

francois

 

[Hussein Abuthuraya[MSFT]]

I think this article should says it all:

Implementing Database Transactions with Microsoft .NET
http://msdn.microsoft.com/library/en-us/dnbda/html/psent.asp?frame=true

and look for the "Appendix A: Transactions Overview". The above article allows to download a complete working sample .NET bproject that uses transactions (PetShop).

Also if you look for any book that deals with .NET and transactions, it should help more (I don't know any specific book but sure you will find some out there)

I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 

[Angel Saenz-Badillos[MS]]

Francois,
This is actually a fairly complex question and I am not going to attempt to
give you a full response. You should read your database specific
documentation for the exact definitions since some of them are vendor
specific.

Here is a very basic 'cliff notes' version to get you started:

Whenever you modify a database while using a transaction you are going to
place locks on your predicate (Quick definition: your predicate is the part
of the database that you are currently working on, so if your query looks
like "select * from foo where id>100" your predicate will be all values of
foo where id>100).

The more you lock your predicate the more you guarantee data integrity, but
at the cost of poor scalability, when you lock a very small amount of data
your application will scale better (more concurrent users). To control the
amount of data that your transaction will lock you can use Isolation Levels.

We use Isolation Levels to determine how your transaction will place locks
in your predicate. For some strange reason database people have defined
Isolation levels in terms of what they allow other people to do to your
predicate:

If your Isolation level guarantees Repeatable Reads then it is not allowing
other people to UPDATE data in your predicate. Every time you read the data
in your predicate it will look the same. Please note that this does not
protect you against "phantom" rows being inserted into your predicate, it
just guarantees that whatever was there before will continue being there.

If your Isolation level guarantees no Phantom Rows then it is not allowing
other people to INSERT data in your predicate. This guarantees that every
time you read your data you will not see any new rows.

Dirty Reads are a completely different beast. If your Isolation level allows
Dirty Reads (warning! here be dragons) then it is allowing _you_ to read
data from a different transaction that has not yet been committed. Note that
if the other transaction rolls back then you are reading data that never
existed.
Note 2: allowing dirty reads does NOT mean that other people can look at
your uncommitted changes.

Isolation Levels:
Read Committed, allows other people to update and to insert data into parts
of the predicate that you have not yet updated.
Repeatable Read, allows other people to insert rows into the predicate. Does
not allow other people to update rows in your predicate.
Serializable (for sql server) Does not allow other people to insert or
update data into your predicate.
Read Uncommitted (for sql server) allows other people to update and to
insert data into parts of the predicate that you have not yet updated, it
allows Dirty Reads of other people's transaction changes.

Hope this helps