Post reply

Transaction Isolation Level SQL Server 2005

  • July 8, 2009 at 12:53 am

    #97835

    Hi All

    Our application is being redeveloped and the backend would be changed from Oracle 9i to SQL Server 2005. With regards to this I am trying to find the most appropriate transaction isolation level that should be set on the new database (SQL Server 2005). Based on below readings in the msdn I am more inclined to use READ_COMMITTED_SNAPSHOT isolation level.

    http://msdn.microsoft.com/en-us/library/ms188277.aspx

    http://msdn.microsoft.com/en-us/library/ms189050.aspx

    http://msdn.microsoft.com/en-us/library/ms345124(SQL.90).aspx

    However many folks have told me that Microsoft advices in most cases to use the default transaction isolation i.e. READ_COMMITED and that new snapshot isolation level may have a negative affect on overall performance.

    My understanding of READ_COMMITTED_SNAPSHOT is that it is suspiciously similar to the way Oracle achieves READ_COMMITED isolation level by muliversioning the rows.

    http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html

    Would like to know if setting the transaction level to READ_COMMITTED_SNAPSHOT isolation level is better approach in the OLTP database. What would be the caveats to look out for if implementing this specifically with regards to

    a.The increased the usage of the tempdb.

    b.The impact on long running transactions (issues like Snapshot too old on Oracle).

    c.Currently we are planning to use the Timestamp column in each table and ensure the updates to a record in a table is matched with the previous read value of timestamp. Would we need to revisit this if using the read committed snapshot isolation level?

    d.Any other errors/issues that we should be aware of when using this isolation level.

    I expect the volume of simultaneous transactions hitting a set of tables to increase. Particularly the tables which keep the track of inventory available would accessed, updated and deleted concurrently. If we use the Read Committed transaction isolation level we could run into deadlocks and hangs.

    Some of the folks here are working around this by setting "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED " . But I think this should be a strict no-no in any prodxn db.

    Am looking for pointers to what a better approach to follow in this scenario.

    Thanks,

    Anand.

  • July 8, 2009 at 1:25 am

    #1021517

    I would start with READ COMMITTED. It's the default isolation level and most of the time is sufficient. Do some load testing, if you do have lots of blocking (and maybe deadlocks) and you can't resolve them by tuning indexes or optimising queries, then consider READ COMMITTED SNAPSHOT.

    The downside of READ COMMITTED SNAPSHOT is that it has an overhead and puts a lot of load on TempDB

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply