SQL Server 2005的事务级和存储过程 [英] SQL Server 2005 Transaction Level and Stored Procedures
问题描述
如果我用命令事务隔离级别设置读取未提交
,然后使用执行存储过程的 EXEC storedProcedureName
在同样的情况下,将在存储过程中使用的事务级表示previously或将使用默认的?
If I use the command SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
and then execute a stored procedure using the EXEC storedProcedureName
on the same context, will the stored procedure use the transaction level stated previously or will use a default one?
如果我想迫使每一个存储过程在事务级用我必须包括C型相同的语句$ C $的顶部(事务隔离级别设置读取未提交
)?
If I want to force every stored procedure to use on transaction level do I have to include at the top of the code the same statement (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
)?
PS:该系统是建立在.NET 2.0和第三方产品的限制,这些替代方法因此需要之上。
PS.: the system is built on top of .NET 2.0 and proprietary third party products with limitations, hence the need of these workarounds.
推荐答案
该存储过程调用时会使用的事务隔离的效果。
The stored procedure will use the transaction isolation in effect when called.
如果存储过程本身设置一个明确的隔离级别,这将是复位时,存储过程退出。
If the stored procedure itself sets an explicit isolation level this will be reset when the stored procedure exits.
(编辑:刚才检查,这是相反的是BOL说......它仍然是该连接设置,直到它被明确地改变,但可以从下面可以看到)
( Just checked and this is contrary to what BOL says "... it remains set for that connection until it is explicitly changed" but can be seen from the below)
CREATE PROC CheckTransLevel
AS
DECLARE @Result varchar(20)
SELECT @Result = CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'Readcomitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
PRINT @Result
GO
CREATE PROC SetRCTransLevel
AS
PRINT 'Enter: SetRCTransLevel'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
EXEC CheckTransLevel
PRINT 'Exit: SetRCTransLevel'
GO
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
EXEC CheckTransLevel
EXEC SetRCTransLevel
EXEC CheckTransLevel
结果
ReadUncomitted
Enter: SetRCTransLevel
Readcomitted
Exit: SetRCTransLevel
ReadUncomitted
这篇关于SQL Server 2005的事务级和存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!