主页 / dba / 问题 / 147624
Accepted
BVernon
Asked: 2016-08-24 08:49:09 +0800 CST

设置事务隔离级别的更新语句读取未提交

  • 772

如果我有一个带有 的存储过程set transaction isolation level read uncommitted，它会影响更新语句吗？

我知道您不应该with (nolock)在更新/删除语句上使用它，这几乎是相同的事情，但不确定 SQL 是否会在过程中的更新语句上忽略它，或者如果有更新语句，我是否应该小心不要使用它。

编辑：

对困惑感到抱歉。我并不是想弄清楚在操作语句上使用这种类型的锁定会产生什么影响，或者它是否是一个好主意。事实上，我不想在操作语句上使用这种锁定，所以我的问题是，是否将“设置事务...”放在我的存储过程的顶部，是否会受到更新/删除语句的尊重，或者是否它将被忽略。我希望它只是被忽略。

我完全了解它将对 select 语句产生的影响（及其优缺点）。

sql-server locking

3 个回答

  1. 如果我有一个未提交的设置事务隔离级别的存储过程，它会影响更新语句吗？

    未提交读允许脏读。X在变为脏之前，将对行或更高级别（在数据页或索引中）进行锁定。在定位要更新的行时，查询直接访问的行将被U锁定并被阻塞。

    但是，更新仍然可能受到隔离级别的影响。

    连接 1

    CREATE TABLE T1
(
X INT NULL,
Y INT NULL
);

INSERT INTO T1 DEFAULT VALUES;

BEGIN TRAN
UPDATE T1 SET X = 100;    

WAITFOR DELAY '00:00:10'

ROLLBACK;

    连接 2（在启动连接 1 后 10 秒内运行此程序）

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

UPDATE T1
SET    Y = (SELECT SUM(X)
            FROM   T1);

SELECT *
FROM   T1;

    结果

    在此处输入图像描述

    读取操作读取未提交的值，并且最终结果被使用，UPDATE即使100读取的最终被回滚。

    • 5
  2. Best Answer

    “事务隔离级别”主要影响（在我的理解中）读操作的行为，即读操作是否会发出一些锁。在“读取未提交”的情况下，这是来自MSDN的引用

    在 READ UNCOMMITTED 级别运行的事务不会发出共享锁，以防止其他事务修改当前事务读取的数据。READ UNCOMMITTED 事务也不会被排他锁阻塞，排他锁会阻止当前事务读取已修改但未由其他事务提交的行

    因此，对于您的问题，答案是否定的，更新不会受到同一存储过程中“读取未提交”事务隔离级别的影响。

    -- 更新（一个例子来证明这个逻辑）在SSMS中，我们打开两个窗口，在窗口1（以下称为W1）中，运行以下命令

    use tempdb
create table dbo.t (a int)
go
insert into dbo.t (a) values (10)
go
begin tran
update dbo.t 
set a = 20
where a = 10

-- commit tran

    在另一个窗口 (W2) 中，运行以下命令（请参阅行为注释）

    use TempdB
set transaction isolation level read uncommitted
select * from dbo.t -- you can have dirty read, showing [a]=20, which is an uncommitted UPDATE in W1

go
-- the following update will wait (before proper locks are granted)
update dbo.t 
set a = 30
where a= 10

    这意味着 W2 中的 UPDATE 语句（带有 READ UNCOMMITTED）不会像 SELECT 语句那样受事务隔离级别的影响（即仍按预期运行）。

    -- 更新 2：根据 MSDN更新t-sql，

    WITH (Table_Hint_Limited)

    指定目标表允许的一个或多个表提示。WITH 关键字和括号是必需的。不允许NOLOCK 和 READUNCOMMITTED 。有关表提示的信息，请参阅表提示 (Transact-SQL)。

    所以我的理解是，当您在 SQL Server 中运行 UPDATE 语句时，即使您可以在会话中读取脏数据，也无法更新脏数据（即未提交的数据）。

    • 4

  3. 根据您的编辑，现在问题更清楚了..

    所以我的问题是，将“set transaction ...”放在我的存储过程的顶部是否会受到更新/删除语句的尊重，或者是否会被忽略。

    事务隔离级别应该从读操作的角度来考虑。隔离级别控制如何保护读取操作免受其他写入操作的影响。

    数据库引擎控制所有写入操作的锁定行为，您无法在数据库级别更改该行为。

    来自BOL：

    所有隔离级别总是为写操作发出独占锁，并在整个事务期间持有锁。

    阅读：

    • 3

相关问题