Устранение проблем с взаимоблокировкой базы данных или целостности данных

Copilot Chat помогает избежать кода, вызывающего медленные или заблокированные операции базы данных, таблицы с отсутствующими или неправильными данными.

В этой статье

Сложные операции базы данных, особенно связанные с транзакциями, могут привести к взаимоблокировкам или несоответствиям данных, которые трудно отлаживать.

Copilot Chat может помочь, определив точки в транзакции, в которой может произойти блокировка или взаимоблокировка, и может предложить рекомендации по изоляции транзакций или разрешению взаимоблокировок, например настройке стратегий блокировки или обработке исключений взаимоблокировки.

Note

Ответы, показанные в этой статье, являются примерами. Ответы Copilot Chat являются недетерминированными, поэтому вы можете получить различные ответы, показанные здесь.

Избегайте одновременных обновлений между строками

Если две или более транзакций пытаются обновить одни и те же строки в таблице базы данных, но в разных заказах, это может привести к циклическим условию ожидания.

Пример сценария

Следующий фрагмент КОДА SQL обновляет одну строку таблицы, а затем выполняет операцию, которая занимает несколько секунд, а затем обновляет другую строку в той же таблице. Это проблематично, так как транзакция блокирует id = 1 строку в течение нескольких секунд до завершения транзакции, освобождая блокировку. Если другая транзакция начинается в течение этого времени, которая выполняет аналогичную операцию, но блокирует id = 2 строку первым, чтобы она может обновить строку, а затем пытается заблокировать id = 1 строку, обе транзакции останутся в ожидании завершения другой, что приведет к взаимоблокировке.

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;

Пример запроса 1

Вы можете проверить наличие проблем с этой транзакцией.

В редакторе выберите код транзакции, а затем попросите Copilot Chat:

Is this transaction good?

Пример ответа 1

Copilot сообщает о том, что "транзакция в коде SQL, как правило, хорошо", но перечислены некоторые вещи, которые вы можете рассмотреть: длительность блокировки, обработка ошибок и параллелизм. В нем упоминается, что "транзакция держит блокировки в течение длительного периода, что потенциально приводит к блокировке или взаимоблокировкам". Ответ также содержит измененный код, который добавляет обработку ошибок.

В этом случае вы решили не добавлять обработку ошибок. Сейчас вы хотите устранить потенциал взаимоблокировок.

Пример запроса 2

Введите следующий запрос:

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.

Пример ответа 2

Copilot дает следующие рекомендации:

  • Порядок доступа: убедитесь, что все транзакции обращаются к таблицам и строкам в одном порядке.
  • Сохранение коротких транзакций. Свести к минимуму время, когда транзакция удерживает блокировки.
  • Используйте соответствующие уровни изоляции: используйте самый низкий уровень изоляции, соответствующий вашим требованиям.

Ниже приведена обновленная версия кода:

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;

Избегайте поиска без индекса

Поиск столбцов таблицы, не индексированных, обычно приводит к полной проверке таблицы. Это может быть очень медленным и ресурсоемким, что повышает вероятность узких мест производительности.

Пример сценария

Следующий SQL приведет к полной проверке таблицы, если some_column не индексируется:

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

Пример запроса

Запрос Copilot следующий вопрос обычно показывает потенциальную проблему.

How can I optimize this transaction?

Пример отклика

Copilot дает некоторые советы и упоминает, что столбец должен быть индексирован:

Чтобы оптимизировать транзакцию, необходимо убедиться, что транзакция максимально коротка, чтобы уменьшить блокировку и повысить производительность. Несколько советов:

  1. Свести к минимуму работу, выполняемую в транзакции: в транзакцию включаются только необходимые операции.
  2. Используйте соответствующие индексы: убедитесь, что столбцы, используемые в предложении WHERE, индексируются.
  3. Избегайте длительных транзакций: не закрывайте длительность транзакции, чтобы избежать проблем с блокировкой.

Предлагаемый код в ответе включает команду SQL для индексирования столбца:

`CREATE INDEX idx_some_column ON my_table(some_column);`

Предотвращение длительных транзакций

Важно избежать длительных запросов и транзакций, когда это возможно, так как они могут значительно снизить производительность и вызвать узкие места для других операций. Кроме того, они могут использовать значительные объемы памяти и пространства журналов, что делает приложение более подверженным сбою в средах с высокой нагрузкой.

Пример сценария

В следующем SQL создаются новые строки target_table, содержащие данные из выбранных полей строк, в source_table которых выполняются различные условия. Если source_table это очень большой, это может привести к длительной транзакции.

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;

Пример запроса

Вы можете задать Copilot, можно ли избежать длительных транзакций.

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?

Пример отклика

Copilot предлагает следующие стратегии:

  • Пакетная обработка. Обработка данных в небольших пакетах вместо одной большой транзакции. Это можно сделать с помощью цикла с ограничением и смещением.
  • Индексирование. Убедитесь, что столбцы, используемые в WHERE предложении (department, , salary``gradeиperformance_rating) индексируются для ускорения запроса.
  • Блокировка: используйте блокировку на уровне строк, чтобы избежать блокировки всей таблицы.

Он предоставляет следующий пример реализации пакетной обработки. Этот SQL обрабатывает данные в пакетах из 1000 строк за раз, уменьшая нагрузку на базу данных и предотвращая длительные транзакции.

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;

Copilot указывает, что в предлагаемом коде необходимо заменить primary_key_column именем фактического столбца source_tableпервичного ключа.

Избегайте проблем с целостностью данных

Важно, чтобы информация в базах данных оставалась точной, согласованной и полной. Плохо разработанные запросы могут привести к отсутствием или неправильным данным.

Пример сценария

Следующая транзакция SQL удаляет запись сотрудника и создает журнал удаления. Тем не менее, операция может DELETE завершиться успешно, и INSERT операция завершится ошибкой, в этом случае для удаления нет записи журнала.

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;

Пример запроса

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

Copilot сообщает, что это достигается с помощью транзакции с обработкой ошибок. В нем приведен следующий пример с помощью конструкции SQL Server TRY...CATCH :

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;

Предлагаемый код гарантирует, что DELETE при сбое или INSERT операции транзакция выполняется откат и никаких изменений в базе данных не выполняется.

Дополнительные материалы