HELP! SET TRANSACTION ISOLATION LEVEL query.

Dear All,

I have a VB6.COM program using a VB6.COM DLL, which in turn uses ADODB that
performs various bits of SQL.

I have re-written the VB6.COM DLL in C#.NET (with a VB6 shell), which uses
the ADODB.Interop library supplied with .NET. The VB6.COM program above
calls this C#.NET library in an effort to perform the equivalent
functionality.

However, when I look at the trace information in the SQL Server Profiler,
they behave annoyingly differently.

In COM-COM-ADODB, BeginTrans causes a SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ to occur. From that point on, no change to TRANSACTION
ISOLATION LEVEL is made.

In COM-.NET-Interop.ADODB, BeginTrans(Specification of RepeatableRead
argument is necessary here) causes SET TRANSACTION LEVEL REPEATABLE READ to
occur. CommitTrans causes a SET TRANSACITON LEVEL READ COMMITTED to occur.
So, to maintain TRANSACTION LEVEL REPEATABLE READ, the specific property
needs to be set on the connection object (which is not alway observed).

The question:
How can I, in .NET using Interop.ADODB, sometimes using ADODB connections
passed to it by COM programs, sometimes opening its own ADODB connections,
ensure/enforce that a connection's transaction isolation level is maintained
at the desired level, and that the system doesn't make decisions on my
behalf?

Eric

re: HELP! SET TRANSACTION ISOLATION LEVEL query.

The Isolation level of the most outer transaction
will be the effective setting, regardless whatever is
the settings in the inner transactions.

Also, if you are using COM+, the isolation level will
be SERIALISABLE.

--
Roji. P. Thomas
SQL Server Programmer ;)
________________________
"Eric Porter" <bodytemp70@hotmail.com> wrote in message
news:bq4mbe$4s2$1$8302bc10@news.demon.co.uk...[color=blue]
> Dear All,
>
> I have a VB6.COM program using a VB6.COM DLL, which in turn uses ADODB[/color]
that[color=blue]
> performs various bits of SQL.
>
> I have re-written the VB6.COM DLL in C#.NET (with a VB6 shell), which uses
> the ADODB.Interop library supplied with .NET. The VB6.COM program above
> calls this C#.NET library in an effort to perform the equivalent
> functionality.
>
> However, when I look at the trace information in the SQL Server Profiler,
> they behave annoyingly differently.
>
> In COM-COM-ADODB, BeginTrans causes a SET TRANSACTION ISOLATION LEVEL
> REPEATABLE READ to occur. From that point on, no change to TRANSACTION
> ISOLATION LEVEL is made.
>
> In COM-.NET-Interop.ADODB, BeginTrans(Specification of RepeatableRead
> argument is necessary here) causes SET TRANSACTION LEVEL REPEATABLE READ[/color]
to[color=blue]
> occur. CommitTrans causes a SET TRANSACITON LEVEL READ COMMITTED to[/color]
occur.[color=blue]
> So, to maintain TRANSACTION LEVEL REPEATABLE READ, the specific property
> needs to be set on the connection object (which is not alway observed).
>
> The question:
> How can I, in .NET using Interop.ADODB, sometimes using ADODB connections
> passed to it by COM programs, sometimes opening its own ADODB connections,
> ensure/enforce that a connection's transaction isolation level is[/color]
maintained[color=blue]
> at the desired level, and that the system doesn't make decisions on my
> behalf?
>
> Eric
>
>[/color]

re: HELP! SET TRANSACTION ISOLATION LEVEL query.

See if this provides you with some info:
http://vyaskn.tripod.com/com_isolation_level.htm

--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm




"Eric Porter" <bodytemp70@hotmail.com> wrote in message
news:bq4mbe$4s2$1$8302bc10@news.demon.co.uk...
Dear All,

I have a VB6.COM program using a VB6.COM DLL, which in turn uses ADODB that
performs various bits of SQL.

