// SQL Server 오류 참조

SQL Server 오류 1205: 교착 상태가 발생하여 희생자로 선택되었습니다

두 세션이 각각 다른 세션이 가진 잠금을 기다리고 있었습니다. SQL Server는 다른 트랜잭션이 완료될 수 있도록 이 트랜잭션을 롤백했습니다. 다시 시도하세요.

오류 번호
1205
심각도
13
카테고리
트랜잭션과 잠금
오류 메시지
Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

의미

교착 상태는 잠금 대기 사이클입니다: 세션 A가 B가 필요한 잠금을 보유하고 있고, 동시에 B가 A가 필요한 잠금을 보유하고 있는 상황입니다. SQL Server의 교착 상태 모니터는 이 사이클을 감지하고, 피해자를 선택하여 해당 세션의 전체 트랜잭션을 롤백하고 1205 오류를 발생시킵니다. 피해자는 가장 낮은 DEADLOCK_PRIORITY, 그리고 같은 우선순위면 롤백 비용이 가장 낮은 것을 고릅니다.

다른 세션은 아무 일도 없었던 것처럼 계속 진행됩니다. 교착 상태는 동시 실행 시 정상적으로 발생할 수 있으며, 목표는 교착 상태의 발생을 드물게 하고 발생했을 때 재시도하는 것입니다.

일반적인 원인

  • 두 코드 경로가 같은 테이블이나 행을 반대 순서로 업데이트합니다.
  • 기본 격리 수준인 READ COMMITTED에서의 읽기가, 자신은 읽는 쪽의 공유 잠금을 기다리는 쓰는 쪽을 기다리는 상황(비클러스터형 인덱스를 통한 조회에서 흔함).
  • 업데이트 및 삭제 시 실제 변경하는 것보다 훨씬 많은 행을 스캔하고 잠그게 만드는 누락된 인덱스.
  • 오래 지속되는 트랜잭션 또는 열린 트랜잭션 중간의 사용자 상호작용.
  • 다음과 같은 더 높은 격리 수준 SERIALIZABLE 범위 잠금을 쥔 트랜잭션이 있을 때입니다.

해결 방법

  1. 트랜잭션이 1205 오류로 실패하면 애플리케이션에서 트랜잭션을 재시도하십시오. T-SQL에서 재시도 루프는 다음과 같습니다:

    DECLARE @tries int = 0;
    WHILE @tries < 3
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;
            UPDATE dbo.Accounts SET Balance -= 100 WHERE AccountId = 1;
            UPDATE dbo.Accounts SET Balance += 100 WHERE AccountId = 2;
            COMMIT;
            BREAK;
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 ROLLBACK;
            IF ERROR_NUMBER() <> 1205 OR @tries = 2 THROW;
            SET @tries += 1;
            WAITFOR DELAY '00:00:00.100';
        END CATCH
    END;
  2. 교착 상태 그래프를 읽으십시오. 내장된 system_health 확장 이벤트(Extended Events) 세션은 모든 교착 상태를 기록합니다.

    SELECT xed.value('@timestamp', 'datetime2') AS occurred_at, xed.query('.') AS deadlock_graph
    FROM (SELECT CAST(target_data AS xml) AS target_data
          FROM sys.dm_xe_session_targets AS t
          JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
          WHERE s.name = N'system_health' AND t.target_name = N'ring_buffer') AS data
    CROSS APPLY target_data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS x(xed);
  3. 모든 트랜잭션에서 테이블과 행에 동일한 순서로 접근하고 트랜잭션을 짧게 유지하세요.

  4. 인덱스를 추가하면 쓰기가 변경하는 행만 건드리게 됩니다. 데드락 그래프는 각 잠금이 걸렸던 인덱스를 보여 줍니다.

  5. 읽기 커밋 스냅샷 격리를 고려해 보세요. 그러면 읽기 작업은 공유 잠금 대신 행 버전을 사용하며 더 이상 쓰기 작업과 데드락이 발생하지 않습니다.

    ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
  6. 덜 중요한 작업을 우선 희생자로 표시하세요.

    SET DEADLOCK_PRIORITY LOW;

안전하게 재현하세요

표시된 순서대로 두 개의 별도 연결에서 임시 데이터베이스에 단계를 실행한 후, 살아남은 세션을 롤백하고 테이블을 삭제하세요.

-- Setup (once):
CREATE TABLE dbo.DL (id int PRIMARY KEY, v int);
INSERT INTO dbo.DL VALUES (1, 0), (2, 0);
-- Session 1:
BEGIN TRAN; UPDATE dbo.DL SET v = 1 WHERE id = 1;
-- Session 2:
BEGIN TRAN; UPDATE dbo.DL SET v = 2 WHERE id = 2;
UPDATE dbo.DL SET v = 2 WHERE id = 1;  -- waits
-- Session 1:
UPDATE dbo.DL SET v = 1 WHERE id = 2;  -- one session gets Msg 1205

질문

교착 상태가 발생한 후 내 데이터가 손실되나요?

희생된 쪽(데드락 희생자)의 전체 트랜잭션이 롤백되므로 그 변경 사항은 저장되지 않았습니다. 다른 것은 영향을 받지 않습니다. 트랜잭션을 다시 실행하세요.

NOLOCK이 교착 상태를 해결하나요?

읽기 작업이 공유 잠금을 걸지 않게 할 수 있지만, 더티 리드와 일관되지 않은 읽기(행이 누락되거나 중복됨)를 허용합니다. 커밋된 읽기 스냅샷 격리는 이런 위험 없이 읽기-쓰기 차단을 없앱니다.

관련 오류

그 밖의 트랜잭션 및 잠금 오류

코드와 메시지 텍스트의 출처는 다음과 같습니다 Microsoft 데이터베이스 엔진 이벤트 및 오류 참조. 전체 목록은 SQL Server 오류 목록 또는 모든 데이터베이스 오류 검색.

// 오류는 줄이고, 수정은 빠르게

SQLly에서 SQL Server 작업하기

스키마를 아는 IntelliSense, 위험한 문장에 대한 가드레일, 명확한 오류 메시지를 갖춘 macOS, Windows, Linux용 네이티브 SQL 클라이언트입니다. SQL Server는 물론 평소 쓰는 다른 데이터베이스까지 지원합니다.