Insert 구문만으로 DeadLock이 발생하는 경우

DeadLock 시리즈는 끝났지만 실제 프로젝트에서 경험을 통해서 알게된 내용과 해결 과정을 공유하려고 합니다. 이번 글 제목처럼 Insert구문만으로도 DeadLock이 발생하는 경우입니다. 하나의 테이블에 단순히 Insert만 한다고 발생하는 경우는 당연히 아닙니다. 실제 프로젝트에서의 내용을 공유할 수는 없어서 DeadLock이 발생하는 상황을 단순화하여 재현을 해보고자 합니다.

먼저 테이블은 아래와 같습니다.

/****** Object:  Table [dbo].[Master]    Script Date: 2017-07-20 오후 1:15:55 ******/ CREATE TABLE [dbo].[Master]( [MasterId] [bigint] IDENTITY(1,1) NOT NULL, [MasterName] [nvarchar](50) NULL,  CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED  ( [MasterId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object:  Table [dbo].[Slave]    Script Date: 2017-07-20 오후 1:15:55 ******/ CREATE TABLE [dbo].[Slave]( [SlaveId] [bigint] IDENTITY(1,1) NOT NULL, [MasterId] [bigint] NOT NULL, [SlaveName] [nvarchar](50) NULL,  CONSTRAINT [PK_Slave_1] PRIMARY KEY CLUSTERED  ( [SlaveId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Slave]  WITH CHECK ADD  CONSTRAINT [FK_Slave_Master] FOREIGN KEY([MasterId]) REFERENCES [dbo].[Master] ([MasterId]) GO ALTER TABLE [dbo].[Slave] CHECK CONSTRAINT [FK_Slave_Master] GO

=> 테이블은 2개입니다. Master, Slave라는 테이블이고 1:N의 관계를 가지는 구조입니다.

=> 노란색으로 표시한 부분이 외래키(FK) 관련 설정입니다.

DeadLock 상황을 설명하기 위한 표입니다.

시간순서	프로세스1	프로세스2	비고
1	Insert : Master 테이블
2		Insert : Master 테이블
3		Select : Slave FK 관계 확인	Insert : Slave 테이블 전에
4	Select : Slave FK 관계 확인		Insert : Slave 테이블 전에

=> DeadLock이 2개의 프로세스에서 Master테이블에 X, S Lock이 순환되어 발생합니다.

=> Lock 관점에서 보면 X, S Lock 호환이 되지 않는 Insert Select 구문으로 인한 것이며 여기서 Select에 해당 하는 부분이 FK 관계에 대한 확인이라는 점입니다. FK 확인 때문에 Master에 S Lock으로 블럭이 발생한다는 점이 의문입니다.

=> 어쨌든 FK와 관련된 것으로 보여 FK를 삭제하니 해결은 되었습니다. 일반적으로 테이블에 대한 관계, 제약조건을 빼고 하는 경우도 실전에서 은근히 많이 보게되어서 잠시 접어두었습니다. 

=> 고도화 하는 과정에서 다시 해당 내용을 살펴보았는데 DeadLock graph 내용을 보아도 특이한 점을 발견할 수가 없었습니다. 

=> SQL Server DeadLock 3편(http://resisa.tistory.com/187)에서 소개해드렸던 테스트 코드에서 특이한 점을 발견하였습니다.

Task.WaitAll(Enumerable.Range(0, 3).Select(t =>    Task.Run(() =>     {         // 비지니스 로직     })).ToArray());

=> 일반적인 상황에서는 2개만 돌려도 DeadLock이 발생하였는데 2개에서는 나오지 않고 3개 이상이 될 경우에 발생하였습니다. 다음으로 SP를 2개의 세션에서 커밋하지 않고 실행을 하니 잠금이 발생하는 부분이 있다는 것을 알 수 있었습니다. 잠금이 발생한다는 상황이 이해는 되지 않았지만 Select에서 잠금이 발생하고 3번째 프로세스에서 DeadLock이 발생하는 것 같다는 추측 정도는 되었습니다.

잠금이 발생하는 부분과 이유는 무엇일까 확인을 해보기 위해서 SP의 내용을 확인해보았습니다.

BEGIN TRAN -- 실제로는 파라미터로 TVP로 받았지만 테스트를 위해서 임시 테이블로 가정 -- 1. Master테이블에 넣을 데이터 DECLARE @MasterTemp AS TABLE ( MasterName nvarchar(100) NOT NULL ) INSERT INTO @MasterTemp VALUES (N'resisa') INSERT INTO [dbo].[Master] SELECT T.MasterName FROM @MasterTemp AS T DECLARE @MasterId bigint = CAST(SCOPE_IDENTITY() AS bigint) -- 2. Slave에 넣을 데이터 DECLARE @SlaveTemp AS TABLE ( JoinId bigint NOT NULL, SlaveName nvarchar(100) NOT NULL ) INSERT INTO @SlaveTemp VALUES (1, N'sisatoss') INSERT INTO @SlaveTemp VALUES (2, N'resisa1982') DECLARE @SlaveTemp2 AS TABLE ( JoinId bigint NOT NULL, MasterId bigint NOT NULL ) INSERT INTO @SlaveTemp2 VALUES (1, @MasterId) INSERT INTO @SlaveTemp2 VALUES (2, @MasterId) -- 3. SlaveTemp와 SlaveTemp2를 조인하여 Slave 테이블에 데이터를 넣어준다. INSERT INTO [dbo].[Slave] (SlaveName, MasterId) SELECT T1.SlaveName, T2.MasterId FROM @SlaveTemp AS T1 INNER JOIN @SlaveTemp2 AS T2 ON T1.JoinId = T2.JoinId ROLLBACK TRAN

=> 문제의 원인이 될것이라고 생각한 부분은 3번 주석의 Slave에 데이터를 넣기 위해서 2개의 임시 테이블을 조인하고 그 결과를 넣어주는 부분이였습니다. 그래서 INNER JOIN을 사용하지 않고 실행을 해보니 DeadLock이 사라졌습니다;;

=> 왜 이러한 현상이 발생하는 알기 위해서 예상실행 계획을 살펴보았습니다.

1. 잠금 현상 없음

2. 잠금 발생

=> 차이점이 보이시나요? 결국 Loop Join과  Merge Join에 대한 차이점으로 보입니다.

=> 잠금이 발생하는 상황을 유추해보면 Insert Select INNER JOIN에서 Sort로 인하여 Master테이블(아마 PAG)에 S Lock을 획득하려고 하기 때문입니다. Loop Join일 경우에는 Master테이블에 KEY에 S Lock을 획득하려고 합니다. 결국 차이점은 FK에 대한 Select에 대하여 어떤 리소스에 S Lock를 획득하려고 하느냐이며 전자의 경우에는 다른 트랜잭션에서 접근하는 리소스에 S Lock을 획득하려고 하기 때문에 블럭이 발생하는 것입니다.

=> 리소스를 PAG라고 예상하는 이유는 Slave Insert구문에 WITH(PAGLOCK) 힌트를 주면 해당 문제가 해결되기 때문입니다. 하지만 당연히 성능이 느려지기 때문에 첨언 정도로 봐주시면 될 것 같습니다.

정리를 해보면 아래와 같습니다.

- 상황 : Insert 구문으로 인한 DeadLock

- 조건

1. 2개의 테이블이 관계(FK)를 가지고 있다.

2. Slave 테이블에 Insert하는 구문에 Join 구문을 사용한다.

- 해결

1. Slave 테이블에 Insert하는 구문에 조인 힌트(OPTION (LOOP JOIN))를 사용한다.

2. Slave 테이블에 Insert하는 구문에 Join을 사용하지 않는다. (임시테이블을 사용하여 단순히 Insert한다)

3. FK 제약 조건을 제거한다.

조인을 할 경우에 OPTION (LOOP JOIN)처럼 힌트를 줄 수 있다는 것을 배웠습니다. 하지만 역시나 힌트는 정확하게 알고 써야한다는 것과 SQL 옵티마이저가 해당 임시테이블에 대한 조인 구문에서 Merge Join 비용이 Loop Join보다 높음에도 불구하고 Merge Join으로 판단한 근거(아니면 다른 이유 때문에???)는 무엇일까를 생각하며 잘 볼줄은 모르지만 예상 실행 계획을 통해서 추측을 해보니 DB 전문가?!가 된 느낌입니다. ㅎㅎ