아직도 꿈만 꿔? :: 누락된 인덱스에 의한 교착상태 해결하기

2009/01/14 16:01

누락된 인덱스에 의한 교착상태 해결하기

2009/01/14 16:01 in SQL Server

데드락하면 처음 의심하게 되는 원인은 교차 리소스 접근에 의한 차단이죠.

하지만 이러한 경우는 리소스 접근 순서를 동일하게 맞춰주는 것으로 해결할 수 있습니다.

이렇게 리소스 접근 순서를 맞춰주는 것으로 라이브락(Live Lock)으로 변경할 수 있죠.

원인을 찾기도 쉽고 해결하기도 쉽습니다.

하지만 리소스 접근 순서도 모두 동일한데 데드락이 발생하고,

유발하는 쿼리를 애써 찾아보면

하나의 테이블을 한 쿼리에서는 갱신(Update, Delete)하고 다른 쿼리에서는 오로지 트랜잭션내에서 동일 테이블의 다른 행을 조회하는 경우에는 도무지 답이 나오질 않습니다.

이럴 경우 의심해 볼 부분이 바로 인덱스 누락에 의한 데드락입니다.

실제로 이러한 상황을 재연해 보도록 하죠.

* 샘플 데이터베이스는 Northwind를 사용합니다.

Northwind 데이터베이스의 Orders테이블과 Customers테이블을 사용할텐데,

데이터만 사용할 것이기 때문에(원본 테이블은 인덱스가 이미 생성되어 있죠.)

다음 구문을 사용해서 Orders와 Customers 테이블의 카피 테이블을 만듭니다.

use Northwind
go

-- Orders
select
    *
    into o
    from Orders

-- Customers
select *
    into c
    from Customers

SSMS(SQL Server 2000에서는 쿼리분석기)에서 두 개의 새 쿼리 창을 엽니다.

편의상 각각의 쿼리창을 연결1, 연결2로 부르도록 하겠습니다.

(팁 :창 메뉴에서 새 세로 탭 그룹으로 쿼리 창을 한 화면에 분할해서 보시면 편합니다.)

연결1에서 다음 구문을 입력한 후 실행합니다.

begin tran

update dbo.c set
ContactName = ContactName + '1'
where CustomerID = 'ANATR'

연결2에서 다음 구문을 입력한 후 실행합니다.

begin tran

update dbo.o set
    ShipName = ShipName + '1'
    where OrderID = 10248


select * from dbo.c with(index(ix_c_CustomerID))
    where CustomerID = 'BERGS'

* 사실 dbo.o 테이블 업데이트 구문을 굳이 넣질 않아도 됩니다.

연결1에 의해 잠겨진 행은 CustomerID = 'ANATR' 행 뿐입니다.

연결2에서는 이 행과는 상관없는 'BERGS' 행의 공유잠금을 요청할 뿐인데

왜 공유잠금을 획득할 수 없을까요?

이유는 c 테이블의 CustomerID에 인덱스가 없기 때문입니다.

'BERGS'를 조회하기 위해 c 테이블을 전체 스캔해야하고

'ANATR'행을 만났을때 연결1에 의해 배타적으로 잠겨진 행에대해서는 공유잠금을 획득할 수 없게 되기 때문입니다.

연결1에서 해당 행의 배타잠금을 해제해 주기 전까지는 계속해서 차단되게 됩니다.

이러한 상태를 누락된 인덱스에 의한 교착상태(데드락)라고 합니다.

CustomerID 컬럼에 인덱스를 생성하면 해결됩니다.

create index ix_c_CustomerID on dbo.c(CustomerID)
go

c 테이블에 행 수가 적기때문에 인덱스를 사용하지 않게 되는데

이럴 경우 여전히 차단되는 걸 볼 수 있습니다.

다음과 같이 인덱스 힌트를 사용하면 됩니다.

begin tran

update dbo.o set
    ShipName = ShipName + '1'
    where OrderID = 10248


select * from dbo.c with(index(ix_c_CustomerID))
    where CustomerID = 'BERGS'

- 참고문헌 : INSIDE MICROSOFT SQL SERVER 2005: T-SQL PROGRAMMING(정보문화사)

이올린에 북마크하기