[DB] 데이터베이스의 대기와 Lock

이 글은 오라클을 기준으로 설명된 글입니다.

Lock이란?

먼저 오라클에 왜 Lock이 필요한지 예를 한번 들어보겠습니다.

 

만약 고객이 창고에 ID=1인 물건의 내용에 숫자 1을 더하고 싶다고 요청했습니다.

요청을 처리하기 위해 SQL문은 아래와 같습니다.

SELECT counter FROM counter_table WHERE id = 1;
UPDATE counter_table SET counter = <새로운 값> WHERE id = 1;

하나의 요청만을 보니 아무런 문제가 없을것 같아보입니다.

 

하지만 오라클은 병렬처리가 가능하다는 특징이 있습니다.

 

만약 A라는 고객이 ID=1 인 물건에 숫자 1을 더하고 싶다고 요청을 해서 SQL을 처리하고 있는 중에 B라는 고객이 ID=1인 물건에 숫자 1을 더하고 싶다고 요청을 했다고 생각해봅시다.

 

1. 고객A가 데이터를 검색합니다.(SELECT)
2. 데이터(1)를 꺼내 옵니다.
3. 고객B가 데이터를 검색합니다.(SELECT)
4. 데이터값이 아직 변경되기 전이므로 데이터(1)를 꺼내 옵니다.
5. 고객A는 결과가 1이었으니까 2로 변경합니다.(UPDATE)
6. 고객B도 결과가 1이었으니까 2로 변경합니다.(UPDATE)

 

원래라면 A,B 고객에 의해 1씩 더해져 물건의 값은 3이 되어야 합니다. 하지만 위의 경우는 A,B의 모든 요청을 처리한 후의 데이터는 2가 되어버립니다.

 

이러한 일이 일어나는 원인은 데이터를 변경할때 데이터가 보호받지 못했기 때문입니다.

 

데이터를 보호하기 위해서는 고객 A가 데이터를 변경하는 동안에는 고객 A이외에는 해당 데이터를 변경할 수 없도록 변경하는 데이터에 Lock을 걸어서 보호해야만 합니다.

 

즉 Lock이란 '다중 처리를 구현하기 위해 데이터를 보호하는'것 입니다.

SELECT counter FROM counter_table WHERE id = 1; #  <- Lock이 걸린다
FOR UPDATE;  # 대기
UPDATE counter_table SET counter = <새로운 값> WHERE id = 1;

 

SQL에서는 SELECT 단계에서 Lock을 거는 것도 가능합니다.

첫 SELECT에 의해 id가 1인 로우에 Lock이 걸립니다.

로우 Lock이 걸렸기 때문에 동일한 로우를 대상으로는 SQL 작업을 수행할 수 없어 기다려야 합니다.

 

 

그러면 아래의  SQL은 SELECT 단계에서 Lock을 거는 과정이 없이 바로 데이터 변경이 일어납니다. 이러한 경우는 어떻게 될까요??

UPDATE counter_table SET counter = counter+1 WHERE id = 1;

 사실은 UPDATE문 등의 DML(Data Manipulation Language)은 자동으로 로우 Lock을 걸기 때문에 문제가 발생하지 않습니다.

 

다만 UPDATE문을 대량으로 수행하면 Lock대기가 발생합니다. Lock 대기는 데이터를 보호하기 위해 발생할 수 밖에 없기때문에, 대량의 DML을 실행해야 할 때는 이런 부분을 감안하고 작업을 개선할 수 밖에 없습니다.

대기와 Lock 대기

대기

대기에는 크게 Idle 대기, Non-Idle 대기 두가지가 있습니다.

 

Idle 대기

• "처리할 것이 없어서 쉬고 있는 대기"
• Idel 대기 이벤트는 SQL의 처리를 기다리게 하지 않으므로 성능을 분석할 때는 신경을 쓰지 않아도 된다.

 

 

Non-Idle 대기

• "이유가 있어 어쩔 수 없이 하는 대기", "이상 상태 등 쓸데없이 SQL을 기다리게 하는 대기"
• "이유가 있어 어쩔 수 없이 하는 대기"는 예로 디스크 I/O 대기가 있습니다. 이것은 SQL 처리에 필요한 대기라고 할 수 있습니다.
• 이상 상태라는 것은 '한 사용자가 어떤 테이블에 Lock을 걸어 버린 후에 식사하러 갔다'등의경우를 말합니다.
• 이상 상태의 경우 다른 사용자가 해당 테이블의 데이터를 변경할 수 없으므로 Lock을 통해 데이터는 보호되지만 쓸데없는 대기라 할 수 있습니다.

