데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

DeadLock(교착상태) 모니터 하기

 

DeadLock(교착상태)?

한 태스크에서 잠근 리소스를 다른 태스크에서 잠그려고 하여 둘 이상의 태스크가 서로 영구적으로 차단하는 현상. (서로 맞물린 상태의 차단)

  • 순환 교착(cycle DeadLock) : 서로 다른 개체를 차단할 때 발생
  • 변환 교착(Conversion DeadLock) : 같은 대상에 대해 둘 이상의 세션이 동시에 잠금을 변경하려고 할 때 발생

 

교착 상태를 일으킬 수 있는 리소스

  • 잠금 : 개체, 페이지, 행, 메타데이터, 응용 프로그램 등의 리소스에 대한 잠금을 획득하려고 대기 하는 경우.
  • 작업자 스레드 : 사용 가능한 작업자 스레드를 대기하는 태스크가 교착 상태를 일으킬 수 있음.대기 태스크가 모든 작업자 스레드를 차단하는 리소스를 소유하는 경우 교착상태 발생.
  • 메모리 : 동시 요청이 사용 가능한 메모리보다 많은 메모리 부여를 대기하는 경우.
  • 병렬 쿼리 실행 관련 리소스 : 병렬쿼리에 속하지 않는 하나 이상의 다른 프로세스를 포함할 경우 서로 차단하여 발생. 서버에서 새 쿼리 실행이 시작되거나 시스템에 작업자 스레드가 부족하여 시스템 작업이 예기치 않게 변경되면 교착 상태가 발생.
  • MARS(Multiple Active Result Sets)리소스 : MASRS에서 여러 활성 요청의 인터리브를 제어하는데 사용.

DeadLock 감지하기.

교착 상태 검색은 데이터베이스 엔진 인스턴스의 모든 태스크에 대한 검색을 주기적으로 시작하는 잠금 모니터에서 수행합니다.

  • 기본 간격은 5초로 수행.
  • 잠금 모니터 스레드가 교착 상태를 발견하면 잠금 상태의 빈도에 따라 5초에서 최하 100밀리초까지 교착 상태 검색 간격이 짧아 짐.
  • 잠금 모니터 스레드가 교착 상태 검색을 중지하면 데이터베이스 엔진은 검색 간격을 다시 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 이상사용

    프로세스별 리소스별 순서로 교착상태의 정보를 보고

    

 

  1. Profiler

     

  2. Perfmon

     

  3. 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

 

 

  1. Job Alert

 

 

DeadLocK 최소화 하기

  • 적절한 인덱스 설정.
  • 자원의 한쪽 방향 액세스,
  • 짧은 트랜잭션
  • 테이블 크기 최소화
  • 잠금 제한 시간 설정(SET LOCK_TIMEOUT)
  • 적절한 격리 수준(READ UNCOMMITTED)
  • 하드웨어 성능 향상
  • 지속적인 모니터링

 

 

그런데…실제 서비스에선 이렇게 단순한 그래프로 남지 않는다는거…

 

 

참조 및 참고 사이트


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp



No. Subject Author Date Views
1613 extended events 를 이용한 wait 분석 minsouk 2014.07.25 6264
1612 동일기준에대해 복수컬럼 PIVOT 사용하기 열이 2014.06.27 21320
1611 SELECT / UPDATE / INSERT 쿼리를 쉽게 만들자... [1] Hisory 2014.05.12 10870
1610 ssms 자동 언어 변경이 되면, 이렇게 설정하세요 minsouk 2014.04.27 9566
1609 [QUERY] 그룹 번호 만들기 이스트럭(강동운) 2014.04.11 8682
1608 [QUERY]연승을 구해보자! [2] 이스트럭(강동운) 2014.04.11 9466
1607 VLF 이스트럭(강동운) 2014.04.11 8076
1606 extended events 용 excel view 공개 버전 minsouk 2014.04.07 6400
1605 [TIP] 한 라인에서 여러 테이블 삭제 하기. [3] 엘리엘1 2014.01.03 8616
1604 GhostRecord(인덱스에서 행 삭제시발생) jevida(강성욱) 2013.11.25 13560
1603 프로시저, 함수, 트리거 생성 정보 보기. jevida(강성욱) 2013.11.25 10632
1602 힙테이블에서 행을 삭제하면어떻게 처리 될까? [1] jevida(강성욱) 2013.11.25 9379
1601 SSMS의 디자이너에서 테이블 수정 시 발생하는 영향 [1] jevida(강성욱) 2013.11.25 10526
1600 SQL Server를 활용한 Perfmon 로그 저장 [1] jevida(강성욱) 2013.11.21 14751
1599 VLF 환경과 성능 jevida(강성욱) 2013.11.21 10060
1598 Block 모니터링 jevida(강성욱) 2013.11.21 8073
1597 추적파일을 테이블로 로드하기. jevida(강성욱) 2013.11.21 8895
1596 LOGON 트리거 jevida(강성욱) 2013.11.18 6562
» DeadLock(교착상태) 모니터 하기 [2] jevida(강성욱) 2013.11.18 11352
1594 Tempdb 경합(동시성 강화) jevida(강성욱) 2013.11.18 7566





XE Login