SQL Server 中嵌套事务的隔离范围是什么? [英] What is the scope of isolation in nested transactions in SQL Server?

问题描述

考虑以下 SQL:

<前>开始传输设置事务隔离级别读取已提交插入带( 姓名 )选择Depeche 模式"联盟选择街机火"-- 我缩进了内部事务以使其更清晰.开始传输设置事务隔离级别读取未提交选择 *从乐队犯罪-- 这里的隔离级别是多少?更新乐队SET 名称 = '适度鼠标'WHERE 名称 = '最奇怪的房子'犯罪

总而言之，我们启动一个事务并将其隔离级别设置为READ COMMITTED.然后我们执行一些随机 SQL 并启动另一个嵌套事务.在此事务中，我们将隔离级别更改为 READ UNCOMMITTED.然后我们提交该事务并返回给另一个.

现在，我的猜测是在内部提交之后，隔离级别返回到 READ COMMITTED.这是正确的吗?

解决方案

我认为这不正确.

参考这里的备注:设置事务

<块引用>

只有一种隔离级别选项可以一次设置，并且它保持为该连接设置，直到它被明确更改.

Consider the following SQL:

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

INSERT Bands
    (  Name  )
SELECT 'Depeche Mode'
UNION
SELECT 'Arcade Fire'

    -- I've indented the inner transaction to make it clearer.

    BEGIN TRAN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT *
      FROM Bands

    COMMIT

-- What is the isolation level right here?

UPDATE Bands
   SET Name = 'Modest Mouse'
 WHERE Name = 'Oddest House'

COMMIT

In sum, we start a transaction and set its isolation level to READ COMMITTED. We then do some random SQL and start another, nested transaction. In this transaction we change the isolation level to READ UNCOMMITTED. We then commit that transaction and return to the other.

Now, my guess is that after the inner commit, the isolation level returns to READ COMMITTED. Is this correct?

解决方案

I don't think that is correct.

Refer to the remarks here: Set Transaction

Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed.

这篇关于SQL Server 中嵌套事务的隔离范围是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！