이상 상태 등 쓸데없이 SQL을 기다리게 하는 대기는 판단하기 어렵습니다.

 

SQL의 처리 과정을 튜닝한다는 관점으로 바라보면 'Non-Idle 대기 이벤트 + SQL 처리에 사용하는 CPU 시간 = SQL에 걸린 시간' 이므로 매우 중요한 부분입니다.

 

Lock 대기

다음으로 Lock으로 인해 발생하는 대기에 알아보겠습니다.

 

Lock을 걸었다는 것 자체만으로는 대기가 발생하지는 않으며, Lock이 걸려 있는 대상에 다시 Lock을 걸려고 했을 때 대기가 발생합니다.

 

자주 볼 수 있는 Lock은 TX와 TM입니다. TX는 로우와 관련된 Lock이며, TM은 테이블에 거는 Lock입니다. 오라클은 'MODE'를 통해 Lock이 어떤 형태로 걸려 있는지를 표시해줍니다.

 

예를 들어, TX Lock은 로우에 대하여 다른 MODE의 Lock을 허용하지 않습니다.

이와 비교해서 DML을 할 때 자주 발생하는 RX MODE로 TM Lock을 얻을 수 있습니다.

RX MODE의 TM Lock이 걸려 있다면 테이블의 정의를 변경하는 등의 작업은 할 수 없지만, 테이블에 대해 여러 개의 트랜잭션을 수행할수는 있습니다.

이런 식으로 MODE를 잘 활용하면 필요한 상호 배제를 구현하면서 동시성을 함께 실현할 수 있습니다.

 

DeadLock

DeadLock이란 서로가 상대방이 보유하고 있는 Lock을 기다리느라 영원히 작업 처리를 진행할 수 없는 상태를 말합니다.

 

DeadLock일 때는 한쪽의 처리가 오라클에 의해 자동으로 롤백되며, alert 파일과 트레이스 파일에 정보가 표시됩니다. 오라클의 버전이 9i 이후 버전이라면 Deadlock이 발생한 SQL 문을 양쪽 모두 알 수 있어서 애플리케이션을 수정할 때 도움이 됩니다.

Latch의 구조

Latch도 다중 처리를 구현하기 위한 Lock입니다.

 

일반적인 Lock과 다른 부분은 Latch는 오라클 내부에서 자동으로 얻으며, SQL을 한 번 실행하기 위해서는 여러 Latch를 얻고 해제하는 것을 반복한다는 것입니다. Latch는 메모리나 데이터를 조작할 때 상호 배타적(mutual exclusive)으로 처리하지 않아 데이터가 손상되는 것을 방지하기 위해 사용됩니다.

 

오라클의 내부를 살펴보면 Latch는 수십, 수백 개가 존재합니다. 이렇게 많은 양의 Latch가 존재하는 이유는 병렬 처리를 가능케 하고 높은 처리량을 실현하기 위함입니다.

 

Lock을 용도에 따라서 최대한 잘게 쪼개서 Lock(Latch)의 종류와 수를 늘리는 방법으로 다른 세션들과 경합할 가능성을 줄이고 있습니다.

 

하지만 현실에서는 Latch 경합을 많은 시스템에서 볼 수 있습니다. 여기에는 CPU와 OS가 관련이 있습니다. 예시를 한번 살펴보겠습니다.

1. 프로세스 A가 작업을 처리하기 위해 Latch를 획득하고 작업을 처리하고 있습니다.
2. OS가 CPU를 오랜 시간 독점해서는 안되기 때문에 CPU를 사용하고 있던 도중이더라도 CPU를 강제로 프로세스 B에게 할당합니다. (선점 방식)
3. 프로세스 B는 CPU를 받았지만 A가 프로세스가 Latch를 획득한 상태이므로 작업을 수행하지 못하고 대기합니다.

위와 같이 CPU를 사용할 수 있는 세션은 Latch를 얻지 못해 처리를 진행하지 못하는 상황이 발생합니다. 또한 이러한 현상은 CPU의 스케줄링 외에도 OS의 페이징 등의 바람직하지 않은 현상으로도 발생합니다.

 

따라서 테스트나 운영할 때 이런 Latch 경합이 보인다면 CPU 대기가 발생하지 않는지 체크해봐야 합니다.

 

 

참고자료

• <그림으로 공부하는 오라클 구조> http://www.kyobobook.co.kr/product/detailViewKor.laf?mallGb=KOR&ejkGb=KOR&barcode=9791185890302 

그림으로 공부하는 오라클 구조 - 교보문고