Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Transaction Isolation Level SQL Server 2005 Expand / Collapse
Posted Wednesday, July 8, 2009 12:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:27 AM
Points: 68, Visits: 55
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.

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.

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.

Post #749071
Posted Wednesday, July 8, 2009 1:25 AM



Group: General Forum Members
Last Login: 2 days ago @ 5:18 AM
Points: 45,619, Visits: 44,147
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

Post #749088
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse