Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
As a professional, Which should be better to use
statement in stored procedure or Lock Hints in particular query..
 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
OR
 
SELECT DISTINCT    
                                            T.CompanyID    
                                     FROM   #Targetlist AS T    
                                            INNER JOIN new.CompanyIndex AS CI2 WITH (NOLOCK) ON [T].[CompanyID] = CI2.CompanyID    
                                     WHERE  [CriteriaID] = @CriteriaID    
                                            AND ( @includeNonMain = 1    
                                                  OR isMain = 1    
                                                )    
                                            AND [SegmentNo] = @SegmentNo    
                                   ) AS CI ON UD.[CompanyID] = CI.CompanyID    
                WHERE   UD.[LocalIdentifierID] = @LocalIdentifierID    
                        AND [UD].[IsActive] = 1    
                ORDER BY UD.[CompanyID] 
Posted 17-Oct-12 0:27am
Edited 17-Oct-12 0:28am
v3
Comments
djj55 at 17-Oct-12 11:32am
   
Remember they are similar but not the same. We had used SET TRANSACTION but it caused some problems (sorry do not remember - something to do with persistent connections) and now use the NOLOCK hint. I am no expert thus the comment instead of a solution.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I'd say use the hints. Imagine if the stored procedure is being called by a program that is using a global transaction object used across the program by several threads (I am not saying that it is a right or wrong way to do, I just saw it happen). The isolation level set in your stored procedure can affect a statement at the other thread.
 
Also, if your stored procedure calls RAISERROR you may forget to re-set the isolation level back.
 
I think, as a good practice, you should set the isolation level once on the transaction initialisation for each transaction used in your program
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I agreed Andrew Cherednik. just i want to confirm so i posted the question.
Actually where i am working is a large transaction traffic and by using isolation level, sometimes u get data with dirty reads or penthoms. But as far locks are safe.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 130
4 DamithSL 95
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100