Início / dba / Perguntas / 58001
Accepted
Ian Boyd
Asked: 2014-02-01 08:08:27 +0800 CST

No isolamento READ COMMITTED, é possível ler o valor antigo após a atualização?

  • 772

Estou tentando resolver um problema com o SQL Server 2008 R2 e estou me agarrando às palhas.

Dado que os dados estão alojados em nós de folha da árvore B e também existem em índices, alguém poderia ler vários valores durante uma única UPDATEinstrução?

Imagine que um processo atualize uma linha:

--Change status from Pending -> Waiting
BEGIN TRANSACTION
   UPDATE Transactions SET Status = 'Waiting'
   WHERE TransactionID = 12345
COMMIT TRANSACTION

É possível que durante esse BEGIN TRANS; UPDATE; COMMITprocesso outro processo possa ler valores novos e antigos?

Eu me pergunto isso porque a atualização não altera um valor em apenas um lugar. Esse valor existirá em vários lugares:

  • nós folha do índice clusterizado
  • IX_Transações_1
  • IX_Transações_2
  • IX_Transações_3
  • IX_Transações_4
  • IX_Transações_5
  • ...
  • IX_Transações_n

Se a atualização começar, ela deverá atualizar os valores em todos esses locais. O que acontece se outro processo usar um plano de execução de índice para encontrar o valor:

  • IX_Clustered : Aguardando Pendente
  • IX_Transactions_1 : Pendente Aguardando
  • IX_Transactions_2 : Pendente Aguardando
  • IX_Transactions_3 : Pendente
  • IX_Transactions_4 : Pendente
  • IX_Transactions_5 : Pendente
  • ...
  • IX_Transactions_n : Pendente

Nesse instante, se for emitida uma consulta que examine o valor no índice clusterizado, ela encontrará Waiting .

Mas se uma consulta usar IX_Transactions_4, ela encontrará um valor de Pending.

Desdocumentando o mecanismo de bloqueio

A ordem de bloqueio internamente não é documentada, mas presumo que o SQL Server tenha um bloqueio compartilhado nos índices clusterizados e não clusterizados:

  • IX_Clustered : Compartilhado
  • IX_Transactions_1 : Compartilhado
  • IX_Transactions_2 : Compartilhado
  • IX_Transactions_3 : Compartilhado
  • IX_Transactions_4 : Compartilhado
  • IX_Transactions_5 : Compartilhado

Em seguida, atualiza esses bloqueios para bloqueios de atualização:

  • IX_Clustered : atualização compartilhada
  • IX_Transactions_1 : atualização compartilhada
  • IX_Transactions_2 : atualização compartilhada
  • IX_Transactions_3 : atualização compartilhada
  • IX_Transactions_4 : atualização compartilhada
  • IX_Transactions_5 : atualização compartilhada

neste ponto, outro processo ainda pode ler os valores desses 5 índices; porque os selects são compatíveis com os bloqueios de atualização.

Em seguida, a atualização continua. Se tirarmos uma foto em um instante no tempo:

  • IX_Clustered : atualização compartilhada exclusiva -> valor alterado
  • IX_Transactions_1 : atualização compartilhada exclusiva -> valor alterado
  • IX_Transactions_2 : atualização compartilhada exclusiva
  • IX_Transactions_3 : atualização compartilhada
  • IX_Transactions_4 : atualização compartilhada
  • IX_Transactions_5 : atualização compartilhada

Outro processo não pode mais ler valores de itens com um bloqueio exclusivo , mas ainda pode ler valores de itens que ainda possuem o bloqueio de atualização (os bloqueios compartilhados são compatíveis com os bloqueios de atualização )

A menos é claro que não é isso que acontece

Isso só funciona se o SQL promover apenas para exclusivo conforme necessário. Se, em vez disso, for:

  • IX_Clustered : S U IX X -> valor alterado
  • IX_Transactions_1 : S U IX X -> valor alterado
  • IX_Transactions_2 : S U IX X
  • IX_Transactions_3 : S U IX
  • IX_Transactions_4 : S U IX
  • IX_Transactions_5 : S U IX

E não consigo mais digitar; minha vontade de continuar é esmagada. Como eu disse, estou me agarrando em palhas.

sql-server-2008-r2

1 respostas

  1. Best Answer

    Você pode usar o sinalizador de rastreamento 1200para imprimir informações de bloqueio e informações 3916de registro. (Ambos AFAIK não documentados)

    Para a seguinte configuração de teste

    CREATE TABLE T
(
A INT IDENTITY PRIMARY KEY,
B INT
)

CREATE NONCLUSTERED INDEX IX ON T(B)

