锁定提示与事务隔离级别 [英] Lock Hints Vs Transaction Isolation Level

问题描述

作为专业人员，应该更好地使用
存储过程中的语句或特定查询中的锁定提示".

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] 

推荐答案

我会说使用提示.想象一下，如果存储程序是由某个程序调用的，而该程序正在使用由多个线程在该程序中使用的全局事务对象(我并不是说这是对还是错的方法，我只是看到了它的发生).在存储过程中设置的隔离级别可能会影响另一个线程上的语句.

另外，如果您的存储过程调用RAISERROR，则可能会忘记重新设置隔离级别.

我认为，作为一种好的做法，对于程序中使用的每个事务，应在事务初始化时设置一次隔离级别.

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

我同意安德鲁·切雷德尼克(Andrew Cherednik)的观点.我想确认一下，所以我发布了问题.
实际上，我正在工作的地方是大量事务，并且通过使用隔离级别，有时您会获得带有脏读或五通的数据.但是到目前为止，锁是安全的.

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.

这篇关于锁定提示与事务隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！