Início / dba / Perguntas / 115252
Accepted
Jonesome Reinstate Monica
Asked: 2015-09-17 06:59:02 +0800 CST

As transações explícitas são necessárias neste loop while?

  • 772

SQLServer 2014:

Temos uma tabela muito grande (100 milhões de linhas) e precisamos atualizar alguns campos nela.

Para envio de logs, etc., também, obviamente, queremos mantê-lo em transações pequenas.

Se deixarmos o seguinte executar um pouco e, em seguida, cancelar/encerrar a consulta, todo o trabalho feito até agora será confirmado ou precisamos adicionar instruções BEGIN TRANSACTION / END TRANSACTION explícitas para que possamos cancelar a qualquer momento?

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END
sql-server t-sql

1 respostas

  1. Best Answer

    Declarações individuais -- DML, DDL, etc -- são transações em si mesmas. Então, sim, após cada iteração do loop (tecnicamente: após cada instrução), qualquer UPDATEalteração na instrução foi confirmada automaticamente.

    Claro, sempre há uma exceção, certo? É possível habilitar Transações Implícitas via SET IMPLICIT_TRANSACTIONS , caso em que a primeira UPDATEinstrução iniciaria uma transação que você deveria COMMITou ROLLBACKno final. Esta é uma configuração de nível de sessão que está DESATIVADA por padrão na maioria dos casos.

    precisamos adicionar instruções BEGIN TRANSACTION / END TRANSACTION explícitas para que possamos cancelar a qualquer momento?

    Não. E, de fato, como você deseja interromper o processo e reiniciá-lo, adicionar uma transação explícita (ou habilitar transações implícitas) seria uma má ideia, pois interromper o processo pode pegá-lo antes de fazer o COMMIT. Nesse caso, você precisaria emitir manualmente COMMIT(se estiver no SSMS) ou, se estiver executando isso a partir de um trabalho do SQL Agent, não terá essa oportunidade e poderá acabar com uma transação órfã.

    Além disso, você pode querer definir @CHUNK_SIZEum número menor. A escalação de bloqueio geralmente ocorre em 5.000 bloqueios adquiridos em um único objeto. Dependendo do tamanho das linhas e se estiver fazendo bloqueios de linha x bloqueios de página, você pode ultrapassar esse limite. Se o tamanho de uma linha for tal que apenas 1 ou 2 linhas caibam por cada página, você sempre poderá acertar isso, mesmo que esteja fazendo bloqueios de página.

    Se a tabela for particionada, você terá a opção de definir a LOCK_ESCALATIONopção (introduzida no SQL Server 2008) para a tabela de AUTOforma que ela bloqueie apenas a partição e não a tabela inteira durante o escalonamento. Ou, para qualquer tabela, você pode definir a mesma opção como DISABLE, embora tenha que ter muito cuidado com isso. Consulte ALTER TABLE para obter detalhes.

    Aqui está alguma documentação que fala sobre o Lock Escalation e os limites: Lock Escalation (diz que se aplica a "SQL Server 2008 R2 e versões superiores"). E aqui está uma postagem de blog que trata da detecção e correção do escalonamento de bloqueio: Bloqueio no Microsoft SQL Server (Parte 12 – Escalamento de bloqueio) .

    Não relacionado à pergunta exata, mas relacionado à consulta na pergunta, há algumas melhorias que podem ser feitas aqui (ou pelo menos parece assim apenas olhando para ela):

    1. Para o seu loop, fazer WHILE (@@ROWCOUNT = @CHUNK_SIZE)é um pouco melhor, pois se o número de linhas atualizadas na última iteração for menor que a quantidade solicitada para UPDATE, não haverá trabalho a ser feito.

    2. Se o deletedcampo for um tipo de dados, esse valor não é BITdeterminado por ser ou não ? Por que você precisa de ambos?deletedDate2000-01-01

    3. Se esses dois campos são novos e você os adicionou NULLpara que possa ser uma operação online / sem bloqueio e agora deseja atualizá-los para o valor "padrão", isso não foi necessário. A partir do SQL Server 2012 (somente Enterprise Edition), adicionar NOT NULLcolunas que possuem uma restrição DEFAULT são operações sem bloqueio, desde que o valor de DEFAULT seja uma constante. Portanto, se você ainda não estiver usando os campos, basta eliminá-los e adicioná-los novamente como NOT NULLe com uma restrição DEFAULT.

    4. Se nenhum outro processo estiver atualizando esses campos enquanto você estiver fazendo este UPDATE, seria mais rápido se você enfileirasse os registros que deseja atualizar e, em seguida, apenas trabalhasse nessa fila. Há uma perda de desempenho no método atual, pois você precisa consultar novamente a tabela todas as vezes para obter o conjunto que precisa ser alterado. Em vez disso, você pode fazer o seguinte, que verifica a tabela apenas uma vez nesses dois campos e, em seguida, emite apenas instruções UPDATE muito direcionadas. Também não há penalidade em interromper o processo a qualquer momento e iniciá-lo posteriormente, pois o preenchimento inicial da fila simplesmente encontrará os registros restantes para atualizar.

      1. Crie uma tabela temporária (#FullSet) que contenha apenas os campos-chave do índice clusterizado.
      2. Crie uma segunda tabela temporária (#CurrentSet) dessa mesma estrutura.

      3. inserir em #FullSet viaSELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

        O TOP(n)está lá devido ao tamanho da mesa. Com 100 milhões de linhas na tabela, você realmente não precisa preencher a tabela de filas com todo esse conjunto de chaves, especialmente se planeja parar o processo de vez em quando e reiniciá-lo mais tarde. Então, talvez defina npara 1 milhão e deixe isso correr até a conclusão. Você sempre pode agendar isso em um trabalho do SQL Agent que executa o conjunto de 1 milhão (ou talvez até menos) e, em seguida, aguarda o próximo horário agendado para atender novamente. Você pode agendar a execução a cada 20 minutos para que haja algum espaço para respirar forçado entre as séries de n, mas ainda concluirá todo o processo sem supervisão. Em seguida, basta excluir o trabalho quando não houver mais nada a fazer :-).

      4. em um loop, faça:
        1. Preencha o lote atual por meio de algo comoDELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
        2. IF (@@ROWCOUNT = 0) BREAK;
        3. Faça o UPDATE usando algo como:UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
        4. Limpe o conjunto atual:TRUNCATE TABLE #CurrentSet;
    5. Em alguns casos, ajuda adicionar um índice filtrado para auxiliar o SELECTque alimenta a #FullSettabela temporária. Aqui estão algumas considerações relacionadas à adição de tal índice:
      1. A condição WHERE deve corresponder à condição WHERE da sua consulta, portantoWHERE deleted is null or deletedDate is null
      2. No início do processo, a maioria das linhas corresponderá à sua condição WHERE, portanto, um índice não é tão útil. Você pode querer esperar até algo em torno da marca de 50% antes de adicionar isso. Claro, o quanto isso ajuda e quando é melhor adicionar o índice varia devido a vários fatores, então é um pouco de tentativa e erro.
      3. Você pode ter que ATUALIZAR ESTATÍSTICAS manualmente e/ou RECONSTRUIR o índice para mantê-lo atualizado, pois os dados básicos mudam com bastante frequência
      4. Lembre-se de que o índice, embora ajude o SELECT, prejudicará o UPDATE, pois é outro objeto que deve ser atualizado durante essa operação, portanto, mais I/O. Isso funciona tanto usando um índice filtrado (que diminui à medida que você atualiza as linhas, pois menos linhas correspondem ao filtro) quanto esperando um pouco para adicionar o índice (se não for muito útil no início, não há razão para incorrer a E/S adicional).

    ATUALIZAÇÃO: Veja minha resposta a uma pergunta relacionada a esta pergunta para a implementação completa do que foi sugerido acima, incluindo um mecanismo para rastrear o status e cancelar de forma limpa: sql server: atualizando campos em uma tabela enorme em pequenos blocos: como obter status de progresso?

    • 15

relate perguntas