Sunday, December 6, 2009

System.Transaction.IsolationLevel

A little amusing observation,
IsolationLevel enumeration defined in the System.Transactions namespace looks something like this:
public enum IsolationLevel
{
Unspecified,
ReadUncommitted,

RepeatableRead,
ReadCommitted,
Serializable,
Chaos,
Snapshot
}
By definition, chaos means -"a state of extreme confusion and disporder. " Initially I was not able to understand what is the whole purpose of having this option (that too with a funny name)
After doing some reading (on MSDN) and talking (with peer devs), this is what I understood.

Chaos Isolation Level - Behaves the same way as Read Uncommited, with additional features as stated below:

  • It permits viewing uncommitted changes by other transactions
  • It checks any other uncompleted update transactions with higher restrictive isolation levels to ensure not to raise any conflics i.e. any oending changes from more highly isolated transactions cannot be overwritten
  • Rollback is not supported in this isolation level

If you want to perform read operations over once per transaction, then go for the Chaos isolation level

Chaos isolation level is present in SSIS as well. Select the task or container on which you want to set the isolation level. Then go to the properties and set the property named IsolationLevel to Chaos.

//Cheers!
//Currently listening to: Put your hands up (Radio Edit) by Wet Fingers

3 comments:

Unknown said...

Can you please give an example,what is the main difference between two isolation levels "Chaos" and "Read Uncommitted"?

MalcolmW said...

Similar:
Both read uncommitted and committed transactions.
Different:
ReadUncommitted (when writing data, by insert or update) "locks" the data being changed, and then releases this lock when the transaction completes. This prevents two transactions trying to change the same data at the same time.
Chaos (when writing data, by insert or update) does NOT lock the data being changed. So, two CHAOS transactions could change the same data at the same time. The result would be unpredictable, hence the name.
I don't know of any database that supports this isolation level - it seems to exist only in ADO.NET and SSIS. Without a database to use it with, perhaps it should be renamed as Pointless. :-)

MalcolmW said...
This comment has been removed by the author.