Início / dba / Perguntas / 10271
Accepted
Oleg Dok
Asked: 2012-01-07 02:34:17 +0800 CST

Eu estava certo com o bloqueio?

  • 772

Em relação a esta pergunta que eu quero fazer

Comparando duas consultas:

(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

e

(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 
)

é claro que as consultas fazem um trabalho semelhante, se não considerarmos gravações simultâneas que podem levar a leituras sujas.

MAS eu tenho uma dúvida

eu estava certo com o seguinte comentário?

observe que será colocado um bloqueio compartilhado (atualizável?) @ tableA desde o início da instrução (2), em vez de duas tabelas NOLOCK na junção (1), que colocará o primeiro bloqueio - bloqueio UX somente depois disso será encontrada a primeira linha para deletar

sql-server sql-server-2005

1 respostas

  1. Best Answer

    Não.

    Pelo menos em meus testes, ambos têm planos de execução idênticos e comportamento de bloqueio idêntico. Ambos colocam um IXbloqueio tableAe um bloqueio de estabilidade de esquema TableBimediatamente e, em seguida, seguem o mesmo comportamento ao fazer a verificação de índice clusterizado ao TableAobter IUbloqueios na página, Ubloqueios no key, então, no caso de uma correspondência, converter o bloqueio de página para IXo bloqueio de tecla Xantes de excluir a linha. Este é exatamente o mesmo padrão como se a NOLOCKdica tableAfosse totalmente removida, então é inútil neste caso.

    Meus resultados originais tiveram algumas diferenças entre as duas consultas, pois a versão Join tem uma série de bloqueios de estabilidade de esquema aparentemente adquiridos e liberados imediatamente TableAque não apareceram na EXISTSversão. Isso parece estar relacionado a se a opção "Incluir Plano de Execução Real" está habilitada no SSMS. Hoje, se eu tiver esta opção habilitada, esta série aparece nas informações de bloqueio para ambos, então não sei por que originalmente apareceu apenas em um. Talvez algum problema de tempo.

    Você pode ver essas informações de bloqueio usando TF1200como abaixo (com a opção "Incluir plano de execução real" desativada).

    Código

    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

    LockRes

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

    Saída (todas as três consultas)

    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

relate perguntas