IsolationLevel problems

Hi,

I am trying to speed up several SELECTs and at the same time reduce the potential for deadlocks by starting a transaction within a UnitOfWork and setting the transaction isloation level to ReadUncommitted. Yes, I am aware of potential dirty reads.

using (var unitOfWork = LightSpeedContext<SomeContext>.CreateUnitOfWork())
using (var transaction = unitOfWork.BeginTransaction(IsolationLevel.ReadUncommitted))
{
    //some SELECT
}

Then I used SQL Server Profiler to examine the statements being sent to the database in order to validate that the isolation level is set correctly.

When the method is called for the first time I see an "Audit Login"-Event in the profiler before the SELECT statement that says:

set transaction isolation level read committed

Calling the same method again (with a brand new UnitOfWork) the "Audit Login"-Event says:

set transaction isolation level read uncommitted

Afterwards - in ALL other units of work in the program that neither explicitly use a transaction nor set an isolation level, the same "read uncommitted" isolation level keeps being used. Until I restart the program.

I had expected that in every unit of work that does not explicitly set a transaction isolation level, the default of "read committed" would be used.

I am using SQL Server 2012. I am forced to use a rather old LightSpeed version 4.0.1035.19328.

Can you give me a hint as to what I am doing wrong?

Thank you!

Sorry, I seem to have misinterpreted the information shown by SQL Server Profiler.

If I put a breakpoint after "unitOfWork.BeginTransaction()", run to that breakpoint and then start the profiler, I see some "Existing Connections", one of which has the correct transaction isolation level set.

So it seems that all is well!