[Oracle] Chapter 8. 오라클의 대기와 LOCK

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

 

시스템을 운영하다 보면 많은 시스템에서 데이터베이스 안에 대기가 발생해 성능이 제대로 나오지 않거나 처리가 지연되기도 하다. 대기의 구조를 제대로 이해하지 않으면 튜닝도 못 하고, 지연을 해소할 수도 없다. 나아가 'Lock 대기', 'Deadlock(교착 상태)'과 같은 장애를 만날 수도 있다. 이런 경우도 대기나 Lock의 구조를 제대로 이해하고 있지 않으면 대처도 어렵고 애플리케이션 담당자에게 설명하기도 어렵다.

 

8.2 데이터베이스에 Lock이 필요한 이유

 

고객이 창고에 맡긴 물건(ID:1)의 내용에 숫자 1을 더하고 싶다고 요청했다. 이 경우 대부분 다음과 같은 로직을 떠올린다.

 

물건의 내용을 변경하는 순서는?

1. 우선 물건(숫자)을 알고 싶으니까 물건을 꺼내자(SELECT한다).

2. 그 후 꺼낸 값에 1을 더하여 다시 맡기자(UPDATE한다).

 

구체적으로 SQL문으로 다음과 같은 SELECT문과 UPDATE문을 떠올리는 분도 있을거다

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

한번 훑어보니 이것으로 충분할 것 같아보인다.

1. 데이터를 알려주세요(SELECT문)

2. 데이터를 테이블에서 꺼내옴

3. 검색 결과가 1이었으니깐 2로 변경합니다(UPDATE문)

4. UPDATE 

5. 데이터를 2로 변경

 

하지만 이렇게 해서는 문제가 발생할 여지가 있다. 발생하는 문제에 관한 힌트는 오라클을 이해하기 위한 키워드인 '병렬 처리를 가능케 하고 높은 처리량을 실현한다'에서 병렬 처리를 가능케 한다는 점이다. 병렬 처리를 하면 실행하는 시점에 따라서는 값이 늘어나지 않는 현상이 발생할 수 있다.

 

SELECT의 타이밍이 거의 같으면 같은 결과를 보게 됨. 그 결과 양쪽 다 같은 값으로 변경하려함. 원래 변경 후의 값이 3이어야 한다(원래 데이터인 1을 고객 A와 고객 B가 각각 1씩을 더한 결과). 하지만 변경 후의 데이터는 2가 되어버렸고, 그 원인은 데이터를 변경할 때 데이터가 보호받지 못했기 떄문이다. 즉, 데이터를 변경하는 작업 도중에는 변경되기 전 데이터를 조회하는 작업이 아닌 작업들을 접근할 수 없도록 해야 한다. 고객A가 데이터를 변경하고 있는 동안에 고객 A 이외에는 해당 데이터를 변경할 수 없도록 변경하는 데이터에 Lock을 걸어서 보호해야만 한다.

Lock의 본질은 '다중 처리를 구현하기 위해 데이터를 보호한다'는 것이다. SQL에서는 다음과 같이 SELECT 단계에서 Lock을 거는 것도 가능하다. '이제부터 내가 이 데이터를 사용할 거니까 아무도 건들지 마'라는 Lock이라고 생각하면 된다.

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

첫 SELECT에 의해 id가 1인 로우에 Lock이 걸린다. 로우 Lock이 걸렸기 떄문에 동일한 로우를 대상으로 UPDATE, DELETE, SELECT FOR UPDATE문을 수행할 수 없어 기다려야 한다. 로우 Lock은 Lock을 건 세션에서 커밋 또는 롤백이 수행될 때 해제된다.

조금 더 설명을 하자면 한 개의 UPDATE문으로도 '값을 1 늘린다'라는 작업은 충분히 처리할 수 있다.

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

이 SQL문에는 문제가 없어 보이나요? 실은 UPDATE문 등의 DML(Data Manipulation Language, 데이터 조작어)은 자동으로 로우 Lock을 걸기 떄문에 문제가 발생하지 않으며, 병렬로 처리를 하더라도 정상적으로 처리된다. 하지만 '이 UPDATE문을 대량으로 동시에 수행하면 id의 값이 1인 로우에 Lock이 발생해서 성능이 나쁘지 않을까? 라고 눈치채신 분들도 있을 것이다. 예상하신 대로 UPDATE문을 대량으로 수행하면 Lock 대기(Lock에 의한 대기)가 발생한다. Lock 대기는 데이터를 보호하기 위해 발생할 수 밖에 없으며, 오라클에서는 이를 대처할 만한 개선 방안이 없으므로 대량의 DML을 실행해야 할 떄는 애플리케이션에서 최대한 이런 부분을 감안하고 작업(프로그램)을 개선할 수밖에 없다.

 

