[DBA] 삭제 조건에 적절한 인덱스가 없는 경우 단위처리 및 RowLock 으로 삭제하는 방법

데이터 삭제 시 TabLock 의 이슈가 발생

---

T1 테이블의 인덱스는 아래와 같이 구성되어 있습니다.

인덱스 종류	컬럼
Clustered Index + PK	C1, C2, C3, C4

이 때, 라이브 중에 아래와 같은 쿼리를 사용하여 데이터를 삭제하려고 합니다.

DELETE FROM dbo.T1 WHERE C2 = 73985

문제는 T1 에 C2 의 인덱스가 없어서,

TabLock 이 발생하게 되고 이 시간동안에는 다른 테이블이 접근할 수 없다는 점입니다.

이런 경우 TabLock 없이 어떻게 삭제 작업을 진행할 수 있을까요?

시퀀스 (identity column)를 잘 활용해 봅시다.

(T1 테이블에 너무 많은 데이터가 있다면 이 작업은 적절치 않을 수 있습니다.)

TabLock 없이 삭제 방법

---

1. 작업용 테이블 생성

T1 의 테이블 스키마와 동일하고, identity column 을 추가로 붙여 작업용 테이블을 생성합니다.

CREATE TABLE dbo.T1_Delete_Working(
    [seq] [int] identity(1,1),
    [C1] [int] NOT NULL,
    [C2] [int] NOT NULL,
    [C3] [int] NOT NULL,
    [C4] [nvarchar](32) NOT NULL,
    [C5] [nvarchar](64) NOT NULL,
    [C6] [int] NULL,
    [C7] [datetime] NULL,
    [C8] [datetime] NULL,
)

2. 작업용 테이블에 작업 대상 데이터 복사

ntt_RejectedUser 테이블의 삭제 대상인 데이터를 작업용 테이블로 넣습니다.

INSERT INTO dbo.T1_Delete_Working
  (C1,
   C2,
   C3,
   C4,
   C5,
   C6,
   C7,
   C8)
  SELECT 
    C1,
    C2,
    C3,
    C4,
    C5,
    C6,
    C7,
    C8
  FROM dbo.T1 WITH(NOLOCK)
  WHERE C2 = 73985
OPTION (MAXDOP 1)

3. 인덱스 생성

작업용 테이블에서 seq 로 인덱스를 생성합니다. ( 혹은 seq + PK )

CREATE CLUSTERED INDEX T1_Delete_Working_PK_seq
    ON dbo.T1_Delete_Working (seq)

4. 단위처리 및 RowLock 으로 삭제 진행

- 기존 테이블을 복사한 테이블과 PK 기준으로 비교하며 데이터 삭제를 진행합니다.

- 삭제 대상이 있는, 작업용 테이블을 seq 순으로 읽어 가면서 PK 컬럼의 데이터를 출력합니다.

- 출력된 PK 데이터를 기존 테이블의 PK 와 비교하면서 삭제를 진행합니다.

* PK 를 기준으로 삭제할 수 있기 때문에, RowLock 으로 진행할 수 있습니다.

* 단위처리로 변경되었기 때문에, DBA 가 작업시간과 진행도를 알 수 있습니다.

DECLARE @i int, @maxCnt int
SET @i = 1
SELECT TOP 1 @maxCnt=seq FROM dbo.T1_Delete_Working WITH(NOLOCK) ORDER BY seq DESC OPTION (MAXDOP 1)
 
WHILE (@i <= @maxCnt)
BEGIN
  DELETE A FROM 
    (SELECT C1,
            C2,
            C3,
            C4
       FROM dbo.T1_Delete_Working WITH(NOLOCK)  
       WHERE seq >= @i
         AND seq < @i+3000
    ) B
    INNER LOOP JOIN dbo.T1 A WITH(ROWLOCK) 
    ON A.C1 = B.C1
    AND A.C2 = B.C2
    AND A.C3 = B.C3
    AND A.C4 = B.C4
  OPTION (MAXDOP 1)
  
  print  CAST(@maxCnt AS VARCHAR(10)) + '건 중 ' + CAST(@i AS VARCHAR(10)) + ' ~ ' + CAST(@i + 3000 - 1 AS VARCHAR(10))
 
  SET @i = @i+3000
END

5. 데이터 삭제 확인

기존 테이블이 삭제가 잘 되었는지 확인합니다.

SELECT COUNT(*)
  FROM dbo.T1 WITH(NOLOCK)
 WHERE C2 = 73985 OPTION (MAXDOP 1)

- 위의 방법은 다시 인덱스를 탈 수 없기 때문에 Lock 이슈는 없지만 Table Scan 이 발생합니다.

- oidUser 가 양수형 정수라는 것을 알고 있다면, 아래처럼 변경하여 Index 를 사용할 수 있게 변경할 수 있습니다.

SELECT COUNT(*)
  FROM dbo.T1 WITH(NOLOCK)
 WHERE C1 >= 1 AND C2 = 73985 OPTION (MAXDOP 1)