INSERT INTO T 
SELECT number
FROM master..spt_values

    Em seguida, execute o seguinte duas vezes (já que a segunda execução não contém os bloqueios irrelevantes adquiridos durante a compilação)

    DBCC TRACEON(3604,1200,3916,-1) WITH NO_INFOMSGS

SET NOCOUNT ON;

UPDATE T 
SET B += 1
WHERE A = 1000

/*If you habitually have 3604 on then change the below*/
DBCC TRACEOFF(3604,1200,3916,-1) WITH NO_INFOMSGS

    Isso retorna a seguinte saída

    Processo 54 adquirindo bloqueio IX em OBJECT: 11:245575913:0 (class bit2000000 ref1) resultado: OK

    Processo 54 adquirindo bloqueio IU na PÁGINA: 11:1:127 (class bit0 ref1) resultado: OK

    Processo 54 adquirindo U lock na KEY: 11:72057594039042048 (1f00de11a529) (class bit0 ref1) resultado: OK

    Processo 54 adquirindo bloqueio IX na PÁGINA: 11:1:127 (classe bit2000000 ref0) resultado: OK

    Processo 54 adquirindo X lock na KEY: 11:72057594039042048 (1f00de11a529) (class bit2000000 ref0) resultado: OK

    XdesId 0000:00000423: m_logReservado 9162, delta 9162, op LOP_BEGIN_XACT, chamador GenerateLogRec, LSN 00000027:00000159:0001.

    XdesId 0000:00000423: m_logReservado 9379, delta 217, op LOP_MODIFY_ROW, chamador GenerateLogRec, LSN 00000027:00000159:0002.

    Processo 54 adquirindo bloqueio IX na PÁGINA: 11:1:166 (classe bit2000000 ref1) resultado: OK

    Processo 54 adquirindo bloqueio X na CHAVE: 11:72057594039107584 (dbf572a551f4) (classe bit2000000 ref1) resultado: OK

    XdesId 0000:00000423: m_logReservado 9597, delta 218, op LOP_DELETE_ROWS, chamador GenerateLogRec, LSN 00000027:00000159:0003.

    Processo 54 adquirindo bloqueio IX na PÁGINA: 11:1:166 (classe bit2000000 ref0) resultado: OK

    Processo 54 adquirindo o bloqueio RangeI-N na CHAVE: 11:72057594039107584 (a39688da7d04) (classe bit1000000 ref1) resultado: OK

    Processo 54 adquirindo bloqueio X na CHAVE: 11:72057594039107584 (82cbfa4b30c1) (classe bit2000000 ref0) resultado: OK

    XdesId 0000:00000423: m_logReservado 9671, delta 74, op LOP_INSERT_ROWS, chamador GenerateLogRec, LSN 00000027:00000159:0004.

    Processo 54 liberando referência de bloqueio na CHAVE: 11:72057594039107584 (dbf572a551f4)

    Processo 54 liberando referência de bloqueio na PÁGINA: 11:1:166

    Processo 54 liberando referência de bloqueio na CHAVE: 11:72057594039042048 (1f00de11a529)

    Processo 54 liberando referência de bloqueio na PÁGINA: 11:1:127

    XdesId 0000:00000423: m_logReservado 0, delta -9671, op LOP_COMMIT_XACT, chamador GenerateLogRec, LSN 00000027:00000159:0005.

    Nenhum bloqueio é feito no índice não clusterizado ( 1:166) até depois da LOP_MODIFY_ROWatualização na chave do índice clusterizado (na página 1:127), então sim, isso seria possível.

    E o acima é com um plano de atualização estreito (por linha).

    Com um plano amplo (por índice), cada índice é atualizado por vez. Possivelmente com operações de classificação intermediárias, como no plano abaixo.

    insira a descrição da imagem aqui

    Isso tornaria a janela de oportunidade mais ampla, mas, enquanto uma transação poderia potencialmente ler o valor "antigo" de um índice ainda a ser atualizado, não seria possível para uma transação confirmada de leitura ler a "nova" versão do valor até a transação foi confirmada.

    É certamente possível que uma instrução read commited leia duas versões diferentes do mesmo valor.

    Em uma corrida de conexão

    WHILE ( 1 = 1 )
  BEGIN
      UPDATE T
      SET    B += 1
      WHERE  A = 1000;
  END

    E então em outra corrida

    SELECT *
FROM   T T1 WITH(READCOMMITTEDLOCK )
       INNER HASH JOIN T T2  WITH(READCOMMITTEDLOCK )
         ON T1.A + 0 = T2.A + 0
WHERE  T1.A = 1000
       AND T2.A = 1000

    Para mim, cerca de 50% das vezes, essa segunda consulta retorna uma linha com Bvalores diferentes conforme o valor muda entre as duas buscas em T.

    • 6

relate perguntas