8.3 대기와 Lock 대기

 

계속해서 Lock 대기의 구조에 관한 이야기를 하기 전에, 먼저 '대기'와 '대기 이벤트(wait event)'에 관해 설명하겠다.

일반적으로 대기는 성능을 나쁘게 한다는 좋지 않은 이미지가 있지만, 실제로 대기는 '기다린다(wait)'는 것을 표시하는 것일 뿐이다. 또한, 대기 이벤트를 간단히 말하면 '기다리게 만든 작업'이라 할 수 있다. 대기로 인해 SQL의 처리가 늦어지는 경우도 있으므로 일반적으로 임지가 나쁘지만, 조금만 생각해보면 알 수 있듯이 대기에는 '처리할 것이 없어서 쉬고 있는 대기', '이유가 있어 어쩔 수 없이 하는 대기', '이상 상태 등 쓸데없이 SQL을 기다리게 하는 대기'가 있다. 첫 번쨰를 'Idle대기'라고 하며, 뒤의 두 가지를 'Non-Idle 대기'라고 부르는 경우도 있다.

 

서버 프로세스는 SQL문이 도착할 떄까지 Idle 대기 이벤트인 'SQL*Net message from client' 상태로 대기한다. SQL문이 도착하면 Idle 대기 이벤트 상태가 끝나며 처리를 시작한다. SQL 처리 도중이더라도 필요에 따라서는 I/O등의 대기 이벤트가 발생한다. 예를 들어, 데이터 파일에서 데이터를 읽어오고 있는 'db file sequential read', 'db file scattered read', Lock을 기다리는 'enqueue', 내부 Lock을 기다리는 'latch free'등이 있다. SQL 처리가 끝나면 'SQL*Net message from client'가 되어 기다린다.

 

Idle 대기 이벤트는 SQL의 처리를 기다리게 하지 않는다. 따라서 일반적으로 성능을 분석할 때는 신경을 쓰지 않아도 된다.(실제로 분석 도구 등을 사용하더라도 이런 형태로 분석하는 것은 마찬가지이다. 단, LOB라고 불리는 크기가 큰 데이터형일 때는 네트워크의 송수신(Idle 대기 이벤트)을 반복하고 있으며, 애플리케이션에서 보면 시간이 걸리는 것처럼 보이는 경우가 있다. LOB일 떄는 Idle 대기 이벤트라 하더라도 주의가 필요하다.) 자주 보이는 Idle 대기 이벤트는 'SQL*Net message from client(클라이언트에서 오는 SQL문 등을 기다리고 있다)', 'smon timer', 'pmon timer', 'rdbms ipc message', 'wakeup time manager', 'Queue Monitor Wait'등 이다.

 

8.3.1 Non-Idle 대기는 주의

 

문제는 Non-Idle 대기 이벤트이다. 이유가 있어서 어쩔 수 없이 기다리는 정상적인 예로 디스크I/O 대기가 있다. 오라클은 SQL 처리 도중에 데이터가 필요할 때는 디스크에서 블록을 일겅오며, 그때 대기가 발생한다. 이것은 SQL 처리에 필요한 대기라 할 수 있다.

이상 상태 등 쓸데없이 SQL을 기다리게 하는 대기는 판단하기가 어렵다. 이상 상태라는 것은 '한 사용자가 어떤 테이블이 Lock을 걸어 버린 후에 식사하러 갔다'등 의 경우를 말한다. 이렇게 되면 다른 사용자가 해당 테이블의 데이터를 변경할 수 없으므로 Lock을 통해 데이터는 보호되지만 쓸데없는 대기라 할 수 있다. 또한, 평상시에는 I/O 처리에 10밀리초가 걸리지만 디스크 장애 등으로 인해 100밀리초가 걸렸다고 가정해보자. 그러면 대기 시간은 10배가 되며 그만큼 SQL 처리가 지연된다. 이는 엔드 유저 입장에서 봤을 때 정상일 경우보다 느리므로 쓸데없는 대기이다. 같은 대기 이벤트라도 이런 사례뜰처럼 '어쩔 수 없는 것'과 '이상한(쓸데없는) 것'이 있다. 또한, 어쩔 수 없는 것이라 하더라도 애플리케이션에서의 처리를 줄임으로써 전체 대기 시간을 단축할 수 있는 경우가 있다.

