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 |
|
rammohan
Posting Yak Master
212 Posts |
 Posted - 2010-04-08 : 03:09:10
|
| Hi All,based on requirement for my current project,we r developing sp's now. i need some suggestion's here.most of scenarios we r developing two types of sp's,1. contains only select command(but have more joins from many tables)2. first select command will pull data,based on condition we will update a couple of rows values of same table.in this scenario, in order to get rid of deadlock n contention problems in future i.e after production, what type of care should i have to taken?please suggest me best transaction isolation level at this stage.(data volume is more high in these tables). my primary aim is to get rid of deadlock situation.u r suggestions r more valuable for me.thanks in advance,rammohanOne can never consent to creep,when one feels an impulse to soarRAMMOHAN |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 03:41:01
|
| "u r suggestions r more valuable for me."First suggestion: Please do not use "text-speak" on this forumMy recommendation would be that you upgrade to SQL2005 / SQL2008 and make use of READ_COMMITTED_SNAPSHOT. This is really a painless solution to the problem of reads blocking writes, and was the reason for many large SQL users to upgrade to SQL2005.For (2) you need a Transaction Block - we do that ALWAYS in any stored procedure that does an UPDATE or DELETE - even if it is only a single statement. It ensures that if we add more Update/Delete statements that we don't forget to add the Transaction Block later, and it provides isolation if our Sproc is itself called from an outer Sproc that also does Updates/Deleted. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-08 : 03:42:36
|
| P.S. SQL2005 will also provide you with the OUTPUT clause - which may enable you to re-use data from one action into another (as a single ATOMic statement) rather than having to rely on locking. |
|
|
|
rammohan
Posting Yak Master
212 Posts |
Posted - 2010-04-08 : 04:47:38
|
| hi kristen,Thanks for u r reply.More strictly, we have to use sqlserver 2000 only. in order to achieve this in this version, i am planning to use transaction block in evry sp we r going to written. in isolation wise, we r thinking to use " READ COMMITTED" for every transaction in sp irrespective of commands it is going to be used. is it will be good we go with above isolation level to get ride of the deadlocks? please suggest meOne can never consent to creep,when one feels an impulse to soarRAMMOHAN |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-04-09 : 07:33:28
|
| rammohan...."slow down" and read and "act on" the suggestion: "Please do not use "text-speak" on this forum""Thanks for u r reply"???You've posted enough questions/replies here to understand the etiquette of the forum. Text-speak is for 12-16 year old kids or for gsm phone messages. adults use the full and proper form of words. |
|
|
|
|
|
|
|
|
Need Suggestion Reg Isloation Level