如果从一个存储过程中调用另一个设置较低事务隔离级别的存储过程，可以吗? [英] Is it okay if from within one stored procedure I call another one that sets a lower transaction isolation level?

问题描述

我有一堆实用程序，它们只是检查数据库中的某些条件并返回标志结果.这些过程以READ UNCOMMITTED隔离级别(与WITH NOLOCK等效)运行.

I have a bunch of utility procedures that just check for some conditions in the database and return a flag result. These procedures are run with READ UNCOMMITTED isolation level, equivalent to WITH NOLOCK.

我还具有在SERIALIZABLE隔离级别下运行的更复杂的过程.他们碰巧也有同样的检查.

I also have more complex procedures that are run with SERIALIZABLE isolation level. They also happen to have these same kind of checks in them.

因此，我决定从那些复杂的过程中调用这些检查过程，而不是复制检查代码.

So I decided to call these check procedures from within those complex procedures instead of replicating the check code.

基本上看起来像这样:

CREATE PROCEDURE [dbo].[CheckSomething]
AS
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    BEGIN TRANSACTION

    -- Do checks

    COMMIT TRANSACTION

和

CREATE PROCEDURE [dbo].[DoSomethingImportant]
AS
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN TRANSACTION

    EXECUTE [dbo].[CheckSomething]

    -- Do some work

    COMMIT TRANSACTION

这样做可以吗?临时激活的较低隔离级别会以某种方式破坏较高级别的保护，还是一切都安全?

Would it be okay to do that? Will the temporarily activated lower isolation level somehow break the higher level protection or is everything perfect safe?

执行过程顺利，没有任何错误.

The execution goes smoothly without any errors.

推荐答案

它是所有对于SQL Server 2005，请点击此处.摘录:

当您更改一项交易时 到另一个资源的隔离级别 更改后读取的是 受以下规则保护 新的水平.读取的资源 在更改继续之前 受以下规则保护 前一级.例如，如果 交易已从"READ"更改为 致力于SERIALIZABLE，共享 更改后获得的锁是 现在举行到 交易.

When you change a transaction from one isolation level to another, resources that are read after the change are protected according to the rules of the new level. Resources that are read before the change continue to be protected according to the rules of the previous level. For example, if a transaction changed from READ COMMITTED to SERIALIZABLE, the shared locks acquired after the change are now held until the end of the transaction.

如果您发出SET TRANSACTION ISOLATION 存储过程中的LEVEL或 当对象返回时触发 控制隔离级别重置 达到对象的有效水平 被调用.例如，如果您设置 批量可重复读取，并且 然后批处理调用存储过程 将隔离级别设置为 可SERIALIZABLE，隔离级别 设置恢复为REPEATABLE READ 当存储过程返回时 控制到批次.

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

在此示例中:

• 每个隔离级别都适用于所存储proc的范围
• 被DoSomethingImportant锁定的资源保留在SERIALIZABLE下
• CheckSomething使用的资源是读未提交"

这篇关于如果从一个存储过程中调用另一个设置较低事务隔离级别的存储过程，可以吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！