SQL Server Performance Forum – Threads Archive
SET TRANSACTION ISOLATION LEVEL
We are using SQL 2000 Enterprise. We are hosting a 3rd party application that was written to run against many backenbds ans as such uses ansi standard sql. Theus – they did not use (nolock) in their select sp’s. To releive some of the blocking that occures – we want to SET TRANSACTION ISOLATION LEVEL to Read uncommitted for the database. 1) Will this have the same affect as using (nolock) in a select query or sp?2) Do we need to restart sql for this dbcc command to take affect?
3) Besides the possibility of dirty reads (which will not bother this particular app) are there any other possibly detrimental side affects? Thanks
1) Yes.
2) As far I know, no.
3) Idem 3) Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
Luis – Thanks so much for the quick response – we have more questions 1) Will this command affect all spids currently running?
2) On startup – the database seems to revert to read committed. Is there a setting that can set the database to read uncommitted on startup so we do not have to run the dbcc command again after startup? Thanks Again
quote:Originally posted by LuisMartin
1) Yes.
2) As far I know, no.
3) Idem 3) Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
2) As far I know, no.
3) Idem 3) Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
1) I think no.
2) You have to set for each query you have. As far I know is not applicable to all database. But wait for others members oppinions.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
Do you know whether application is using COM+ or not? If the component is the "root" component, then this configured isolation level is the isolation level used when the MSDTC transaction is created. MSDTC will provide the isolation level value to SQL. http://www.awprofessional.com/articles/article.asp?p=26890&seqNum=3
http://www.databasejournal.com/features/mssql/article.php/1442301
http://vyaskn.tripod.com/com_isolation_level.htm
Fyi HTH. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>