Corrección de los interbloqueos de base de datos o los problemas de integridad de datos

Las operaciones complejas de base de datos, especialmente en las que intervienen transacciones, pueden provocar interbloqueos o incoherencias de datos difíciles de depurar.

Copilot Chat puede ayudar identificando los puntos en una transacción en los que se pueden producir bloqueos o interbloqueos, y puede sugerir procedimientos recomendados para el aislamiento de las transacciones o la resolución de los interbloqueos, como ajustar las estrategias de bloqueo o controlar las excepciones de interbloqueo correctamente.

Evitar actualizaciones simultáneas en filas interdependientes

Cuando dos o más transacciones intentan actualizar las mismas filas de una tabla de base de datos, pero en distinto orden, se puede producir una condición de espera circular.

Escenario de ejemplo

El siguiente fragmento de código SQL actualiza una fila de una tabla y, a continuación, realiza una operación que tarda varios segundos para posteriormente actualizar otra fila de la misma tabla. Esto es problemático porque la transacción bloquea la fila id = 1 durante varios segundos antes de que finalice la transacción, liberando el bloqueo. Si otra transacción que realiza una operación similar se inicia durante este tiempo, pero bloquea primero la fila id = 2 para poder actualizarla y luego intenta bloquear la fila id = 1, ambas transacciones se quedarán esperando a que se complete la otra, lo que dará lugar a un interbloqueo.

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;

Indicación de ejemplo 1

Puedes comprobar si hay algún problema con esta transacción.

En el editor, selecciona el código de la transacción y, a continuación, pregunta a Copilot Chat:

Is this transaction good?

Respuesta de ejemplo 1

Copilot indica que "la transacción en el código SQL está bien en general", pero enumera algunas cosas que podrías considerar: duración del bloqueo, control de errores y simultaneidad. Menciona que "la transacción contiene bloqueos durante un período prolongado, lo que podría provocar bloqueos o interbloqueos". La respuesta también incluye código revisado que agrega control de errores.

En este caso, decides no agregar el control de errores. Ahora mismo quieres abordar los posibles interbloqueos.

Indicación de ejemplo 2

Escribe esta indicación de seguimiento:

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.

Respuesta de ejemplo 2

Copilot proporciona el siguiente consejo:

• Orden de acceso: asegúrate de que todas las transacciones acceden a las tablas y filas en el mismo orden.
• Procurar que las transacciones sean cortas: minimiza el tiempo que una transacción contiene bloqueos.
• Usar los niveles de aislamiento adecuados: usa el nivel de aislamiento más bajo que cumpla tus requisitos.

Esta es una versión revisada del 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;

Evitar búsquedas no indexadas

La búsqueda en columnas de tabla que no se han indexado normalmente dará lugar a un escaneo de la tabla completo. Este proceso puede ser muy lento y consumir muchos recursos, lo que aumenta la probabilidad de cuellos de botella en el rendimiento.

Escenario de ejemplo

El siguiente código SQL dará como resultado un escaneo de tabla completo si some_column no está indexado:

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

Ejemplos de indicaciones

Con hacer a Copilot la siguiente pregunta, se suele revelar el posible problema.

How can I optimize this transaction?

Respuesta de ejemplo

Copilot ofrece algunos consejos y menciona que la columna debe indexarse:

Para optimizar la transacción, debes asegurarte de que la transacción sea lo más corta posible para reducir el bloqueo y mejorar el rendimiento. A continuación se muestran algunas sugerencias:

1. Minimizar el trabajo realizado en la transacción: incluye solamente las operaciones necesarias dentro de la transacción.
2. Usar índices adecuados: asegúrate de que las columnas usadas en la cláusula WHERE estén indexadas.
3. Evitar transacciones de larga duración: la duración de la transacción debe ser corta para evitar problemas de bloqueo.

El código sugerido en la respuesta incluye el comando SQL para indexar la columna:

`CREATE INDEX idx_some_column ON my_table(some_column);`

Evitar transacciones de larga duración

Es importante evitar consultas y transacciones de larga duración, siempre que sea posible, ya que pueden degradar gravemente el rendimiento y provocar cuellos de botella en otras operaciones. Además, pueden consumir memoria y espacio de registro considerables, lo que hace que la aplicación sea más propensa a errores en entornos de carga elevada.

Escenario de ejemplo

El siguiente código SQL crea nuevas filas en target_table, que contienen datos de campos seleccionados de filas en source_table donde se cumplen varias condiciones. Si source_table es extremadamente grande, se puede producir una transacción de larga duración.

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;

Ejemplos de indicaciones

Puedes preguntar a Copilot si es posible evitar transacciones de larga duración.

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?

Respuesta de ejemplo

Copilot sugiere las siguientes estrategias:

• Procesamiento por lotes: procesa los datos en lotes más pequeños en lugar de una sola transacción de gran tamaño. Para ello, usa un bucle con un límite y un desplazamiento.
• Indexación: asegúrate de que las columnas usadas en la cláusula WHERE (department, salary, gradey performance_rating) se indexan para acelerar la consulta.
• Bloqueo: usa el bloqueo de nivel de fila para evitar el bloqueo de toda la tabla.

Proporciona el ejemplo siguiente de cómo puedes implementar el procesamiento por lotes. Este código SQL procesa los datos en lotes de 1000 filas a la vez, lo que reduce la carga en la base de datos y evita las transacciones de larga duración.

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 te indica que reemplaces primary_key_column en el código sugerido por el nombre de la columna de clave principal real de source_table.

Evitar problemas de integridad de los datos

Es importante que la información de las bases de datos siga siendo exacta, coherente y completa. Las consultas mal diseñadas pueden dar lugar a datos que faltan o son incorrectos.

Escenario de ejemplo

La siguiente transacción SQL elimina un registro de empleado y crea un registro de eliminación. Sin embargo, es posible que la operación DELETE se realice correctamente y que la operación INSERT genere un error, en cuyo caso no hay ningún registro para la eliminación.

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;

Ejemplos de indicaciones

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

Copilot te indica que para lograr esto utilices una transacción con control de errores. Proporciona el ejemplo siguiente usando la construcción TRY...CATCH de 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;

El código sugerido garantiza que, si se produce un error en la operación DELETE o INSERT, la transacción se revierte y no se realizan cambios en la base de datos.

Información adicional

• "Solicitud de ingeniería para GitHub Copilot"
• "Procedimientos recomendados para usar GitHub Copilot"