468,110 Members | 1,854 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,110 developers. It's quick & easy.

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
Nov 15 '05 #1
3 8326
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

Nov 15 '05 #2
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


Nov 15 '05 #3
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


Nov 15 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Tom | last post: by
6 posts views Thread by wukexin | last post: by
3 posts views Thread by Colin J. Williams | last post: by
7 posts views Thread by Corepaul | last post: by
5 posts views Thread by Steve | last post: by
8 posts views Thread by Mark | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.