none
DELETE/INSERT quando é efetuado update com mais de uma linha em Banco de dados replicados RRS feed

  • Pergunta

  • Boa tarde!

    Tenho uma estrtura de banco de dados replicados por transação push.

    Quando eu executo um update, com alteração em mais de uma linha, no banco de dados publicador ocorre um erro na replicação.

    Segundo o que eu vi neste link: http://support.microsoft.com/kb/238254/en-us este padrão de delete/insert é executado quando mais de uma linha é alterada no update.

    Como minha tabela é utilizando por outras tabelas, ocorre o erro de replicado, pois dá erro de violação de FK.

    Gostaria de saber se tem alguma forma de eu alterar este padrão no SQL SERVER 2008 para não dar delete/insert e sim update como é o caso de quando o update só afeta uma linha.

    Obrigado.

    • Movido Gustavo Maia Aguiar sexta-feira, 2 de julho de 2010 20:12 (De:SQL Server - Desenvolvimento Geral)
    sexta-feira, 2 de julho de 2010 18:56

Respostas

  • Fábio,

    Eu montei todo ambiente no SQL Server 2008 Enterprise, trabalhando com Replicação Transacional, com a estrutura das tabelas que você indicou, bem como, também com base em um banco que utilizo, e pude identificar justamente este comportamento do SQL Server, realizei todos o monitoramento com base no SQL Server Profiler!!!!

    E quando realizei o update com uma linha não ocorreu o Delete/Insert, mas com várias linhas o SQL Server realiza este processo com base na coluna que ele utiliza para controle dos registros que estão sendo replicados, desta forma, ele poderá evitar quando inconsistência ou ambiguidade de dados.

    Utilizando o Profiler ficou bem visível este comportamento e foi possível entender.

    No SQL Server 2000 ocorre um comportamento similar, no SQL Server 2005 e 2008 esta mais fácil de identificar e entender este comportamento, ainda mais se você ativar o Replicator Monitor.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    sexta-feira, 9 de julho de 2010 17:56
    Moderador

