sql-server - 如果我从一个存储过程中调用另一个设置较低事务隔离级别的存储过程可以吗？

Question

我有一堆实用程序，它们只检查数据库中的某些条件并返回标志结果。这些过程以 READ UNCOMMITTED 隔离级别运行，相当于 WITH NOLOCK。

我还有更复杂的程序，它们以 SERIALIZABLE 隔离级别运行。他们也恰好有这些相同类型的支票。

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

基本上它看起来像这样:

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

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

编辑:执行顺利，没有任何错误。

Answer 1

是all here对于 SQL Server 2005。一个片段:

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.

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.

在这个例子中:

• 每个隔离级别适用于存储过程的范围
• DoSomethingImportant 锁定的资源保持在 SERIALIZABLE 下
• CheckSomething 使用的资源是未提交的 READ