Click here to Skip to main content
11,636,778 members (77,685 online)
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 16-Oct-12 23:27pm
Edited 16-Oct-12 23:28pm
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 jyo.net 263
1 stibee 249
2 Sergey Alexandrovich Kryukov 229
3 Suvendu Shekhar Giri 225
4 OriginalGriff 183
0 OriginalGriff 8,941
1 Sergey Alexandrovich Kryukov 8,493
2 Mika Wendelius 6,909
3 F-ES Sitecore 2,388
4 Suvendu Shekhar Giri 2,320


Advertise | Privacy | Mobile
Web01 | 2.8.150728.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2015
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