[DB] MSSQL 데드락(교착상태) 해결방법

데이터베이스를 운영하여보면 "트랜잭션(프로세스 ID 57)이 잠금 리소스에서 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다". 트랜잭션을 다시 실행하십시오." 와 같은 에러가 발생하는 경우가 있습니다. 

1. 데드락 발생원인 (교착상태에 빠지는이유)

아래 그림과 같이 트랜잭션1이 Table1을 업데이트후 Table2를 업데이트 하려고 할때, 트랜잭션2가 이미 Table2에 업데이트 작업을 진행중인 경우 해당작업을 완료할 때까지 대기후 처리합니다.  트랜잭션2가 Table2업데이트 완료후 Table1을 업데이트 하려고 할때 트랜잭션1이 Table1의 작업을 하고 있어서 완료 될때까지 대기한다. 이렇게 두개의 트랜잭션작업이 서로 완료 될때까지 기다리게되며 결과적으로 아무것도 완료가 되지 않는 상태가됩니다.이렇게 해서 교착상태가 빠지게 됩니다.  

실제로 데이터베이스상에서 데드락을 발생시켜보겠습니다. 강제로 waitfor delay를 사용하여 update구간에 5초 대기를 시켜보면 아래와 같이 교착상태가 발생하는것을 알수있습니다. 

2. 해결방법(데드락 최소화하기) 
1) 트랜잭션 진행방향을 같은방향으로 처리 
트랜잭션1 : Table1 업데이트 후 Table2업데이트 , 트랜잭션2 : Table1업데이트 후 Table2업데이트로처리하게되면 업데이트시 begintran로 인하여 블록킹이 될수는 있으나 데드락이 발생할 확률이 줄어지게됩니다. 
2)트랜잭션 처리속도를 최소화
update문이 빨리 처리되었으면 데드락이 발생할 확률이 줄어듭니다. 당연한 애기지만 이미 트랜잭션속도가 빨라commit처리가 되면 다른트랜잭션에서 테이블이 잠길일이 없습니다. 
3) SET LOCK_TIMEOUT문을 이용하여 잠금해제 시간을 조절
데드락이 발생했을때 잠금 시간을 해제해주면 무기한 대기하지 않고 만료가 되어 다음작업을 진행할수 있습니다. 단위는 밀리세컨드입니다.  LOCK_TIMEOUT값을 10초로 설정해보겠습니다. -1(기본값)은 제한시간없이 무기한대기를 나타냅니다.

-- LOCK 타임아웃 10초설정
SET LOCK_TIMEOUT 10000

SELECT @@LOCK_TIMEOUT