Setting IsolationLevel for a transaction does not seem to have an effect

[aay]

Hi Ritesh,

I am not sure whether you still check this message board, but
hopefully you will do so in the near future.

I am running into issues with IsolationLevel settings for
UnitOfWorkScope (which should go all the way down to NHibernate
transactions, right?). I need to make sure that every single select is
done with ReadUncommited isolation level, and playing with different
settings and values I cannot seem to get this level - SQL Profiler
trace keeps showing "set transaction isolation level read committed"
for a connection.

Do you have any advice on this matter? I would appreciate any sort of
direction.

Thanks!

[aay]

Actually wanted to add to this post some more details.

I started playing with some of your unit tests and noticed that
isolation level is not being set properly. Doing some investigation
further I figured out that it is only a first transaction that runs
with default isolation level set to ReadCommitted - any further number
of transactions seem to run with correct level set (according to SQL
Profiler). I actually switched to ISession and .BeginTransaction()
methods of nHibernate directly without using nCommon to see where the
problem is at.

I've downloaded latest source of nHibernate and will look into it
next. But if you see this post and will have a desire to confirm my
concern, please do, I would appreciate that.

Alex

[Ritesh Rao]

Alex,

Could you let me know how you are setting the isolation level on the parent unit of work scope? If you could give me a test case I can replicate I can take a look why the isolation level is not being set on the root.

Thanks,

Ritesh

[aay]

Ritesh,

I don't think this has anything to do with nCommon - I think it's a
problem with nHibernate. But then again - I am not sure if there is
something else that I'm missing.

The very first transaction I do in an application (or sets of unit
tests) runs with 'read committed' isolation level no matter what is
specified as isolation level in nHibernate config or directly on the
transaction. Every subsequent transaction seems to be running with
isolation level set to whatever value I have.

So, pretty much I've got 2 sets of tests - one using nCommon, another
one using just ISession and ISession.BeginTransaction().

1. For nCommon usage there are 3 unit tests doing exactly the same:

using (UnitOfWorkScope scope = new UnitOfWorkScope
(IsolationLevel.ReadUncommitted))
{
NHRepository<Order> repository = new NHRepository<Order>();
IList<Order> orders = (from o in repository
where o.Id == 133268203
select o).ToList();
scope.Commit();
Assert.AreEqual(1, orders.Count);
}

2. For hNibernate there are couple of unit tests (almost the same,
just different variations of sessions and transactions), one of them
is as follows:

using (ISession session = Store.Local.Get<ISessionFactory>
("NHRepositoryTests.SessionFactory").OpenSession())
{
using (ITransaction transaction = session.BeginTransaction
(IsolationLevel.ReadUncommitted))
{
Order result = session.Get<Order>(133268223);
transaction.Commit();
Assert.IsNotNull(result);
}
using (ITransaction transaction = session.BeginTransaction
(IsolationLevel.ReadUncommitted))
{
Order result = session.Get<Order>(133268229);
transaction.Commit();
Assert.IsNotNull(result);
}
}

3. nHibernate config entry:

<property name="connection.isolation">ReadUncommitted</property>

For both of these cases i run SQL Profiler and look at AuditLogin
events which contain connection details. First connection ever will
run with 'set transaction isolation level read committed' while the
rest of them will run with "read uncommitted' set. I am also not 100%
sure as to how nHibernate manages the connections and whether I keep
getting the same connection over and over again, since the tests are
run sequentially, or whether nHibernate discards a connection and gets
a new one since I get a new ISession in different unit tests.

I haven't got to nHibernate latest source to build and confirm/deny
this behavior in the latest trunk version. Most likely this will have
to wait until I can find some time to dig into this further. But this
is where I ended up so far.

And if you want to replicate this behavior - just run any single test
with isolation level set to anything but ReadCommitted and trace in
SQL Profiler. You should see that your connection is set to a default
value of ReadCommitted. This is how I discovered it, because I have a
requirement to have all reads to be dirty.

Well, hope this helps.

Regards,
Alex

On Oct 20, 7:55 am, Ritesh Rao <rao.rit...@gmail.com> wrote:
> Alex,
> Could you let me know how you are setting the isolation level on the parent
> unit of work scope? If you could give me a test case I can replicate I can
> take a look why the isolation level is not being set on the root.
>
> Thanks,
> Ritesh
>

[Ritesh Rao]

Hey Alex,

I talked with Ayende regarding this issue and both he and I think there's something wrong here. NHibernate should be honoring your isolation level en on the first transaction creation. I'll have to run some tests on my end to make sure, perhaps tonight or tomorrow when I get some time but in the mean time could you get a download NHProf from www.nhprof.com and set it up to profile your tests and see what its showing? That would give more reliable NH profiling than SQL Profiler.

Let me know what you find.

Thanks,

Ritesh

[aay]

Ritesh,

I have NH Prof and I did look at what it outputs. Actually originally
I didn't bother that much with SQL Profiler since NH Prof was showing
transactions beginning with isolation level set to whatever I wanted
it to. However then another member of our team was doing something and
he noticed that transaction details displayed in AuditLogin event
trapped by SQL Profiler are always show 'read committed' no matter
what.

This is what I get now from SQL Profiler:

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7

set transaction isolation level read committed

NH Prof says that transaction begins with ReadUncommitted level of
isolation.

Our DBAs are not going to trust anything but the profiler and what
they can get from the DB itself. Am I wrong about the way I try to
confirm isolation level on my SQL server? Are there any other ways?

I actually have a simple solution with 3 projects in it that I made
specifically to figure out what was going on: 1 domain class, 1 hbm
file and 2 unit tests. If you want I can zip it up and send it to you
so you don't need to spend some time building that.

I hope I'm not doing something totally wrong here, but this is where I
am now at.

Thank you for your involvement.

Regards,
Alex

On Oct 21, 10:54 am, Ritesh Rao <rao.rit...@gmail.com> wrote:
> Hey Alex,
> I talked with Ayende regarding this issue and both he and I think there's
> something wrong here. NHibernate should be honoring your isolation level en
> on the first transaction creation. I'll have to run some tests on my end to
> make sure, perhaps tonight or tomorrow when I get some time but in the mean

> time could you get a download NHProf fromwww.nhprof.comand set it up to

> profile your tests and see what its showing? That would give more reliable
> NH profiling than SQL Profiler.
>
> Let me know what you find.
>
> Thanks,
> Ritesh
>

[aay]

Ritesh,

Did you have a chance to look at this issue and maybe create some unit
tests?

Thanks,
Alex

On Oct 21, 6:21 pm, aay <yerma...@gmail.com> wrote:
> Ritesh,
>

> > time could you get a download NHProf fromwww.nhprof.comandset it up to