Corrigindo problemas de integridade de dados ou deadlocks de banco de dados

Operações de banco de dados complexas – especialmente as que envolvem transações – podem levar a deadlocks ou inconsistências de dados difíceis de depurar.

O Copilot Chat pode ajudar identificando pontos em uma transação em que bloqueios ou deadlocks podem ocorrer e pode sugerir práticas recomendadas para o isolamento das transações ou a resolução dos deadlocks, como ajustar estratégias de bloqueio ou lidar com exceções de deadlock com elegância.

Evitando atualizações simultâneas em linhas interdependentes

Quando duas ou mais transações tentam atualizar as mesmas linhas em uma tabela de banco de dados, mas em ordens diferentes, isso pode causar uma condição de espera circular.

Cenário de exemplo

O snippet de SQL a seguir atualiza uma linha de uma tabela e, em seguida, executa uma operação que leva vários segundos e atualiza outra linha na mesma tabela. Isso é problemático porque a transação bloqueia a linha id = 1 por vários segundos antes de ser concluída, liberando o bloqueio. Se durante esse período for iniciada outra transação que executa uma operação semelhante, mas bloqueia a linha id = 2 primeiro para que possa atualizar a linha e, em seguida, tentar bloquear a linha id = 1, ambas as transações ficarão aguardando a outra ser concluída, resultando em um deadlock.

BEGIN TRANSACTION;
UPDATE my_table SET value = 'Some value' WHERE id = 301;
-- Simulate a process taking 5 seconds:
WAITFOR DELAY '00:00:05';
UPDATE my_table SET value = 'Another value' WHERE id = 127;
COMMIT TRANSACTION;

Exemplo de prompt 1

Você pode verificar se há algum problema com essa transação.

No editor, selecione o código da transação e pergunte ao Copilot Chat:

Is this transaction good?

Exemplo de resposta 1

O Copilot informa que "a transação no código SQL está bem de modo geral", mas lista algumas coisas que talvez você queira considerar: duração do bloqueio, tratamento de erros e simultaneidade. Ele menciona que "a transação mantém bloqueios por um longo período, potencialmente levando a bloqueios ou deadlocks". A resposta também inclui código revisado que adiciona o tratamento de erros.

Nesse caso, você opta por não adicionar o tratamento de erros. No momento, você deseja resolver o potencial de deadlocks.

Exemplo de prompt 2

Digite este prompt de acompanhamento:

How can I rewrite this code to reduce the chance of deadlock to a minimum while not adversely affecting performance. Tell me anything else I can do to avoid a deadlock. Note: I don't want to add error handling at this time.

Exemplo de resposta 2

O Copilot fornece estas orientações:

• Ordem de acesso: verifique se todas as transações acessam tabelas e linhas na mesma ordem.
• Manter transações curtas: minimize o tempo em que uma transação mantém bloqueios.
• Usar níveis de isolamento apropriados: use o nível de isolamento mais baixo que atenda aos seus requisitos.

Esta é uma versão revisada do seu código:

BEGIN TRANSACTION;
-- Update the rows in a consistent order
UPDATE my_table SET value = 'Another value' WHERE id = 127;
UPDATE my_table SET value = 'Some value' WHERE id = 301;
-- Simulate a process taking 5 seconds:
WAITFOR DELAY '00:00:05';
COMMIT TRANSACTION;

Evitando pesquisas fora do índice

Pesquisar colunas de tabelas que não foram indexadas normalmente levará a uma verificação de tabela completa. Isso pode ser muito lento e fazer uso intensivo de recursos, aumentando a probabilidade de gargalos de desempenho.

Cenário de exemplo

Este SQL resultará em uma verificação de tabela completa se some_column não estiver indexado:

BEGIN TRANSACTION;
SELECT * FROM my_table WHERE some_column = 'some_value';
-- More actions here, then:
COMMIT TRANSACTION;

Solicitação de exemplo

Fazer a pergunta a seguir ao Copilot geralmente revelará o possível problema.

How can I optimize this transaction?

Exemplo de resposta

O Copilot fornece algumas orientações e menciona que a coluna deve ser indexada:

Para otimizar a transação, assegure que a transação seja o mais curta possível para reduzir o bloqueio e aprimorar o desempenho. Veja a seguir algumas dicas:

1. Minimizar o trabalho feito na transação: inclua somente as operações necessárias na transação.
2. Usar índices apropriados: verifique se as colunas usadas na cláusula WHERE estão indexadas.
3. Evitar transações de execução prolongada: mantenha a duração da transação curta para evitar problemas de bloqueio.

