원본 글 : http://sqlmvp.kr/140164120810
최근 들어 서비스에서 DeadLock(교착상태) 이슈가 자주 발생 하여 이번 아티클은 DeadLock을 모니터링하는 방법에 대해서 알아 보도록 하겠습니다..
DeadLock(교착상태)?
한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하는 현상. (서로 맞물린 상태의 차단)
l 순환 교착(cycle DeadLock) : 서로 다른 개체를 차단할 때 발생
l 변환 교착(Conversion DeadLock) : 같은 대상에 대해 둘 이상의 세션이 동시에 잠금을 변경하려고 할 때발생
교착 상태를 일으킬 수 있는 리소스
l 잠금 : 개체, 페이지, 행, 메타데이터, 응용 프로그램 등의 리소스에 대한 잠금을 획득하려고대기 하는 경우.
l 작업자 스레드 : 사용 가능한 작업자 스레드를 대기하는 태스크가 교착 상태를 일으킬 수 있음.대기태스크가 모든 작업자 스레드를 차단하는 리소스를 소유하는 경우 교착상태 발생.
l 메모리 : 동시 요청이 사용 가능한 메모리보다 많은 메모리 부여를 대기하는 경우.
l 병렬 쿼리실행 관련 리소스 : 병렬쿼리에 속하지 않는 하나 이상의 다른 프로세스를 포함할 경우 서로 차단하여발생. 서버에서 새 쿼리 실행이 시작되거나 시스템에 작업자 스레드가 부족하여 시스템 작업이 예기치 않게변경되면 교착 상태가 발생.
l MARS(Multiple Active Result Sets)리소스 : MASRS에서여러 활성 요청의 인터리브를 제어하는데 사용.
DeadLock 감지하기.
교착 상태 검색은 데이터베이스 엔진 인스턴스의 모든 태스크에 대한 검색을 주기적으로 시작하는 잠금 모니터에서수행합니다.
l 기본 간격은 5초로 수행.
l 잠금 모니터스레드가 교착 상태를 발견하면 잠금 상태의 빈도에 따라 5초에서 최하100밀리초까지 교착 상태 검색 간격이 짧아 짐.
l 잠금 모니터스레드가 교착 상태 검색을 중지하면 데이터베이스 엔진은 검색 간격을 다시 5초로 늘림.
실습 환경
CREATE PROC SESSION_1 AS BEGIN TRAN UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100 WAITFOR DELAY '00:00:03'; UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100 ROLLBACK TRAN GO CREATE PROC SESSION_2 AS BEGIN TRAN UPDATE TBL_B SET COL5 = COL5 * 2 WHERE COL1 = 100 WAITFOR DELAY '00:00:03'; UPDATE TBL_A SET COL5 = COL5 * 2 WHERE COL1 = 100 ROLLBACK TRAN GO |
서로 다른 세션에서SESSION_1, SESSION_2 실행
1. sp_lock
2. DBCC TRACEON (1204, -1)
교착상태와 관련된 각 노드에 의해 형식이 지정된 교착 상태 정보를 보고.
3. DBCC TRACEON (1222, -1) --SQL Server 2005 이상사용
프로세스별 및 리소스별 순서로 교착상태의 정보를 보고
4. Profiler
5. Perfmon
6. DMV
SELECT TEXT, BLOCKING_SESSION_ID, COMMAND, DATABASE_ID,WAIT_TYPE,WAIT_RESOURCE, * FROM SYS.DM_EXEC_REQUESTS AS REQUEST CROSS APPLY SYS.DM_EXEC_SQL_TEXT(REQUEST.SQL_HANDLE) AS SQL_TEXT WHERE SESSION_ID > 50 AND SESSION_ID <> @@SPID AND BLOCKING_SESSION_ID <> 0 |
7. Job Alert
DeadLocK 최소화 하기
l 적절한 인덱스설정.
l 자원의 한쪽방향 액세스,
l 짧은 트랜잭션
l 테이블 크기최소화
l 잠금 제한시간 설정(SET LOCK_TIMEOUT)
l 적절한 격리수준(READ UNCOMMITTED)
l 하드웨어성능 향상
l 지속적인모니터링
참조 및 참고 사이트
l http://msdn.microsoft.com/ko-kr/library/ms178104(v=sql.105).aspx
'데이터베이스 > MS-SQL' 카테고리의 다른 글
| LOCK 프로세스 정보확인 (0) | 2014.01.03 |
|---|---|
| SQL Server 2005 암호화 함수 (0) | 2013.12.11 |
| tempdb 관리 (0) | 2013.11.27 |
| mdf 파일만 가지고 복구시키기 (0) | 2013.11.27 |
| MSSQL 문자열 함수 (0) | 2013.11.01 |
