事务隔离级别范围

发布于 2024-07-25 07:18:05 字数 484 浏览 5 评论 0原文

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

显然，当您使用这样的命令时，范围就开始了：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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

我会在不同的情况下测试它，但我不知道如何读取当前隔离级别的设置。

原文

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

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?

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

温柔一刀 2024-08-01 07:18:06

运行以下命令并亲自查看：

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;

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;

回复收藏 0 原文

婴鹅 2024-08-01 07:18:06

来自 MSDN

如果您在存储过程中发出 SET TRANSACTION ISOLATION LEVEL 或
触发，当对象返回控制时，隔离级别重置
到调用对象时有效的级别。例如，如果
您在批处理中设置 REPEATABLE READ，然后该批处理调用存储的
将隔离级别设置为 SERIALIZABLE 的过程，隔离
当存储过程执行时，级别设置恢复为 REPEATABLE READ
将控制权返回给批处理。