O código sugerido na resposta inclui o comando SQL para indexar a coluna:

`CREATE INDEX idx_some_column ON my_table(some_column);`

Evitando transações de execução prolongada

Sempre que possível, é importante evitar consultas e transações de execução prolongada, pois elas podem prejudicar muito o desempenho e causar gargalos para outras operações. Além disso, elas podem consumir memória significativa e espaço de log, tornando o aplicativo mais propenso a falhas em ambientes de alta carga.

Cenário de exemplo

O SQL a seguir cria linhas em target_table, contendo dados dos campos de linhas selecionados em source_table, em que várias condições são atendidas. Se source_table for extremamente grande, isso poderá resultar em uma transação de execução prolongada.

BEGIN TRANSACTION;

INSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)
SELECT first_name, last_name, email, department, job_title, start_date
FROM source_table
WHERE (department = 'Engineering' AND (salary > 95000 OR grade > 5))
  OR (department = 'Marketing' AND performance_rating = 'Excellent');

COMMIT;

Solicitação de exemplo

Você pode perguntar ao Copilot se é possível evitar transações de execução prolongada.

source_table is an extremely large table. Without altering the table, how can I ensure this SQL doesn't become a long-running query, that blocks other operations?

Exemplo de resposta

O Copilot sugere as seguintes estratégias:

• Processamento em lote: processar os dados em lotes menores em vez de em uma só transação grande. Isso pode ser feito usando um loop com um limite e um deslocamento.
• Indexação: verifique se as colunas usadas na cláusula WHERE (department, salary, grade e performance_rating) são indexadas para acelerar a consulta.
• Bloqueio: use o bloqueio em nível de linha para evitar o bloqueio de toda a tabela.

Ele fornece o exemplo a seguir de como você pode implementar o processamento em lote. Esse SQL processa os dados em lotes de 1000 linhas por vez, reduzindo a carga sobre o banco de dados e impedindo transações de execução prolongada.

DECLARE @BatchSize INT = 1000;
DECLARE @Offset INT = 0;
DECLARE @RowCount INT;

SET @RowCount = @BatchSize;

WHILE @RowCount = @BatchSize
BEGIN
    BEGIN TRANSACTION;

    INSERT INTO target_table (first_name, last_name, email, dept, role, hire_date)
    SELECT first_name, last_name, email, department, job_title, start_date
    FROM source_table
    WHERE (department = 'Engineering' AND salary > 95000)
       OR (department = 'Engineering' AND years_of_experience > 5)
       OR (department = 'Marketing' AND performance_rating = 'Excellent');
    ORDER BY primary_key_column
    OFFSET @Offset ROWS FETCH NEXT @BatchSize ROWS ONLY;

    SET @RowCount = @@ROWCOUNT;
    SET @Offset = @Offset + @BatchSize;

    COMMIT;
END;

O Copilot instrui você a substituir primary_key_column no código sugerido pelo nome da coluna de chave primária real de source_table.

Evitando problemas de integridade de dados

É importante que as informações nos bancos de dados permaneçam precisas, consistentes e completas. Consultas mal elaboradas podem resultar em dados ausentes ou incorretos.

Cenário de exemplo

A transação SQL a seguir exclui um registro de funcionário e cria um log de exclusão. No entanto, é possível que a operação DELETE seja bem-sucedida e a operação INSERT falhe; nesse caso, não há nenhum registro em log da exclusão.

BEGIN TRANSACTION;

DELETE FROM employees
WHERE employee_id = 101;

INSERT INTO deletion_log (employee_id, deletion_date, reason)
VALUES (101, GETDATE(), 'Voluntary Resignation');

COMMIT TRANSACTION;

Solicitação de exemplo

How can I ensure that the DELETE and INSERT operations are only performed if both succeed?

O Copilot informa que você consegue fazer isso usando uma transação com tratamento de erros. Ele fornece o seguinte exemplo usando o constructo TRY...CATCH do SQL Server:

BEGIN TRY
    BEGIN TRANSACTION;

    DELETE FROM employees
    WHERE employee_id = 101;

    INSERT INTO deletion_log (employee_id, deletion_date, reason)
    VALUES (101, GETDATE(), 'Voluntary Resignation');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    -- Handle the error, e.g., log it or re-throw
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

O código sugerido garante que, se a operação DELETE ou INSERT falhar, a transação será revertida e nenhuma alteração será feita no banco de dados.

Leitura adicional

• "Engenharia de prompts para o GitHub Copilot"
• "Melhores práticas para usar o GitHub Copilot"