ADO.NET vs SNAPSHOT ISOLATION LEVEL

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  February 23, 2004 at 1:38 pm

  #65038

  Hello Everyone,

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

  The problem is that It don't seem to be easy use this 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 ... ALLOW_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:

  ...

  MyCmd.ExecuteNonQuery();

  ...

  MyCmd.ExecuteReader();

  ...

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

  N.B. I know I ca do it within a stored proc but I would be able to do it outside of a strored proc.

  Hope I was clear,

  Regards,

  Carl

• SteveB-74825

  Hall of Fame

  Points: 3290

  More actions

  February 24, 2004 at 3:20 am

  #495265

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

   

  Each database needs to have snapshot isolation set and transaction isolation level must be set to snapshot.

   

  You need to be aware as well that the snapshots are stored in tempdb so if you have a busy server tempdb is likely to grow quickly.

  cheers

  dbgeezer

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  February 24, 2004 at 8:14 am

  #495324

  Thank's Steve,

  We've noticed that yesterday in our tests.

  Hope the trace flag wont be needed on the next version of SQL Server.

  Hope also that SNAPSHOT ISOLATION LEVEL will become more transparent (the default behavior).

  Best regards,

  Carl