HELP! SET TRANSACTION ISOLATION LEVEL query.

[Eric Porter]

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

[Roji. P. Thomas]

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" <bo********@hotmail.com> wrote in message
news:bq*******************@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

[Narayana Vyas Kondreddi]

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" <bo********@hotmail.com> wrote in message
news:bq*******************@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

[Eric Porter]

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" <la********@somewhere.com> wrote in message
news:uE**************@TK2MSFTNGP10.phx.gbl...

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" <bo********@hotmail.com> wrote in message
news:bq*******************@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