I have re-written the VB6.COM DLL in C#.NET (with a VB6 shell), which uses
the ADODB.Interop library supplied with .NET. The VB6.COM program above
calls this C#.NET library in an effort to perform the equivalent
functionality.

However, when I look at the trace information in the SQL Server Profiler,
they behave annoyingly differently.

In COM-COM-ADODB, BeginTrans causes a SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ to occur. From that point on, no change to TRANSACTION
ISOLATION LEVEL is made.

In COM-.NET-Interop.ADODB, BeginTrans(Specification of RepeatableRead
argument is necessary here) causes SET TRANSACTION LEVEL REPEATABLE READ to
occur. CommitTrans causes a SET TRANSACITON LEVEL READ COMMITTED to occur.
So, to maintain TRANSACTION LEVEL REPEATABLE READ, the specific property
needs to be set on the connection object (which is not alway observed).

The question:
How can I, in .NET using Interop.ADODB, sometimes using ADODB connections
passed to it by COM programs, sometimes opening its own ADODB connections,
ensure/enforce that a connection's transaction isolation level is maintained
at the desired level, and that the system doesn't make decisions on my
behalf?

Eric

re: HELP! SET TRANSACTION ISOLATION LEVEL query.

There are no nested transactions. The connection, in .NET, is opened
explicity as REPEATABLE READ, and transactions are begun (serially - i.e.
one is committed before another one is opened) at the same level, but still
on commit, transaction isolation level is set to READ COMMITTED.

"Roji. P. Thomas" <lazydragon@somewhere.com> wrote in message
news:uENpWuNtDHA.1888@TK2MSFTNGP10.phx.gbl...[color=blue]
> The Isolation level of the most outer transaction
> will be the effective setting, regardless whatever is
> the settings in the inner transactions.
>
> Also, if you are using COM+, the isolation level will
> be SERIALISABLE.
>
> --
> Roji. P. Thomas
> SQL Server Programmer ;)
> ________________________
> "Eric Porter" <bodytemp70@hotmail.com> wrote in message
> news:bq4mbe$4s2$1$8302bc10@news.demon.co.uk...[color=green]
> > Dear All,
> >
> > I have a VB6.COM program using a VB6.COM DLL, which in turn uses ADODB[/color]
> that[color=green]
> > performs various bits of SQL.
> >
> > I have re-written the VB6.COM DLL in C#.NET (with a VB6 shell), which[/color][/color]
uses[color=blue][color=green]
> > the ADODB.Interop library supplied with .NET. The VB6.COM program above
> > calls this C#.NET library in an effort to perform the equivalent
> > functionality.
> >
> > However, when I look at the trace information in the SQL Server[/color][/color]
Profiler,[color=blue][color=green]
> > they behave annoyingly differently.
> >
> > In COM-COM-ADODB, BeginTrans causes a SET TRANSACTION ISOLATION LEVEL
> > REPEATABLE READ to occur. From that point on, no change to TRANSACTION
> > ISOLATION LEVEL is made.
> >
> > In COM-.NET-Interop.ADODB, BeginTrans(Specification of RepeatableRead
> > argument is necessary here) causes SET TRANSACTION LEVEL REPEATABLE READ[/color]
> to[color=green]
> > occur. CommitTrans causes a SET TRANSACITON LEVEL READ COMMITTED to[/color]
> occur.[color=green]
> > So, to maintain TRANSACTION LEVEL REPEATABLE READ, the specific property
> > needs to be set on the connection object (which is not alway observed).
> >
> > The question:
> > How can I, in .NET using Interop.ADODB, sometimes using ADODB[/color][/color]
connections[color=blue][color=green]
> > passed to it by COM programs, sometimes opening its own ADODB[/color][/color]
connections,[color=blue][color=green]
> > ensure/enforce that a connection's transaction isolation level is[/color]
> maintained[color=green]
> > at the desired level, and that the system doesn't make decisions on my
> > behalf?
> >
> > Eric
> >
> >[/color]
>
>[/color]