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

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

В этой статье

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

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

Примечание.

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

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

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

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

Следующий фрагмент КОДА 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 prompt
Is this transaction good?

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

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

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

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

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

Copilot prompt
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 Даёт следующие советы:

Порядок Access: Убедитесь, что все транзакции access таблицы и строки в одном порядке.

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

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

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 следующий вопрос, обычно выявляя потенциальную проблему.

Copilot prompt
How can I optimize this transaction?

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

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

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

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

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

Предлагаемый код в ответе включает команду 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 , возможно ли избежать долгосрочных транзакций.

Copilot prompt
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 Предлагают следующие стратегии:

  • Пакетная обработка. Обработка данных в небольших пакетах вместо одной большой транзакции. Это можно сделать с помощью loop с пределом и смещением.
  • Индексирование. Убедитесь, что столбцы, используемые в 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;

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

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

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

Copilot сообщает, что вы достигаете этого с помощью транзакции с обработкой ошибок. Приводится следующий пример с использованием конструкции TRY...CATCH 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;

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

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