DATOR


SQL Server 2000에서 트랜잭션 복제와 교착상태 Performance Tuning


 

< SQL Server 2000에서 트랜잭션 복제로 인한 교착상태 >

SQL Server에서는 select 문장도 공유 잠금(shared lock)을 얻기 때문에, 수정과 조회가 잘못 만났을 경우에도 교착상태가 발생할 수 있다.

테스트에 앞서 결론부터 얘기하자면, 트랜잭션 복제를 사용하는 환경에서는 교착상태(데드 락)를 완전히 피하기는 불가능하다.

즉, 트랜잭션의 길이를 최대한 짧게 한다든지 한 트랜잭션에 사용된 테이블들을 같은 방향으로 액세스하는 방법 등을 사용하여 교착상태의 발생을 최소화할 수는 있겠지만, 완전히 막는 것은 불가능하다는 것이다.

아래의 테스트 과정은 모 부동산 사이트의 환경을 그대로 시뮬레이션한 것이다.

이 사이트의 경우 엔코아의 튜닝 작업 전에는 일일 교착상태 발생이 1000건 이상에 육박했지만, 인덱스 조정 및 SQL 수정 등의 간단한 튜닝 작업만으로도 교착상태 발생을 50건 이하로 낮추었다. 완벽한 튜닝이 이뤄진다면 거의 0에 가깝도록 최소화할 수 있을 것이다.

트랜잭션 복제가 어쩔 수 없이 교착상태를 발생시키기는 하지만, 사용자의 노력에 따라 얼마든지 개선이 가능한 것이다.

그러면 다음의 테스트 과정을 따라가 보자.

* 각 그림에 대한 설명은 그림의 바로 밑에 있다.

1.     트랜잭션 복제가 교착 상태(데드락) 유발할 밖에 없음을 다음에 진행할 테스트를 통해 확인하고자 한다.

2.     앞으로 진행될 테스트를 이해하려면 최소한 다음의 내용을 숙지하고 있어야 한다.

ü         트랜잭션의 개념

ü         온라인 설명서의 [암시적 트랜잭션] -> [set implicit_transactions]

ü         온라인 설명서의 [공유 잠금 모드] -> [잠금 아키텍처]

ü         온라인 설명서의 [잠금 모니터링] -> [잠금 정보 표시]

 

-01-clip_image001-(7).jpg

 

ü        게시자의 데이터를 확인한다.

 

 

-02-clip_image003-(5).jpg

 

ü        구독자의 데이터를 확인한다.

ü        컬럼의 값은 물론 msrepl_tran_version 값도 정확히 일치한다.

 

-03-clip_image005-(4).jpg 

 

ü        게시자의 데이터를 수정한다.

ü        세션에서 col1 = 5000 건을 삭제하고, 다른 세션에서 col1 between 1 and 4999 건을 수정한다.

ü        이것은 col1 = 5000 데이터가 삭제되는 시점과 col1 = 4999 데이터가 수정되는 시점 사이의 시간을 많이 증가시킴으로써, 사이의 어느 시점에 다른 SQL 실행하기 위함이다. 내용은 다음에 진행할 테스트를 따라가 보면 이해가 것이다.

 

 

-04-clip_image007-(2).jpg 

 

ü        게시자의 프로필러를 확인한다.

ü        개의 세션에서 실행한 update문의 트레이스가 기록되어 있다.

 

-05-clip_image009-(2).jpg

 

ü        배포자의 프로필러를 확인한다.

ü        각각의 세션에서 DML문을 따로 수행했지만, 배포자는 개의 트랜잭션을 묶음으로 처리한다.

ü        차례 테스트 결과, 세션의 DML 하나로 묶이는 경우도 있었고 따로 수행되는 경우도 있었다. 이것은 개의 트랜잭션이 배포 에이전트의 시간 단위(트랜잭션 로그를 확인하는) 포함되는지 여부에 따라서 결정되는 것으로 보인다.

ü        어쨌든 다른 세션의 트랜잭션들이 하나의 묶음으로 처리될 있음을 확인하였다.

 

-06-clip_image011-(2).jpg

 

ü        구독자의 프로필러를 확인한다.

ü        게시자에서 수행한 delete문이 먼저 배포되었고, 뒤이어 update문이 여러 개로 분리되어 배포되고 있음을 확인할 있다. col1 = 5000 건이 삭제되는 트레이스가 보이면 곧바로 구독자의 세션에서 다음 페이지의 SQL 수행한다. col1 = 4999 수정되는 시점과의 시간 간격을 충분히 확보했으므로 delete update사이에 다른 SQL 실행하는 것이 어렵지 않다.

ü        위의 그림에서는 보이지 않지만 배포된 트랜잭션을 구독하는 프로세스(아래 그림에서 spid 53) 암시적 트랜잭션 모드가 on 상태(set implicit_transactions on) 진행된다. 이것은 구독자를 재시작하면 확인이 가능하다. 아래 그림을 참고하기 바란다.

ü        결국, 게시자에서 별개로 진행한 트랜잭션이 구독자에서는 하나의 트랜잭션으로 묶였음을 의미한다.


 

 -07-clip_image013-(2) 사본.jpg

 

ü        구독자에서 SQL 실행한다.

ü        현재 delete 작업에 의해서 col1 = 5000 배타적 잠금이 걸려 있는 상태이므로, 위의 select문을 실행하면 col1 = 4999 공유 잠금을 걸어 놓고 col1 = 5000 대해서 공유 잠금을 얻으려고 대기한다.

ü        상태에서 update 작업이 col1 = 4999 대해서 배타적 잠금을 얻으려고 하지만, 직전에 수행한 select 의한 공유 잠금 때문에 대기해야 하므로 결국은 교착 상태가 발생하게 된다.

ü        세션의 select문이 취소되었으므로 게시자에 실행한 delete update 작업이 완료되었을 것이다.

 

-09-clip_image017-(2).jpg    

 

Leave Comments