컴퓨토피아

SQL Server for Developer: 관리자를 위한 튜닝 가이드

잠금

차주언 | SQL 컨설턴트

잠금

수칙1과2.시간이 너무오래걸리는 트랜잭션이나 데드락을 모니터링 해서 뽑아내는가?

위의 두가지 사항은 손쉽게 프로필러로 뽑아낼 수 있습니다. 다음의 따라하기는 단순한 모니터링일뿐 근원적인 해결책은 아 닙니다. 해결은 도움말 잠금편을 읽어보고 해결해야만 합니다. 또는 외부에 해결을 의뢰하기 위해 파일로 저장할 수도 있습 니다.

[따라하기]
01.데드락 유발쿼리 입니다 쿼리창을 열어 각각의 쿼리를 거의 동시에 수행해 봅시다. 물론 아래의 데드락 해결방법은 쿼리 실행 순서를 둘다 동일하게 tb_test,tb_test2순으로 작성하면 아무런 문제가 없어집니다.

02.프로필러를 실행 시킵니다. 새 추적을 클릭합니다

03. 데드락을 감시하고자 하는 서버에 접속합니다.

04. 데드락을 검사하려면 이름을 정하고

05. 이벤트텝에서 잠금 항목의 데드락 및 체인을 선택합니다.

06. 데드락으로 표시되는부분은 붉게 표시되어 나타납니다. 개발자와 상담하여 데드락을 유발시키는 쿼리를 라이브락으로 변경 시켜주어야합니다.

07.기다리면 시스템에서 발생하는 쿼리들중 데드락인 부분은 붉게 표시되어 나타납니다. 추가로 앞서 제작해 두었던 sp_blocker_pss80 도 꼭 실험을 해보도록 해야합니다.

그렇다면 보다 자세한 잠금관련 공부를 해보겠습니다

잠금관련 권고사항
가. 트랜잭션은 가능한 짧게 한다
나. 데드락을 피하기 위해
  A. 같은방향으로 트랜잭션을 진행합니다.
  B. 잠금수준을 올려줄 수도 있어야 합니다
다. 잠금수준을 내려서 불필요한 잠금을 없애야합니다(read uncommitted)
라. 트리거를 사용하지 않습니다.
마. 대규모 데이터 변경시에만 커서를 사용합니다.

[따라하기- 트랜잭션은 왜 짧아야 하는가?]
트랜잭션이 길어지면 잠금이 길어지고 그만큼 다른 작업도 지연된다 다음 대표적인 잠금대기 실험을 해보겠습니다.

1.다음과 같이 창을 두개 만듭니다. CTRL+N을 클릭하여 새창을 만든 후 메뉴에서 창 > 새로 바둑판식 배열을 선택합니다.

2.왼쪽창에선 트랜잭션 중간에 멈추고 오른쪽에서 그 트랜잭션이 잠그는 부분과 충돌하는 장면을 연출해 봅시다.

3.왼쪽창을 다 실행한 다음 오른쪽을 실행합니다.(price는 기존의 값이 아닌값이면 됩니다)

3.실행결과 오른쪽 쿼리는 무한대기가 걸린다. 언제까지 무한대기냐면 왼쪽에서 commit tran (rollback tran)할때까지 입니 다. 즉 트랜잭션이 끝날때까지 입니다. 이를 라이브락 이라합니다.

4.성공적으로 오른쪽 쿼리가 실행됩니다. 따라서 트랜잭션은 빠르면 빠를 수 록 좋습니다.

[따라하기 - 잠금수준을 낮춰서 잠금 충돌을 회피합시다]
1.왼쪽 쿼리를 commit tran을 하지 않은 상태에서 다음과 같은 오른쪽 쿼리를 실행합니다.

2.실제 데이터가 아닌 버퍼에 있는 값을 읽어오므로 잠금과는 상관이 없는 readuncommitted를 사용한 예제입니다.

[따라하기 - 데드락을 피하는 노력들!]
1.실험에 사용할 테이블 두개를 만듭니다.

2.데드락 중에서 순환데드락을 구현해 보겠습니다 순환 데드락이란 서로가 서로 업데이트할 내용을 막고 있는 형태입니다.

3.위의 상황은 업데이트 잠금 때문에 다른 트랜잭션의 업데이트가 실행되지 못하는 것 입니다 해결하려면 다음과 같이 해줘 야 합니다. 같은 방향으로 트랜잭션을 진행합니다.

[따라하기 - 변환 데드락]
1.같은 방법으로 이번엔 잠금수준을 변경함으로써 데드락을 해소해 보겠습니다. 우선 단순히 양쪽쿼리는 select 후 update 를 시도합니다. 여기서 repeatableread를 한 이유는 select(공유)잠금이 트랜잭션 동안 유지되게 하기 위해서입니다.

2.select 와 공유는 동시에 되지 않습니다. 차라리 select 할때 다른 트랜잭션도 select못하도록 잠금 수준을 향상 시킵니다 .

데드락의 대표예 두가지를 성공적으로 해결했습니다. 결론을 다시 반복하자면 순환 데드락의 경우는 트랜잭션 방향을 일방 통행으로 변환데드락은 잠금 수준을 조정함으로써 해결 해야합니다.

제공 : DB포탈사이트 DBguide.net