● 교착상태
한 작업에서 잠근 리소스를 다른 작업에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하면 교착 상태가 발생합니다. 예를 들면 다음과 같습니다.
트랜잭션 A가 1행에 대한 공유 잠금을 획득합니다.
트랜잭션 B가 2행에 대한 공유 잠금을 획득합니다.
트랜잭션 A가 2행에 대한 배타적 잠금을 요청하고 트랜잭션 B가 2행에 대해 소유하고 있는 공유 잠금을 종료 및 해제할 때까지 트랜잭션 A가 차단됩니다.
트랜잭션 B가 1행에 대한 배타적 잠금을 요청하고 트랜잭션 A가 1행에 대해 소유하고 있는 공유 잠금을 종료 및 해제할 때까지 트랜잭션 B가 차단됩니다.
트랜잭션 B가 완료되어야 트랜잭션 A도 완료될 수 있지만 트랜잭션 B는 트랜잭션 A에 의해 차단된 상태입니다. 이러한 상태를 순환 종속 관계라고 합니다. 트랜잭션 A는 트랜잭션 B에 종속되고 트랜잭션 B는 트랜잭션 A에 종속된 형태로 순환됩니다.
교착 상태의 트랜잭션은 둘 다 외부 프로세스에서 교착 상태를 해제할 때까지 기다립니다. MicrosoftSQL Server 데이터베이스 엔진 교착 상태 모니터는 교착 상태에 있는 태스크가 있는지 주기적으로 검사합니다. 순환 종속 관계가 발견되면 모니터는 두 작업 중 처리하지 않을 태스크를 하나 선택하고 해당 트랜잭션을 오류와 함께 종료합니다. 이렇게 하여 다른 태스크가 해당 트랜잭션을 완료할 수 있습니다. 오류와 함께 종료된 트랜잭션의 응용 프로그램은 해당 트랜잭션을 다시 시도하며 이 트랜잭션은 대개 교착 상태의 다른 트랜잭션이 완료된 후에 끝납니다.
응용 프로그램에 특정 코딩 규칙을 사용하여 응용 프로그램에서 교착 상태를 일으킬 가능성을 줄일 수 있습니다. 자세한 내용은 교착 상태 최소화를 참조하십시오.
교착 상태는 종종 일반적인 차단과 혼동됩니다. 트랜잭션이 다른 트랜잭션에서 잠근 리소스에 대한 잠금을 요청하면 잠금이 해제될 때까지 잠금을 요청한 트랜잭션이 기다립니다. 기본적으로 LOCK_TIMEOUT이 설정되지 않은 한 SQL Server 트랜잭션 시간은 제한되지 않습니다. 잠금을 요청하는 트랜잭션은 잠금을 소유하는 트랜잭션을 차단하기 위한 작업을 수행하지 않으므로 교착 상태에 빠지지 않고 차단됩니다. 결국 잠금을 소유하는 트랜잭션이 완료되고 잠금을 해제하면 잠금을 요청하는 트랜잭션에 잠금이 허가되고 트랜잭션이 진행됩니다.
교착 상태는 deadly embrace(치명적인 포옹)라고도 합니다.
교착 상태는 관계형 데이터베이스 관리 시스템뿐만 아니라 다중 스레드를 사용하는 어느 시스템에서나 발생할 수 있으며 데이터베이스 개체에 대한 잠금 이외의 리소스에 대해 발생할 수 있습니다. 예를 들어 다중 스레드 운영 체제의 스레드는 메모리 블록과 같은 하나 이상의 리소스를 획득할 수 있습니다. 획득하려는 리소스를 현재 다른 스레드가 소유하고 있으면 대상 리소스가 해제될 때까지 첫 번째 스레드가 기다려야 할 수 있습니다. 이렇게 대기 중인 스레드는 해당 리소스에 대해 리소스를 소유하는 스레드에 종속됩니다. 데이터베이스 엔진의 인스턴스에서 세션은 메모리나 스레드 등의 데이터베이스가 아닌 리소스를 획득할 때 교착 상태에 빠질 수 있습니다.
이 그림에서 트랜잭션 T1은 Part 테이블 잠금 리소스에 대해 트랜잭션 T2에 종속됩니다. 마찬가지로 스레드 T2는 Supplier 테이블 잠금 리소스에 대해 트랜잭션 T1에 종속됩니다. 이러한 종속 관계는 순환적이므로 스레드 T1과 T2 간에 교착 상태가 발생합니다.
테이블이 분할되고 ALTER TABLE의 LOCK_ESCALATION 설정이 AUTO로 설정된 경우에도 교착 상태가 발생할 수 있습니다. LOCK_ESCALATION이 AUTO로 설정되면 데이터베이스 엔진에서 TABLE 수준이 아니라 HoBT 수준에서 테이블 파티션을 잠그도록 허용하여 동시성이 증가합니다. 그러나 개별 트랜잭션이 테이블에 파티션 잠금을 보유하고 다른 트랜잭션 파티션에서 잠금을 원하면 교착 상태가 발생합니다. 이런 유형의 교착 상태는 LOCK_ESCALATION을 TABLE로 설정하면 방지할 수 있습니다. 하지만 이 설정으로 인해 테이블 잠금을 기다리도록 파티션에 대규모 업데이트가 강제 적용되어 동시성이 감소됩니다.
----------------------------------------------------------------------------------------------------------------------------------------
위 내용은 MSDN에서 참조한 내용이다.
출처 : http://msdn.microsoft.com/ko-kr/library/ms177433(v=sql.100).aspx
하나의 테이블에 여러 트랜잭선에서 동시다발적으로 UPDATE/DELETE 등을 실행할 경우 교착상태(DEADLOCK)이 발생한다.
보통 이런경우 아래와 같은 에러메세지가 출력한다.
SqlException:System.Data.SqlClient.SqlException (0x80131904): 트랜잭션(프로세스 ID 95)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. 트랜잭션을 다시 실행하십시오.
이런 증상을 최소화 하기 위해서는 몇가지 방법이 있다.
1. 인덱스를 설정한다. 인덱스가 없는 경우 DEADLOCK 이 발생할 확률이 넓어진다.
2. 트랜젝션을 가급적 짧고 단순하게 만든다.
3. Transaction Isolation Level 을 "Read UnCommitted"로 설정한다.
4. LOCK_ESCALATION 을 "Disable", ALLOW_PAGE_LOCKS 를 "OFF"로 설정한다.
위의 4가지 항목들을 모두 설정해야 오류가 발생하지 않는것 같다.
▷ Isolation Level 설정 방법
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
쿼리문 상단에 위 설정구분을 넣어두면 된다.
▷ LOCK_ESCALATION 설정 방법
ALTER TABLE dbo.tb_ExTable SET (LOCK_ESCALATION = DISABLE )
LOCK_ESCALATION 은 AUTO, TABLE, DISABLE 로 설정할 수 있다.(기본값 TABLE)
설정된 LOCK_ESCALATION 값을 보고 싶은경우 아래와 같은 쿼리를 실행하면 된다.
SELECT lock_escalation, lock_escalation_desc FROM sys.tables WHERE name IN ('tb_ExTable')
▷ ALLOW_PAGE_LOCKS 설정 방법
EXEC SP_INDEXOPTION tb_ExTable, DISALLOWPAGELOCKS, 1;
ALLOW_PAGE_LOCKS 는 테이블 생성할 때 WITH 문에 "ALLOW_PAGE_LOCKS = OFF"를 추가 하거나
위와 같이 테이블 생성 이후 DISALLOWPAGELOCKS 를 "1"로 설정하여 값을 변경할 수 있다.
설정된 ALLOW_PAGE_LOCKS 값을 보고 싶은 경우 아래와 같은 쿼리를 실행하면 된다.
SELECT ALLOW_PAGE_LOCKS FROM sys.indexes
WHERE OBJECT_ID IN (OBJECT_ID('tb_ExTable'))
위 항목들을 모두 설정하였는데도 에러가 발생한다면;;;;
다른 방법을 찾아보기 바란다.
SP 또는 해당 Update/Delete 작업을 최소한으로 수정하여 처리하는 방법밖에 없을것 같다.