HELP! SET TRANSACTION ISOLATION LEVEL query.

  • Thread starter Thread starter Eric Porter
  • Start date Start date
E

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
 
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.
 
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




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
 
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.
 
Back
Top