事务隔离级别范围 [英] Transaction Isolation Level Scopes

问题描述

SQL Server 2005 中事务隔离级别的范围规则是什么?我知道不同级别的含义，但不知道如何在手动运行脚本之外正确应用它们.我找不到生产质量代码的实际使用指南.

What are the scoping rules for transaction isolation levels in SQL Server 2005? I know what the different levels mean, but not how to properly apply them outside of a manually run script. I can't find a guide for practical use in production-quality code.

显然，当您使用这样的命令时，作用域就开始了:

Obviously, the scope begins when you use a command like this:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

但它在哪里结束?如果我在存储过程中设置 iso 级别，然后该 proc 调用另一个，嵌套的 proc 是否继承它?更好的是，如果我升级嵌套 proc 内的 iso 级别，它是否会重新执行到调用 proc 中?BEGIN TRAN、ROLLBACK 和 COMMIT 等事务命令有什么不同吗?

But where does it end? If I set the iso level in a stored procedure and then that proc calls another, does the nested proc inherit it? Even better, if I escalate the iso level inside the nested proc is it going to carry back out into the calling proc? Do transaction commands like BEGIN TRAN, ROLLBACK, and COMMIT make any differences?

当应用程序或代理作业调用存储过程时，隔离级别更改是否会以某种方式持续存在?我是否总是需要在每个 proc 结束时恢复到默认的 READ COMMITTED?

When a stored proc is called by an application or an agent job do the isolation level changes persist in some way? Do I always have to revert to the default READ COMMITTED at the end of each proc?

我会在不同的情况下对其进行测试，但我不知道如何读取当前的隔离级别设置.

I would test it in different situations but I don't know how to read what the current isolation level is set to.

推荐答案

运行以下命令，亲眼看看:

Run the following and see for yourself:

CREATE PROCEDURE dbo.KeepsIsolation
AS
BEGIN
PRINT 'Inside sproc that does not change isolation level';
DBCC USEROPTIONS;
END
GO

CREATE PROCEDURE dbo.ChangesIsolation
AS
BEGIN
PRINT 'Inside sproc that changes isolation level';
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DBCC USEROPTIONS;
END
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DBCC USEROPTIONS;
EXEC dbo.KeepsIsolation;
DBCC USEROPTIONS;
EXEC dbo.ChangesIsolation;
-- demonstrates that isolation level restored to REPEATABLE READ after exiting the procedure
    DBCC USEROPTIONS;

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