데이터베이스 교착 상태 또는 데이터 무결성 문제 해결

복잡한 데이터베이스 작업, 특히 트랜잭션과 관련된 작업은 디버그하기 어려운 교착 상태 또는 데이터 불일치로 이어질 수 있습니다.

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은 다양한 조건이 충족되는 source_table 행의 선택된 필드에서 데이터를 포함하는 target_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 작업이 실패할 경우 트랜잭션이 롤백되고 데이터베이스가 변경되지 않도록 합니다.

추가 참고 자료

• Prompt engineering for GitHub Copilot Chat
• GitHub Copilot 사용 모범 사례