Início / dba / Perguntas / 111754
Accepted
Andrew Savinykh
Asked: 2015-08-21 13:58:05 +0800 CST

Como evitar um erro de "chave duplicada"?

  • 772

Esta afirmação:

INSERT INTO deleteme
SELECT #t.id, 'test' FROM #t
LEFT JOIN  deleteme  ON deleteme.id = #t.id
WHERE deleteme.id IS NULL;

... falhará com uma violação de chave primária no cenário simultâneo (ou seja, ao inserir a mesma chave que está ausente em deleteme de vários threads ao mesmo tempo) com o nível de isolamento de transação padrão de READ COMMITTED:

Erro: violação da restrição PRIMARY KEY 'PK_DeleteMe'. Não é possível inserir a chave duplicada no objeto 'dbo.deleteme'.

Qual é a melhor forma de prevenir isso?

A tabela fica mais ou menos assim:

CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] PRIMARY KEY ([id] ASC));

Atualizar

Dos comentários:

Por que você tem várias sessões, que obviamente podem extrair o mesmo id de algum lugar, usando a mesma tabela permanente sem ter algum tipo de chave de sessão para diferenciá-las? Mesmo sem um erro duplicado, como você saberá quais linhas pertencem a qual sessão?

Este é um procedimento armazenado que é chamado por um serviço externo que preenche dados nesta tabela. O serviço gera o ID do registro e não garante que não enviará os mesmos dados duas vezes. Ou envie os mesmos dados ao mesmo tempo.

O código do banco de dados deve descartar todos os registros com o mesmo id, se já existir um. O serviço nunca envia dois registros com o mesmo id no mesmo lote.

sql-server sql-server-2014

2 respostas

  1. Best Answer

    Se você realmente precisar executar vários threads ao mesmo tempo, poderá ativar a ignore_dup_keyopção na chave primária.

    Isso apenas dará um aviso em vez de um erro quando uma inserção resultar em uma violação de chave duplicada. Mas, em vez de falhar, ele descartará a(s) linha(s) que, se inseridas, causariam a violação de exclusividade.

    CREATE TABLE [dbo].[DeleteMe](
[id] [uniqueidentifier] NOT NULL,
[Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe] 
PRIMARY KEY ([id] ASC) 
WITH (IGNORE_DUP_KEY = ON));

    Exemplo no sqlfiddle

    Explicação detalhada de Paul White sobre a opção IGNORE_DUP_KEY . Obrigado Paulo.

    • 10

  2. O nome da tabela DeleteMesugere que você está acumulando IDs nesta tabela e, em seguida, exclua periodicamente linhas com esses IDs de alguma outra tabela permanente. É verdade?

    Se for verdade, você pode permitir DeleteMeque haja IDs duplicados. Basta ter um índice não exclusivo em idvez de exclusivo (e como idfaz uniqueidentifiersentido tornar esse índice não clusterizado também):

    CREATE TABLE [dbo].[DeleteMe](
    [id] [uniqueidentifier] NOT NULL,
    [Value] [varchar](max) NULL,
)

CREATE NONCLUSTERED INDEX [IX_ID] ON [dbo].[DeleteMe]
(
    [id] ASC
)

    Se você usa DeleteMepara remover linhas de outra MainTableassim, não importa se idin DeleteMeé exclusivo ou não:

    DELETE FROM MainTable
WHERE MainTable.ID IN (SELECT id FROM DeleteMe)

    BTW, a consulta a ser preenchida DeleteMetambém se torna muito mais simples:

    INSERT INTO DeleteMe (id, Value)
SELECT #t.id, 'test' 
FROM #t
    • 0

relate perguntas