Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jothikannan
Starting Member
36 Posts |
 Posted - 2006-03-06 : 07:27:20
|
| hi All,Our software is an ERP application developed in SQL sever 2000 (SP4) and uses COM+ 2.0 as MiddleWare.We have packaged all components in MTS with the defaut Isolation level which is SERIALIZABLE. Our application is being accessed by more than 500 concurrent users at any point in time because of which we are facing a lot of locks & deadlocks apart from other performance issues. Now that we intend to change the isolation level at MTS for all our components to "READ COMMITTED'. Will this action create any negative impact in my aplication or Bug apart from consistency of data?Thanks in advance |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-06 : 09:18:41
|
| That question in impossible to answer without a complete understanding of your business rules. Sql server's default isolation level is read committed which is typically a satisfactory isolation level for a system handling "normal" transaction processing. Repeatable Reads and Serializable are the 2 most restrictive levels of isolation in terms of concurrency and exist to prevent specific problems that can occur during concurrent requests. Please read the Books Online topic: "Concurrency Problems". This topic provides a good explanation of when you may want to use either of these isolation levels.Be One with the OptimizerTG |
 |
|
|
Jothikannan
Starting Member
36 Posts |
Posted - 2006-03-06 : 09:42:05
|
| Thanks TG,i am sure my Application really donot Need SERIALIZABLE Isolationn level,i changed the Isoaltion at the code Level(in Stored proc) to Read committed ,read uncommitted for some important sps.Now i am facing Lot of performance & locking issues all other places,so that i decided to change isolation level for All Components in MTS leveli want to know ,1.by changing isolation in MTS,possibilities of bugs?, 2.what are the factors i need to consider before Change the Isolation in MTS?3.what are the thing i need to check in Com+ & SQl server Levels before Change the Isolation ?thanks again TG |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-06 : 10:38:13
|
| 1) I don't see how chaning the isolation level will introduce bugs at an application level. The only difference to you app could (potentially) be different data values returned. But basic signature inputs and result set definitions will remain unchanged.2 & 3) different values in repeatable reads from concurrent users during the lifetime of a given transaction. Phantom rows affecting the values read from concurrent users. Are the only factors (I can think of) that need to be considered when changing from Serializable to Read Committed.Be One with the OptimizerTG |
|
|
|
Jothikannan
Starting Member
36 Posts |
Posted - 2006-03-06 : 11:04:12
|
| Thanks TG |
|
|
|
|
|
|
ISOLATION LEVEL READ COMMITTED HELP !!!