SQL의 처리 과정을 튜닝한다는 관점으로 바라보면, 'Non-Idle 대기 이벤트 + SQL처리에 사용하는 CPU 시간'이 SQL에 걸린 시간이므로 이 부분은 매우 중요한 개념이다. 튜닝을 할 떄 이들 항목에 항상 신경을 쓸 필요가 있다. 대기 이벤트는 스태츠팩(또는 AWR)이나 v$session_wait에서 볼 수 있다. 각 프로세스가 이런 식으로 작업을 처리하거나 다른 프로세스의 처리를 기다리는 등, 다른 프로세스에게 일을 의뢰하고 자신은 기다리는 동작을 마치 사람처럼 수행하기 떄문에 개인적으로는 오라클의 프로세스를 사람의 형태로 의인화해서 표현할 떄가 많다.

 

8.3.2 Lock에 의한 대기는?

 

다음으로 Lock으로 인해 발생하는 대기에 관하여 이야기하겠다. Lock을 걸었다는 것 자체만으로는 대기가 발생하지 않으며, Lock이 걸려 있는 대상에 다시 Lock을 걸려고 했을 때 대기가 발생한다. Lock의 정보는 v$lock 뷰 등에서 확인할 수 있다. v$lock을 조회했을 때 'HELD' 컬럼에 Lock이 보이면 Lock을 보유하고 있는 것이며, 'REQUESTED' 컬럼에 보이면 Lock을 요청하고(기다리고) 있는 것이다. 'LOCK TYPE'은 Lock의 종류를 의미한다.

자주 볼 수 있는 Lock은 'TX'와 'TM'이다. TX는 로우와 관련된 Lock이며, TM은 테이블에 거는 Lock이다. 'MODE'는 동시성 제어를 위한 것으로서 Lock이 어떤 형태로 걸려 있는지를 표시해준다. 예를 들어, TX Lock의 'X(배타적, Exclusive)'는 같은 로우에 대하여 다른 MODE의 Lock을 허용하지 않는다. 이와 비교해서 DML을 할 때 자주 발생하는 RX)또는 RS) MODE의 TM Lock(테이블의 Lock)은 다른 세션이 같은 테이블에 대해 RX(또는 RS) MODE로 TM Lock을 얻을 수 있다. RX MODE의 TM Lock이 걸려 있다면 테이블의 정의를 변경하는 등의 작업(DROP TABLE, ALTER TABLE 등)은 할 수 없지만, 테이블에 대해 여러 개의 트랜잭션을 수행할 수는 있다. 이런 식으로 MODE를 잘 활용하면 필요한 상호 배제를 구현하면서 동시성을 함께 실현할 수 있다. Lock의 상세한 내용은 튜닝 매뉴얼을 참고하자. 또한 v$lock을 조회해보면 MR이라는 Lock이 여러 개 보이지만, 이것은 인스턴스가 가동하면 자동으로 얻는 Lock이므로 무시해도 괜찮다.

 

8.3.3 Deadlock의 구조

 

Deadlock(고장 난 열쇠)은 이름에서도 알  수 있듯이, 고장 나서 작동하지 않는 열쇠라고 생각하면 된다. 구체적으로는 서로가 상대방이 보유하고 있는 Lock을 기다리느라 영원히 작업 처리를 진행할 수 없는 상태를 말한다.

Deadlock(ORA-00060 발생)일 떄는 한쪽이 처리가 오라클에 의해 자동으로 롤백되며, alert 파일과 트레이스 파일에 정보가 표시된다. 오라클의 버전에 따라서는 기록되는 정보의 양이 다를 수 있지만, 9i 이후 버전이라면 Deadlock이 발생한 SQL문을 양쪽 모두 알 수 있어서 애플리케이션을 수정할 때 도움이 된다. Deadlock일 때는 'Deadlock 그래프'라는 Deadlock의 정보가 기록되며, 기본적으로 보는 방법은 v$lock과 마찬가지이다.

 

8.4 Latch의 구조

 

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

 

