DB 데드락(deadlock) 해결사례

운영중인 DB에서 데드락이 발생하여 트랜잭션이 중단되는 문제가 발생하였다.

해당 기능의 중요성 및 안정성을 감안하였을때, 쿼리문을 변경하거나 트랜잭션 범위와 격리수준 변경을 할수없었고 다른방법을 찾아보고있었다. 

특정 작업을 처리할때, 특정 테이블을 동시다발적으로 접근하면서 데드락이 발생한것으로 보이지만,

관련 로그나 모니터링 환경이 구축되어있지않은 상황에서 정확히 어떤 트랜잭션에서 어떤 작업이 문제를 일으키는지 파악할 필요가 있었다.

 

1. 데드락 모니터링 환경 구축하기

 

MSSQL은 SSMS(SQL SERVER MANAGEMENT STUDIO)라는 DB 관리도구 툴을 제공한다.

사용하는 MSSQL 버전에 맞추어 SSMS를 설치한뒤, DB 커넥션을 맺어 접속한다.

이후, MSSQL의 TRACE PLAG중에서 데드락에 관련된 플래그인 '1222'를 활성화 시킨다.

 

DBCC TRACEON(1222,-1)

 

쿼리창에 위 명령어를 입력하면 SQL SERVER에서 데드락 상황이 발생했을 경우 자세한 디버깅 정보를 에러로그에 기록하는데,

• 데드락이 발생한 트랜잭션과 자원: 어떤 트랜잭션들이 데드락 상태에 있었는지, 그리고 어떤 자원에서 데드락이 발생했는지에 대한 정보를 제공합니다.
• 트랜잭션 간의 대기 상태: 데드락을 일으킨 각 트랜잭션이 어떤 자원을 기다리고 있었는지에 대한 자세한 내용을 포함합니다.
• 문제 해결을 위한 정보: 어떤 트랜잭션이 데드락에서 "희생"이 되어 종료될 것인지, 그리고 왜 그런 결정을 내렸는지에 대한 정보를 제공합니다.

위와 같은 정보를 얻을수 있다.

추가적으로  -1 옵션은 특정 세션이 아닌 전체 세션에서 발생하는 에러에 대해 적용하는 것으로 DB 커넥션을 타고 들어오는 WAS등 다양한 세션에서 DB에 접근하는 세션들에 대해 데드락 에러로그를 수집하도록 하는것이다.

 

2. 데드락 원인 트랜잭션 파악 및 쿼리분석

 

deadlock-list
 deadlock victim=process41758a928
  process-list
  
   process id=process41758a928 
   	waitresource=PAGE: 8:1:1409558  waittime=4285 lockMode=IU 
    spid=80
    clientapp=Microsoft JDBC Driver for SQL Server isolationlevel=read committed (2) 
    

WAS로부터 실행한 SQL 쿼리작업 1
  
   
   process id=process4e0a8d0c8  
   waitresource=PAGE: 8:1:1409638  waittime=4285 lockMode=U
   spid=66 
   clientapp=Microsoft JDBC Driver for SQL Server 
   isolationlevel=read committed (2) 
    

WAS로부터 실행한 SQL 쿼리작업 2

  resource-list
   pagelock fileid=1 pageid=1409558 dbid=8 subresource=FULL objectname= id=lock7f7841f00 mode=U associatedObjectId=72057594053394432
    owner-list
     owner id=process408be6cf8 mode=U
    waiter-list
     waiter id=process41758a928 mode=IU requestType=wait
   pagelock fileid=1 pageid=1409638 dbid=8 subresource=FULL objectname= id=lock83b7fa680 mode=IU associatedObjectId=72057594053394432
    owner-list
     owner id=process41758a928 mode=IU
    waiter-list
     waiter id=process4e0a8d0c8 mode=U requestType=wait
   exchangeEvent id=Pipe537dfea80 WaitType=e_waitPipeGetRow nodeId=3
    owner-list
     owner id=process4e0a8d0c8
    waiter-list
     waiter id=process408be6cf8

 

 

SQL SERVER에서 수집한 데드락 에러로그를 분석해보면 위와 같다.

위의 에러로그로 부터 알수있는 데드락 정보는

 

1. deadlock victim : process41758a928

먼저, 데드락 희생 프로세스는 a928이다. 

 

2. process-list : 데드락에 연관된 프로세스들
데드락을 유발시킨 프로세스들에 대한 정보를 알려준다.

process41758a928 와 process4e0a8d0c8 가 서로 충돌하여 데드락 상황을 유발시켰다.

 

3. resource-list : 각 프로세스들이 요청을 얻기위한 리소스들에 대한 정보

 

먼저, 1409558 page와 1409638 page에 lock이 걸렸다. 

  process41758a928(spid=80) 프로세스는 1409638 page에 대한 잠금을 보유한 채 1409558 page에 대한 잠금을 얻기위해 기다리고있다.

process4e0a8d0c8(spid=66)프로세스는 1409558 page에 대한 잠금을 보유한채 1409638 page에 대한 잠금을 얻기위해 기다리고 있다.

위의 상황에서 데드락이 발생한 것이다.

 

 

이제, 각각의 프로세스들이 보유한 페이지에 대해 어떠한 데이터들이 있는지 확인해보았다.

 

DBCC TRACEON(3604); 
DBCC PAGE(8, 1, 1409558, 3);

 

TRACE PLAG 3604는 DBCC 명령어의 출력결과를 사용자에게 보이도록 쿼리결과창에 나타내는 코드이다.

해당 플래그를 ON한뒤, DBCC PAGE 명령어를 통해 페이지에 어떠한 데이터들이 들어가있는지 확인할 수 있다.

DBCC PAGE(DBID, FILEID, PAGEID, OPTION)에서 DB, FILD,PAGE ID정보는 resource-list를 통해 얻을수있다.

 

위 명령어를 통해 두개의 충돌되는 프로세스가 쿼리작업을 하면서 잠금을 얻기위해 시도한 페이지에 어떠한 데이터(컬럼)이 있는지 확인 할 수있다.

 

3. 원인파악후 개선작업

 

나의 경우, 위 작업을 통해 각각의 충돌되는 트랜잭션 작업에서 어떠한 컬럼이 문제되는지 확인할 수 있었다.

동일한 테이블에서 서로 다른 2개의 쿼리 UPDATE 작업에서 WHERE 조건을 타면서 특정 3개의 컬럼에 대한 잠금요청이 광범위하게 이루어진다는 사실을 알게되었고,

SQL 쿼리문을 수정하거나, 트랜잭션 로직을 변경할수없는 상황에서 해당 특정 3개의 컬럼에 대하여 INDEX 설정을 통해 

SQL SERVER 내부에서 해당 테이블의 특정 컬럼에 빠르게 접근하여 페이지 잠금 수준을 줄이는 방향으로 개선하였고,

해당 방법을 통해 데드락 문제를 해결 할 수 있었다.