[Oracle] 08장 오라클의 대기와 LOCK

왜 대기와 오라클의 Lock을 배워야 하는가?

• Lock 대기, Deadlock(교착 상태)와 같은 장애를 만날 수도 있다.

• Lock의 본질: 다중 처리를 구현하기 위해 데이터를 보호한다
• 대기(wait): 기다린다를 표시하는 것
  • Idle 대기: 처리할 것이 있어 쉬고 있는 대기
  • Non-Idle 대기: 이유가 있어 어쩔 수 없이 하는 대기, 이상 상태 등 쓸데없이 SQL을 기다리게 하는 대기
• 대기 이벤트(wait event): 기다리게 만든 작업
  • Idle 대기 이벤트
    • 성능 분석할 때에는 신경쓰지 않아도 된다. SQL의 처리를 기다리게 하지 않는다.
    • SQL *Net message from client(클라이언트에서 오는 SQL문 등을 기다리고 있다)
    • smon timer
    • pmon timer
    • rdbms ipc message
    • wakeup time manager
    • Queue Monitor Wait 등

Non-Idle 대기는 주의

• 이유가 있어 어쩔 수 없이 하는 대기: 디스크 I/O 대기
• 이상 상태 등 쓸데 없이 SQL을 기다리게 하는 대기
  • ex) 한 사용자가 어떤 테이블에 Lock을 걸어버린 후에 식사하러 갔다 등
• 디스크 장애로 인한 지연
  • 같은 대기 이벤트라도 이런 사례들처럼 어쩔 수 없는 것과 이상한 (쓸데 없는) 것

어쩔 수 없는 것이라 하더라도, 애플리케이션의 처리를 줄임으로써 전체 대기 시간을 단축하는 경우도 있다.

• SQL 처리 과정을 튜닝한다는 관점으로 바라보면, Non-Idle 대기 이벤트 + SQL 처리에 사용하는 CPU 시간이 SQL이 걸린 시간이므로, 매우 중요하다.
• 대기 이벤트는 스태츠팩(또는 AWR), v$session_wait에서 볼 수 있다.

Lock에 의한 대기는?

• Lock을 걸었다는 것 자체만으로 대기가 발생하지 않으며, Lock이 걸려있는 대상에서 다시 Lock을 걸려할 떄 대기가 발생한다.

• v$lock에서 확인할 수 있다.

  • 조회 시 REQUESTED 컬럼에 보이면, NONE이 아닌 X면, Lock을 요청ㅇ하고 있는 것이다.
  • Lock Type: TX, TM
  • MR이라는 lock이 여러 개 보이지만, 인스턴스가 기동하면 자동으로 얻는 Lock이므로 무시해도 괜찮다.
    

• /*+ ORDERED */ : 조인하는 순서를 정하는 힌트로, SQL문의 성능 저하를 막아준다.

Lock Type : TX, TM

TX

• row와 관련된 lock
• MODE는 동시성 제어를 위한 것: Lock이 어떤 형태로 걸려있는지를 표시해준다.
  • 예를 들어, TX Lock의 X(배타적, Exclusive)는 같은 row에 대하여 다른 MODE의 Lock을 허용하지 않는다.

TM

• 테이블에 거는 Lock

Deadlock의 구조

• 고장난 열쇠: 고장나서 작동하지 않는 열쇠
• 서로가 상대방이 보유하고 있는 Lock을 기다리느라, 영원히 작업 처리를 진행할 수 없는 상태를 말한다.
• Deadlock(ORA-00060 발생) 일 때는 한쪽의 처리가 오라클에 의해 자동으로 롤백되며,
  alert 파일과 트레이스 파일에 정보가 표시된다.
• 오라클의 버전에 따라서는 기록되는 정보의 양이 다를 수 있지만, 9i 이후 버전이라면 Deadlock이 발생한 SQL문을 양쪽 모두 알 수 있어 애플리케이션 수정 시 도움이 된다.

Latch의 구조

• 다중 처리를 구현하기 위한 Lock

• 일반적인 Lock과 다른 부분은, Latch는 오라클 내부에서 자동으로 얻으며, SQL을 한 번 실행하기 위해서는 여러 Latch를 얻고 해제하는 것을 반복한다는 것이다.

요약

• 대기는 단순히 기다리고 있는 상황을 표시하는 것에 지나지 않는다.
• Idle 대기 이벤트, Non-Idle 대기 이벤트
• Lock은 데이터를 보호하기 위해 존재한다.
• Deadlock은 상대방이 소유하고 있는 Lock을 요청해서 작업의 처리를 진행하지 못하는 상태.
• Lock 경합을 해소하기 위해서는 애플리케이션 측에서 대처해야 할 때가 많다.
• Latch는 오라클 내부의 중요한 것(특히 메모리)을 보호하기 위해 존재
• 대형 시스템이 아닌데, Latch 경합이 심하다면 CPU 자원이 부족하거나 페이징이 발생하는 등의 바람직하지 않은 상태인지를 확인한다.
• 오라클을 정상적으로 운영하기 위해서는 토대가 되는 OS도 정상적인 상태여야 한다.