스태츠팩(또는 AWR)을 보면 Latch는 수십, 수백 개가 존재한다. 이렇게 많은 양의 Latch가 존재하는 이유는 '병렬 처리를 가능케 하고 높은 처리량을 실현'하기 위함이다. Lock을 용도에 따라 최대한 잘게 쪼개서 Lock(latch)의 종류와 수를 늘리는 방법으로 다른 세션들과 경합할 가능성을 줄이고 있는 것이다. 이런 여러 장치들 덕분에 오라클에서는 Latch로 인한 경합은 최소화되고 있다.

하지만 현실에서는 Latch 경합을 많은 시스템에서 볼 수 있다. 왜 이런 것일까? 실은 여기에는 CPU와 OS가 관련이 있다.

최근 OS에서는 여러 처리를 동시에 실행하는 멀티태스킹이 당연시되고 있으며, 선점(preemptive)이라고 불리는 처리 중인 CPU를 가로채는 동작도 존재한다.

 

그러면 이런 OS의 동작으로 인해 Lock(latch)을 가진 세션(프로세스)에서 CPU를 가로채면 어떤 사태가 발생할까? 이런 때는 Latch를 가진 세션은 CPU를 사용할 수없어서 처리를 진행하지 못하고, CPU를 사용할 수 있는 세션은 Latch를 얻지 못해 처리를 진행하지 못하는 상황이 발생한다.

 

이 모습을 사람에 비유하자면, '어떤 사람이 중요한 작업을 처리하는 도중에 직장 상사가 갑자기 새로운 일을 시키는 바람에 중요한 작업 결과를 원하던 사람들이 애꿎게 마냥 기다리는 상황이 되었다'와 비슷할 것이다.

또한, 이러한 현상은 CPU의 스케줄링 외에도 OS의 페이징 등의 바람직하지 않은 현성으로 인해 오라클의 프로세스 처리가 멈춰 버리는 상황에서도 발생한다. 글쓴이 경험으로는 최근에 중/소규모 시슽메에서 발생하는 Latch 경합의 대부분은 CPU 자원의 부족이나 페이징이 그 원인이었다. 바람직하지 않은 대기와 Lock 경합이 격렬하게 발생하는 상황은 오라클에 국한되지 않고 OS 등 IT의 여러 분야에서 많이 볼 수 있으므로 꼭 기억해두자. 또한, 테스트나 운영할 떄 이런 Latch 경합이 보인다면 우선은 CPU 대기가 발생하지 않도록 개선을 시도하자.

 

용어

풀리다/뚫리다, 스파이크/튀다/치다, 죽다

 

- 풀리다, 뚫리다

쌓여서 처리되지 못한 작업들이 처리되어 대기 중인 처리가 없어지는 것을 의미한다. '페이징이 줄어들어서 Latch가 더 이상 보이지 않아. 그리고 겨우 쌓였던 것들이 풀렸어'라고 말하기도 한다.

 

- 스파이크, 튀다, 치다

자원(예를 들면 CPU 사용률)을 모니터링하고 있을 떄 갑자기 튀어 오른 값을 이렇게 부른다. 이런 용어로 불리게 된 것은 자원의 사용 상황을 시계열로 그래프화하면 문제가 있는 상황에서는 튀어 오른 모양을 볼 수 있기 떄문이다. 예를 들어, 'Latch 대기는 여기 CPU 사용률이 튄 시간대에 발생했어'와 같이 말한다.

 

- 죽다

비정상 종료한 것을 말하며 '배치 처리가 죽었다', '데이터베이스가 죽었다'와 같이 이야기한다.

 

8.5 요약

 

- 대기는 단순히 기다리고 있는 상황을 표시하는 것에 지나지 않는다.

- Idle 대기 이벤트와 Non-Idle 대기 이벤트가 있다.

- Lock은 데이터를 보호하기 위해 존재한다.

- Deadlock은 상대방이 소유학 있는 Lock을 요청해서 작업의 처리를 진행하지 못하는 상태다.

- Lock 경합을 해소하기 위해서는 애플리케이션 측에서 대처해야 할 떄가 많다.

- Latch는 오라클 내부의 중요한 것(특히 메모리)을 보호하기 위해 존재한다.

- 대형 시스템이 아닌데 Latch 경합이 심하다면 CPU 자원이 부족하거나 페이징이 발생하는 등의 바람직하지 않은 상태인    지를 확인해본다.

- 오라클을 정상적으로 운영하기 위해서는 토대가 되는 OS도 정상적인 상태여야만 한다.