SQL Server 오류 1205: 교착 상태가 발생하여 희생자로 선택되었습니다
두 세션이 각각 다른 세션이 가진 잠금을 기다리고 있었습니다. SQL Server는 다른 트랜잭션이 완료될 수 있도록 이 트랜잭션을 롤백했습니다. 다시 시도하세요.
- 오류 번호
1205- 심각도
- 13
- 카테고리
- 트랜잭션과 잠금
의미
교착 상태는 잠금 대기 사이클입니다: 세션 A가 B가 필요한 잠금을 보유하고 있고, 동시에 B가 A가 필요한 잠금을 보유하고 있는 상황입니다. SQL Server의 교착 상태 모니터는 이 사이클을 감지하고, 피해자를 선택하여 해당 세션의 전체 트랜잭션을 롤백하고 1205 오류를 발생시킵니다. 피해자는 가장 낮은 DEADLOCK_PRIORITY, 그리고 같은 우선순위면 롤백 비용이 가장 낮은 것을 고릅니다.
다른 세션은 아무 일도 없었던 것처럼 계속 진행됩니다. 교착 상태는 동시 실행 시 정상적으로 발생할 수 있으며, 목표는 교착 상태의 발생을 드물게 하고 발생했을 때 재시도하는 것입니다.
일반적인 원인
- 두 코드 경로가 같은 테이블이나 행을 반대 순서로 업데이트합니다.
- 기본 격리 수준인 READ COMMITTED에서의 읽기가, 자신은 읽는 쪽의 공유 잠금을 기다리는 쓰는 쪽을 기다리는 상황(비클러스터형 인덱스를 통한 조회에서 흔함).
- 업데이트 및 삭제 시 실제 변경하는 것보다 훨씬 많은 행을 스캔하고 잠그게 만드는 누락된 인덱스.
- 오래 지속되는 트랜잭션 또는 열린 트랜잭션 중간의 사용자 상호작용.
- 다음과 같은 더 높은 격리 수준
SERIALIZABLE범위 잠금을 쥔 트랜잭션이 있을 때입니다.
해결 방법
-
트랜잭션이 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; -
교착 상태 그래프를 읽으십시오. 내장된
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); -
모든 트랜잭션에서 테이블과 행에 동일한 순서로 접근하고 트랜잭션을 짧게 유지하세요.
-
인덱스를 추가하면 쓰기가 변경하는 행만 건드리게 됩니다. 데드락 그래프는 각 잠금이 걸렸던 인덱스를 보여 줍니다.
-
읽기 커밋 스냅샷 격리를 고려해 보세요. 그러면 읽기 작업은 공유 잠금 대신 행 버전을 사용하며 더 이상 쓰기 작업과 데드락이 발생하지 않습니다.
ALTER DATABASE SalesDb SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
-
덜 중요한 작업을 우선 희생자로 표시하세요.
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이 교착 상태를 해결하나요?
읽기 작업이 공유 잠금을 걸지 않게 할 수 있지만, 더티 리드와 일관되지 않은 읽기(행이 누락되거나 중복됨)를 허용합니다. 커밋된 읽기 스냅샷 격리는 이런 위험 없이 읽기-쓰기 차단을 없앱니다.
관련 오류
1222잠금 요청 제한 시간이 초과되었습니다.3960스냅샷 격리 업데이트 충돌266EXECUTE 후 트랜잭션 수 불일치40P01PostgreSQL: 교착 상태 감지됨1213MySQL: 잠금을 얻으려고 할 때 교착 상태 발생ORA-00060Oracle: 자원을 기다리는 동안 교착 상태가 감지되었습니다
그 밖의 트랜잭션 및 잠금 오류
3930트랜잭션을 커밋할 수 없습니다.3902COMMIT에는 해당 BEGIN이 없습니다.3903ROLLBACK에 짝이 되는 BEGIN이 없습니다3609트리거에서 트랜잭션이 종료되었습니다.3616트리거 실행 중 발생한 오류6401트랜잭션이나 세이브포인트를 찾을 수 없습니다3998커밋할 수 없는 트랜잭션이 롤백되었습니다3621명령문이 종료되었습니다
코드와 메시지 텍스트의 출처는 다음과 같습니다 Microsoft 데이터베이스 엔진 이벤트 및 오류 참조. 전체 목록은 SQL Server 오류 목록 또는 모든 데이터베이스 오류 검색.
SQLly에서 SQL Server 작업하기
스키마를 아는 IntelliSense, 위험한 문장에 대한 가드레일, 명확한 오류 메시지를 갖춘 macOS, Windows, Linux용 네이티브 SQL 클라이언트입니다. SQL Server는 물론 평소 쓰는 다른 데이터베이스까지 지원합니다.