主页 / dba / 问题 / 10271
Accepted
Oleg Dok
Asked: 2012-01-07 02:34:17 +0800 CST

我锁定是正确的吗？

  • 772

关于这个问题我想问

比较两个查询：

(1)
DELETE dbA.dbo.tableA
FROM dbA.dbo.tableA a WITH(NOLOCK)
JOIN dbB.dbo.tableB b WITH(NOLOCK)
  ON 
      b.colA = a.colA
  AND b.colB = a.colB

和

(2)
DELETE FROM dbA.dbo.tableA 
WHERE EXISTS
(
  SELECT *
  FROM dbB.dbo.tableB b WITH(NOLOCK) 
  where
      b.colA = dbA.dbo.tableA .colA 
  AND b.colB = dbA.dbo.tableA .colB 
)

很明显，如果我们不考虑可能导致脏读的并发写入，查询会做类似的工作。

但我有疑问

我对以下评论是否正确？

请注意，将从语句 (2) 的最开始放置一个共享（可升级？）锁 @ tableA，而不是在连接 (1) 中放置两个 NOLOCK 表，这将放置第一个锁 - UX 锁仅在此之后将找到要删除的第一行

sql-server sql-server-2005

1 个回答

  1. Best Answer

    不。

    至少在我的测试中，它们都具有相同的执行计划和相同的锁定行为。两者都立即放置一个IX锁tableA和一个模式稳定性锁TableB，然后在进行聚簇索引扫描时遵循相同的行为，在页面上TableA获取IU锁，U锁上key，然后在匹配的情况下将页面锁转换为IX然后键锁定到X删除行之前。NOLOCK这与完全删除提示的模式完全相同，tableA因此在这种情况下毫无意义。

    我的原始结果在两个查询之间存在一些差异，因为 Join 版本有一系列明显的获取和立即释放模式稳定性锁，但在该版本TableA中没有出现。EXISTS这似乎与 SSMS 中是否启用了“包括实际执行计划”选项有关。今天，如果我启用了这个选项，这个系列就会出现在他们两个的锁定信息中，所以不确定为什么最初它只出现在一个中。也许一些时间问题。

    您可以使用如下方式查看此锁定信息TF1200（禁用“包括实际执行计划”选项）。

    代码

    SET NOCOUNT ON;

CREATE TABLE tableA(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

CREATE TABLE tableB(
colA INT,
colB INT,
PRIMARY KEY(colA,colB));

SELECT OBJECT_ID('tableA') AS tableA,
       OBJECT_ID('tableB') AS tableB;

INSERT INTO tableA VALUES (0,0),(1,1),(2,2),(3,3);
INSERT INTO tableB VALUES (1,1),(2,2),(4,4),(5,5),(6,6),(7,7);

SELECT *, %%lockres%%  AS lockres
FROM tableA


DECLARE @JoinSQLNoLockTableA nvarchar(max) = N'
DELETE tableA
FROM tableA a WITH(NOLOCK)
JOIN tableB b WITH(NOLOCK)
  ON 
      b.colA = a.colA
  AND b.colB = a.colB;'

DECLARE @ExistsSQL nvarchar(max) = N'
DELETE FROM tableA 
WHERE EXISTS
(
  SELECT *
  FROM tableB b WITH(NOLOCK) 
  where
      b.colA = tableA.colA 
  AND b.colB = tableA.colB 
);'  

DECLARE @JoinSQLWithoutNoLockTableA nvarchar(max) = REPLACE(@JoinSQLNoLockTableA,'tableA a WITH(NOLOCK)', 'tableA a')

/*Run the commands first with the trace flag off so the locking
info is less full of irrelevant stuff about plan compilation */
EXEC (@JoinSQLNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@ExistsSQL);
INSERT INTO tableA VALUES (1,1),(2,2);
EXEC (@JoinSQLWithoutNoLockTableA);
INSERT INTO tableA VALUES (1,1),(2,2);


/*Run with TF1200 on*/

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLNoLockTableA - Start';
EXEC (@JoinSQLNoLockTableA);
PRINT '@JoinSQLNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@ExistsSQL - Start';
EXEC (@ExistsSQL);
PRINT '@ExistsSQL - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

INSERT INTO tableA VALUES (1,1),(2,2);

DBCC TRACEON(-1,3604,1200) WITH NO_INFOMSGS;
PRINT '@JoinSQLWithoutNoLockTableA - Start';
EXEC (@JoinSQLWithoutNoLockTableA);
PRINT '@JoinSQLWithoutNoLockTableA - End';
DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;

DROP TABLE tableA,
           tableB;

    ObjectIds

    tableA      tableB
----------- -----------
308196148   372196376

    锁定资源

    colA        colB        lockres
----------- ----------- ------------------------------
0           0           (00009620dd9a)
1           1           (02006d47cbee)
2           2           (040060eff172)
3           3           (06009b88e706)

    输出（所有三个查询）

    Process 52 acquiring Sch-S lock on OBJECT: 23:372196376:0  (class bit0 ref1) result: OK

Process 52 acquiring IX lock on OBJECT: 23:308196148:0  (class bit2000000 ref1) result: OK

Process 52 acquiring IU lock on PAGE: 23:1:14144 (class bit0 ref1) result: OK

Process 52 acquiring U lock on KEY: 23:72057594051231744 (00009620dd9a) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (00009620dd9a)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit0 ref1) result: OK

Process 52 acquiring IX lock on PAGE: 23:1:14144 (class bit2000000 ref0) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (02006d47cbee) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (02006d47cbee)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (040060eff172) (class bit0 ref1) result: OK

Process 52 acquiring X lock on KEY: 23:72057594051231744 (040060eff172) (class bit2000000 ref0) result: OK

Process 52 releasing lock reference on KEY: 23:72057594051231744 (040060eff172)

Process 52 acquiring U lock on KEY: 23:72057594051231744 (06009b88e706) (class bit0 ref1) result: OK

Process 52 releasing lock on KEY: 23:72057594051231744 (06009b88e706)

Process 52 releasing lock reference on PAGE: 23:1:14144

Process 52 releasing lock on OBJECT: 23:372196376:0
    • 8

相关问题