Início / dba / Perguntas / 58425
Accepted
deadLock
Asked: 2014-02-07 23:40:24 +0800 CST

Como evitar uma condição de corrida com chamadas de proc cross db? e outras preocupações

  • 772

Nem tenho certeza se essa pergunta é necessária, mas estou curioso para saber a opinião de todos. Tenho dois bancos de dados no mesmo servidor, dbFoo, dbBar. dbFoo tem a tabela a seguir, observe que este é um exemplo simplificado e a sintaxe pode não estar correta, pois estou com pressa e muito mais interessado na resposta para o problema subjacente do que no código para fazê-lo ...

CREATE TABLE dbo.CodeNumbers(
CodeNumbersID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
CodeValue VARCHAR(30) NOT NULL
IsUsed BIT NOT NULL DEFAULT(0)
);

dbo.CodeNumbersé preenchido com um CSV mensal fornecido, o método de importação de sua escolha já está escrito para colocá-los lá. NUNCA recebemos um código duplicado.

Vamos supor, por causa dos argumentos, que temos 10.000.000 linhas na tabela. que todos seguem este formato quando importados:

1, 'ajdirjfisofklrlfo039402', 0 all the way till
10000000, 'fkeiir9489', 0

Agora em dbBar eu tenho 2 procedimentos armazenados, o primeiro deve acessar o primeiro código não usado em dbFoo, retorná-lo em uma variável de saída e marcá-lo como usado. Então eu tenho algo como:

CREATE PROCEDURE GetNextUseableCode
   @CodeOut VARCHAR(30) OUTPUT,
   @CID INT OUTPUT
AS
   SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
   FROM dbFoo.dbo.CodeNumbers
   WHERE IsUsed = 0

   UPDATE dbFoo.dbo.CodeNumbers
   SET IsUsed = 1
   WHERE CodeNumbersID = @CID

O código que chama o procedimento de dbBar é acessado por 200k sessões por dia em vários horários. Quando dbFoo.Codesnão tem mais para retornar, está tudo bem, está tudo bem, o aplicativo é simplesmente informado, desculpe, não volte mais amanhã.

Tenho 3 perguntas principais..

  1. Existe algo especial que eu precisaria ter no código para evitar condições de corrida e, em caso afirmativo, o que seria melhor para lidar com isso sem deixar o sistema de joelhos.

  2. É uma maneira eficiente de garantir que o próximo código obtido sempre que o procedimento for chamado, seja o próximo em ordem cronológica na coluna de ID.

  3. Existe alguma outra preocupação que não estou analisando agora que possa gerar grandes problemas e qual seria uma maneira eloqüente de lidar com essa situação?

Entendo que esta é uma pergunta longa e bastante aberta, tenho algumas soluções codificadas, mas sinto que há maneiras muito melhores de obter os resultados que desejo.

Desde já agradeço como sempre por toda a ajuda.

sql-server sql-server-2012

1 respostas

  1. Best Answer

    Não há nada em você SELECTque dite a ordem. Também não está protegido de duas sessões lendo a mesma linha. Para ver que não é seguro:

    1. Crie uma tabela descartável com uma coluna chave.

      CREATE TABLE dbo.GeneratedIDs(ID INT PRIMARY KEY);

    2. Execute este código em um loop de duas sessões diferentes:

      SET NOCOUNT ON;

DECLARE @DECLARE @CID INT, @CodeOUt VARCHAR(30);

SELECT @CID = CodeNumbersID, @CodeOut = CodeValue
  FROM dbFoo.dbo.CodeNumbers
  WHERE IsUsed = 0

UPDATE dbFoo.dbo.CodeNumbers
  SET IsUsed = 1
  WHERE CodeNumbersID = @CID 

INSERT dbo.GeneratedIDs SELECT @CID;

GO 100000

    3. Verifique a saída para estes - eles acontecerão:

      Msg 2627, Nível 14, Estado 1
      Violação da restrição PRIMARY KEY 'PK_hexcode'. Não é possível inserir chave duplicada no objeto 'dbo.GeneratedIDs'. O valor da chave duplicada é (<algum valor>).

      Ou você pode ver impasses se agrupar o SELECT/ UPDATEem uma transação explícita:

      Msg 1205, nível 13, estado 52 A
      transação (ID do processo 57) foi bloqueada em recursos de bloqueio com outro processo e foi escolhida como a vítima do bloqueio. Execute novamente a transação.

    Para contornar esse problema e garantir que o ID obtido seja o menor disponível, faça o seguinte:

    BEGIN TRANSACTION;

SELECT TOP (1) @CID = ...
FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
WHERE IsUsed = 0
ORDER BY CodeNumbersID;

UPDATE ...

COMMIT TRANSACTION;

    Observe que adicionei uma transação explícita e também XLOCK/ HOLDLOCKhints para impedir que duas sessões simultâneas leiam a mesma linha. Claro, isso tem um impacto na simultaneidade (que, infelizmente, é exatamente o que você quer e precisa aqui).

    Outras maneiras de fazer isso incluem apenas atualizar a linha e usar uma variável de tabela para capturar valores da OUTPUTcláusula:

    DECLARE @x TABLE(CodeOut VARCHAR(30), CID INT);

;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x SET IsUsed = 1
  OUTPUT inserted.CodeValue, inserted.CodeNumbersID INTO @x;

SELECT @CodeOut = CodeOut, @CID = CID FROM @x;

    Pela atualização de Paul, sim, você também pode fazer isso sem a variável de tabela:

    ;WITH x AS 
(
  SELECT TOP (1) CodeNumbersID, CodeValue, IsUsed
    FROM dbFoo.dbo.CodeNumbers WITH (XLOCK, HOLDLOCK)
    WHERE IsUsed = 0
    ORDER BY CodeNumbersID
)
UPDATE x 
  SET @CodeOut = x.CodeValue, @CID = x.CodeNumbersID, IsUsed = 1;

    (Embora eu não goste muito dessa sintaxe; não sei por quê. Pode ser a mesma razão pela qual sempre esqueço que ela existe.)

    Você pode alterar o chamador para esperar um conjunto de resultados em vez de dois parâmetros de saída, mas isso também é um trabalho extra. Em ambos os casos, você ainda precisa garantir que obteve o ID mais baixo disponível, o que provavelmente significa um CTE com SELECTas mesmas dicas. Alguma discussão aqui . Também falo sobre uma abordagem semelhante nesta postagem do blog, mas não entrei em nada sobre simultaneidade e duas sessões tentando excluir a mesma linha ao mesmo tempo. Obviamente, nesse caso, apenas um deles pode vencer, mas com um UPDATEambos podem conseguir ter sucesso (pelo menos em teoria).

    Para facilitar as coisas, você pode relaxar a restrição de que o "próximo" ID distribuído é o menor ID disponível. Mas você ainda precisa do isolamento por meio das dicas para garantir que duas sessões simultâneas não leiam o mesmo valor (o que pode acontecer independentemente da ordem). Esperançosamente, com um índice adequado, eles não destruirão a simultaneidade.

    • 9

relate perguntas