Click here to Skip to main content
11,789,512 members (57,972 online)
Rate this: bad
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..



                                     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
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
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
Rate this: bad
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.

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

  Print Answers RSS
0 OriginalGriff 954
1 Maciej Los 765
2 KrunalRohit 666
3 CPallini 606
4 Richard MacCutchan 420

Advertise | Privacy | Mobile
Web02 | 2.8.1509028.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