New Snapshot Isolation Level on Yukon

[GiroCarl]

Hello Everyone,

We want to take advantage of the new SNAPSHOT ISOLATION LEVEL that SQL
Server Yukon offers. This will allow readers not bloking
"modificators" and "modificators" not blocking readers. Just like
Oracle offers for a long time with their rollback segments.

The problem is that I don't know how to use it with ADO.NET.

To be in SNAPSHOT ISOLATION LEVEL you have to issue this statement to
the DB:

ALTER DATABASE MyDbName SET ALLOW_SNAPSHOT_ISOLATION ON

After that you can issue all the queries you want and you'll see the
"before image" of any uncommited data that exists at the beginning of
the query (snapshot).

Since all queries sent to the server with ADO.NET are executed with
the stored procedure sp_executesql, all those queries are in another
context and they don't "see" that the ALTER DATABASE ...
ALLOSW_SNAPSHOT_ISOLATION ON was set.

We tried this by executin a command as nonquery (containing the ALTER
DATABASE command) and after that issuing a query with a SqlDataReader
and it did not worked.

Any idea, clue on how to implement it with ADO.NET?

Hope I was clear,

Regards,

Carl

 

[Peter Vervoorn]

From: "GiroCarl" <[email protected]>
Newsgroups: microsoft.public.dotnet.framework.adonet
Sent: Monday, February 23, 2004 6:34 PM
Subject: New Snapshot Isolation Level on Yukon

Hello Everyone,

We want to take advantage of the new SNAPSHOT ISOLATION LEVEL that SQL
Server Yukon offers. This will allow readers not bloking
"modificators" and "modificators" not blocking readers. Just like
Oracle offers for a long time with their rollback segments.

The problem is that I don't know how to use it with ADO.NET.

To be in SNAPSHOT ISOLATION LEVEL you have to issue this statement to
the DB:

ALTER DATABASE MyDbName SET ALLOW_SNAPSHOT_ISOLATION ON

After that you can issue all the queries you want and you'll see the
"before image" of any uncommited data that exists at the beginning of
the query (snapshot).

Since all queries sent to the server with ADO.NET are executed with
the stored procedure sp_executesql, all those queries are in another
context and they don't "see" that the ALTER DATABASE ...
ALLOSW_SNAPSHOT_ISOLATION ON was set.

We tried this by executin a command as nonquery (containing the ALTER
DATABASE command) and after that issuing a query with a SqlDataReader
and it did not worked.

Any idea, clue on how to implement it with ADO.NET?

Hope I was clear,

Regards,

Carl

Hello Carl,

You don't have to call the ALTER DATABASE statement each time.
The 'trick' is that you have to do your reads from inside a transaction as
well.
So, what you should do is open a transaction on the connection and specify
the isolation level:
SqlTransaction TheTrans = TheConnection.BeginTrnas(IsolationLevel.Snapshot);

Hope this helps,

Peter

 

[GiroCarl]

In fact I've found what was wrong:

The server must be set to run in a mode that supports versioning if
you are using statement level versioning. The flag is /T3970 in beta
1.

The server must be started manually with the trace flag 3970.

And this way, it works,

Thank's

Carl