Início / dba / Perguntas / 12698
Accepted
ErikE
Asked: 2012-02-12 17:06:23 +0800 CST

Gerenciando simultaneidade ao usar o padrão SELECT-UPDATE

  • 772

Digamos que você tenha o seguinte código (ignore que é horrível):

BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the client or used somewhere else

A meu ver, isso NÃO está gerenciando a simultaneidade corretamente. Só porque você tem uma transação não significa que outra pessoa não lerá o mesmo valor que você leu antes de chegar ao seu extrato de atualização.

Agora, deixando o código como está (percebo que isso é melhor tratado como uma única instrução ou ainda melhor usando uma coluna de autoincremento/identidade), quais são as maneiras seguras de fazê-lo lidar com a simultaneidade adequadamente e evitar condições de corrida que permitem que dois clientes obtenham o mesmo valor id?

Tenho certeza de que adicionar um WITH (UPDLOCK, HOLDLOCK)ao SELECT resolverá o problema. O nível de isolamento da transação SERIALIZABLE também parece funcionar, pois nega que qualquer outra pessoa leia o que você fez até que a transferência termine ( ATUALIZAÇÃO : isso é falso. Veja a resposta de Martin). Isso é verdade? Ambos funcionarão igualmente bem? Um é preferido em detrimento do outro?

Imagine fazer algo mais legítimo do que uma atualização de ID - algum cálculo baseado em uma leitura que você precisa atualizar. Pode haver muitas tabelas envolvidas, algumas das quais você escreverá e outras não. Qual é a melhor prática aqui?

Tendo escrito esta pergunta, acho que as dicas de bloqueio são melhores porque você está apenas bloqueando as tabelas de que precisa, mas gostaria da opinião de qualquer pessoa.

PS E não, não sei a melhor resposta e realmente quero entender melhor! :)

sql-server locking

3 respostas

  1. Best Answer

    Apenas abordando o SERIALIZABLEaspecto do nível de isolamento. Sim, isso funcionará, mas com risco de impasse.

    Duas transações poderão ler a linha simultaneamente. Eles não bloquearão um ao outro, pois terão um bloqueio de objeto Sou bloqueios de índice RangeS-Sdependentes da estrutura da tabela e esses bloqueios são compatíveis . Mas eles se bloquearão ao tentar adquirir os bloqueios necessários para a atualização ( IXbloqueio de objeto ou índice, RangeS-Urespectivamente), o que levará a um impasse.

    Em vez disso, o uso de uma dica explícita UPDLOCKserializará as leituras, evitando o risco de impasse.

    • 12

  2. Acho que a melhor abordagem para você seria realmente expor seu módulo a alta simultaneidade e ver por si mesmo. Às vezes, apenas UPDLOCK é suficiente e não há necessidade de HOLDLOCK. Às vezes sp_getapplock funciona muito bem. Eu não faria nenhuma declaração geral aqui - às vezes, adicionar mais um índice, gatilho ou exibição indexada altera o resultado. Precisamos enfatizar o código de teste e ver por nós mesmos caso a caso.

    Eu escrevi vários exemplos de testes de estresse aqui

    Edit: para um melhor conhecimento dos internos, você pode ler os livros de Kalen Delaney. No entanto, os livros podem ficar fora de sincronia como qualquer outra documentação. Além disso, há muitas combinações a serem consideradas: seis níveis de isolamento, muitos tipos de bloqueios, índices clusterizados/não clusterizados e sabe-se lá o que mais. Isso é um monte de combinações. Além disso, o SQL Server é de código fechado, portanto, não podemos baixar o código-fonte, depurá-lo e tal - essa seria a fonte definitiva de conhecimento. Qualquer outra coisa pode estar incompleta ou desatualizada após o próximo lançamento ou service pack.

    Portanto, você não deve decidir o que funciona para o seu sistema sem o seu próprio teste de estresse. Tudo o que você leu pode ajudá-lo a entender o que está acontecendo, mas você deve provar que o conselho que leu funciona para você. Acho que ninguém pode fazer isso por você.

    • 11

  3. Neste caso particular, a adição de um UPDLOCKbloqueio ao de SELECTfato evitaria anomalias. A adição de HOLDLOCKnão é necessária, pois um bloqueio de atualização é mantido durante a transação, mas confesso que o incluí como um hábito (possivelmente ruim) no passado.

    Imagine fazer algo mais legítimo do que uma atualização de ID, algum cálculo baseado em uma leitura que você precisa atualizar. Pode haver muitas tabelas envolvidas, algumas das quais você escreverá e outras não. Qual é a melhor prática aqui?

    Não há melhor prática. Sua escolha de controle de simultaneidade deve ser baseada nos requisitos do aplicativo. Algumas aplicações/transações precisam ser executadas como se tivessem propriedade exclusiva do banco de dados, evitando a todo custo anomalias e imprecisões. Outros aplicativos/transações podem tolerar algum grau de interferência uns dos outros.

    • Recuperar um nível de estoque em faixas (<5, 10+, 50+, 100+) para um produto em uma loja na web = leitura suja (impreciso não importa).
    • Verificar e reduzir o nível de estoque no check-out da loja virtual = leitura repetível (DEVEMOS ter o estoque antes de vender, não DEVEMOS acabar com um nível de estoque negativo).
    • Movimentar dinheiro entre minha conta corrente e poupança no banco = serializável (não calcule mal ou perca meu dinheiro!).

    Editar: o comentário de @AlexKuznetsov me levou a reler a pergunta e remover o erro muito óbvio em minha resposta. Nota para si mesmo sobre postagem noturna.

    • 9

relate perguntas