Todas as Respostas

  • Fábio,

    Eu não consegui entender, você esta tendo problemas em sua replicação quando realiza o Update em mais de uma linha de registros?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    sexta-feira, 2 de julho de 2010 23:14
    Moderador
  • Isso mesmo.

    Quando eu executo um update no publicador que afeta mais de uma linha, esse update é enviado para os assinantes. Mas em vez de fazer update, a replicação faz um delete/insert. Como minha tabela que estou fazendo update é referenciada por outras tabelas, da erro na replicacão.

     

    sexta-feira, 2 de julho de 2010 23:49
  • Fábio,

    Você deverá verificar as propriedades da sua replicação, inclusive sobre as tabelas envolvidas na replicação, pois possivelmente você deve ter configurado para que os dados não sejam alterados mas sim sempre renovados, excluídos e depois inseridos.

     


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    sábado, 3 de julho de 2010 20:27
    Moderador
  • Bom dia!

    Junior,

    Você sabe onde fica está configuração, pois não encontrei.

    Obrigado!

    T+

    segunda-feira, 5 de julho de 2010 13:26
  • Fábio,

    Acredito que você deverá verificar as propriedades dos artigos que estão envolvidos na sua replicação.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    terça-feira, 6 de julho de 2010 23:20
    Moderador
  • Bom dia!

     

    Junior,

    Desculpe minha persistência, mas eu não achei nada no artigo da tabela. Coloquei no sendspace as imagens da propriedade do artigo da tabela, que se encontra neste link: http://www.sendspace.com/file/sxcr2x

    Obrigado pela atenção!

     

    quarta-feira, 7 de julho de 2010 12:34
  • Fábio,

    Baixei as imagens que você publicou no sendspace, a principio também não encontrei nada.

    Acredito que terei que tentar simular o seu ambiente para encontrar alguma coisa.

    Que tipo de replicação transacional você escolhe?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    quarta-feira, 7 de julho de 2010 17:34
    Moderador
  • Boa tarde!

    Eu utilizo o transaction replication e push subscription.

    Neste link http://support.microsoft.com/kb/238254/en-us é comentado que este é o padrão do sql server quando ocorre update que afeta mais de uma linha, mas até a versão 2005. Pensei que na versão 2008 poderia ter alguma melhoria em relação a isso.

    Pra vc simular, basta criar duas tabelas, TabelaPai, TabelaFilha, onde a TabelaPai referência a TabelaFilha, e execute um update que afete mais de uma linha na TabelaFilha. A replicação irá acionar o delete e insert, mas como a TabelaPai tem referência para a Filha vai dar erro de FK no log da replicação.

    Eu ativei o Profile e vi que a replicação aciona o delete quando afeta mais de uma linha. Alterei o update para afetar somente uma linha, ai a replicação envio um update mesmo.

    Obrigado!

    T+

    quarta-feira, 7 de julho de 2010 20:19
  • Fábio,

    Eu montei todo ambiente no SQL Server 2008 Enterprise, trabalhando com Replicação Transacional, com a estrutura das tabelas que você indicou, bem como, também com base em um banco que utilizo, e pude identificar justamente este comportamento do SQL Server, realizei todos o monitoramento com base no SQL Server Profiler!!!!

    E quando realizei o update com uma linha não ocorreu o Delete/Insert, mas com várias linhas o SQL Server realiza este processo com base na coluna que ele utiliza para controle dos registros que estão sendo replicados, desta forma, ele poderá evitar quando inconsistência ou ambiguidade de dados.

    Utilizando o Profiler ficou bem visível este comportamento e foi possível entender.

    No SQL Server 2000 ocorre um comportamento similar, no SQL Server 2005 e 2008 esta mais fácil de identificar e entender este comportamento, ainda mais se você ativar o Replicator Monitor.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    sexta-feira, 9 de julho de 2010 17:56
    Moderador
  • Boa tarde!

    Muito obrigado pela resposta.

    Então, foi o que eu notei também. Eu pensava que poderia ter alguma forma de alterar este padrão. Mas pelo jeito não tem mesmo.

    Novamente muito obrigado.

    T+

    sexta-feira, 9 de julho de 2010 19:03
  • Fábio,

    Acredito que não tenha a possibilidade de alterar este comportamento, mas vou realizar mais alguns testes e conversar com alguns amigos sobre isso, se tiver alguma novidade eu retorno.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    sexta-feira, 9 de julho de 2010 19:25
    Moderador
  • Bom Dia,

    Esse é um comportamento conhecido do SQL Server (desde o 2000 no mínimo). É possível que ele reescreva operações de UPDATE através de comando DELETE e INSERT e isso realmente pode vir a trazer problemas. A substituição de um UPDATE por um DELETE e INSERT não é exclusiva de ambientes replicados podendo acontecer inclusive com comando de UPDATE locais que não são replicados.

    Um comando de UPDATE que substitui os dados é chamado de UPDATE In Place e não incorre em um DELETE seguido de um INSERT. É possível que seus comandos não estejam sendo In Place e por isso, na hora do Log Reader "traduzir" os comandos para a replicação ele opte por fazer um DELETE seguido de um INSERT.

    Para evitar atualizações que não sejam InPlace é interessante obedecer as seguintes regras:

    - Não atualizar colunas participantes de um índice cluster
    - Não dados em colunas VARCHAR de valores pequenos para valores muito grandes

    Claro que nem sempre é possível garantir isso e nesses casos é preciso "contornar".

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como validar os nomes das colunas durante a criação de uma tabela – Parte I
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1100.entry


    Classifique as respostas. O seu feedback é imprescindível
    domingo, 11 de julho de 2010 15:20
  • Bom dia!

    OBrigado pelo esclarecimento.

    As vezes é impossível satisfazer estes requisitos. No meu caso eu utilizei um CURSOR que vai atualizando linha por linha. Foi a forma que eu encontrei de contornar este padrão.

    T+

    segunda-feira, 12 de julho de 2010 11:35
  • Oi Fábio,

    Eu ia sugerir isso, mas acabei por não fazê-lo, pois operações linha a linha normalmente denigrem o desempenho.
    Entretanto, parece ser a única alternativa.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como validar os nomes das colunas durante a criação de uma tabela – Parte I
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!1100.entry


    Classifique as respostas. O seu feedback é imprescindível
    segunda-feira, 12 de julho de 2010 13:35
  • Fábio,

    A explicação do Gustavo com certeza foi esclarecedora!!!

    Se o cursor ajudou a resolver a sua solução podemos dizer que este post esta concluído, mas é claro, você deverá analisar a performance da sua query, eu gosto muito de utilizar cursor por sua praticidade, mas tenho sempre em mente que devo tomar cuidado em relação a algum tipo de aumento no custo de processamento.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    terça-feira, 13 de julho de 2010 00:02
    Moderador
  • Maia,

    Esse é um comportamento comum por parte do SQL Server em relação ao tipo de Replicação que ele esta utlizando!!!

    Mas o que eu fiquei pensando, é que os comando são enviados entre os servidores e executados localmente em cada instância, então o Log Reader deveria identificar como In Place, mas parece que isso não esta sendo identificado.

    No meu ambiente de teste que montei aqui, também pude observar este comportamento, e também a forma que o Log Reader esta trabalhando, ainda mais fazendo uso do SQL Server Profiler.

    Acredito que deveríamos analisar as transações de Update que ele esta executando, estou suspeitando que o Update esta sendo feito sobre a chave primária.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    terça-feira, 13 de julho de 2010 00:08
    Moderador
  • Bom dia!

    O meu update era assim: update Produto set Codigo = '0' + Codigo where len(Codigo) = 13;

    Não é pela chave primária. Mas é por um campo, Codigo, que é nvarchar.

    Obrigado.

    T+
    terça-feira, 13 de julho de 2010 12:03
  • Fábio,

    Então mas o campo esta armazenando valores do tipo String, mesmo ele não sendo Varchar ele é tipo derivado, neste caso NVarChar.

    Se analisar as considerações que o Gustavo indicou isso pode ser considerada uma das possíveis causas deste comportamento.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário]
    quarta-feira, 14 de julho de 2010 00:42
    Moderador