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

📌 8장에서는 SQL을 처리할 때 발생하는 대기(wait)에 관해 알아봅니다. 또한, 대기를 발생시켜 '성능 문제'를 발생시키는 나쁜 이미지가 있지만 꼭 그렇지만은 않은 Lock에 대해서도 살펴봅니다.

 

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

· 시스템 운영시 데이터베이스 안에 대기가 발생해 성능이 제대로 나오지 않거나 처리가 지연되기도 함. 이 때 대기의 구조를 제대로 이해하지 않으면 튜닝도 못하고, 지연도 해소할 수 없음

 

 

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

· Lock의 본질은 '다중 처리를 구현하기 위해 데이터를 보호한다'는 것이며, 병렬 처리를 가능케 하고 높은 처리량을 실현할 수 있음

· 병렬 처리를 하면 실행하는 시점에 따라서는 값이 변경되지 않는 현상이 발생(ex. 동시에 SELECT하여 UPDATE 하는 쿼리 송신). 이는 데이터를 변경할 때 데이터가 보호받지 못했기 때문

· 즉, 데이터를 변경하는 작업 도중에는 변경되기 전 데이터를 조회하는 작업이 아닌 작업들은 접근할 수 없도록 해야 함. 고객 A가 데이터를 변경하고 있는 동안에는 고객 A 이외에는 해당 데이터를 변경할 수 없도록 변경하는 데이터에 Lock을 걸어서 보호해야 함.

/* 첫 SELECT에 의해 id가 1인 로우에 Lock이 걸림 */
SELECT counter FROM counter_table WHERE id = 1
FOR UPDATE; -- ◁ Lock이 걸림
Update counter_table SET counter = <새로운 값> WHERE id = 1;

· Lock이 걸렸기 때문에 동일한 로우를 대상으로 UPDATE, DELETE, SELECT FOR UPDATE문을 수행할 수 없어 기다려야 함

· 로우 Lock은 Lock을 건 세션에서 커밋 또는 롤백이 수행될 때 해제됨 

/* DML은 자동으로 로우 Lock을 검 */
UPDATE counter_table SET counter = counter+1 WHERE id = 1;

· DML은 자동으로 로우 Lock을 걸기 때문에 문제가 발생하지 않으며, 병렬로 정상 처리됨

· 다만, 과다한 DML문이 수행되면 Lock대기도 발생하기 때문에 Lock 대기는 데이터를 보호하기 위해 발생할 수 밖에 없으며, 오라클에서는 이를 대처할 만한 개선 방안이 없으므로 애플리케이션에서 최대한 이런 부분을 감안하고 작업(프로그램)을 개선할 수 밖에 없음

 

 

8.3 | 대기와 Lock 대기

· '대기'는 성능을 나쁘게 한다는 좋지 않은 이미지가 있지만, 실제로 대기는 '기다린다(wait)'는 것을 표시하는 것일 뿐임

· '대기 이벤트'는 '기다리게 만든 작업'이라고 할 수 있음. 스테츠팩(또는 AWR)이나 v$session_wait에서 볼 수 있음

· 대기로 인해 SQL의 처리가 늦어지는 경우도 있으므로 일반적으로 이미지가 나쁘지만, 대기에는 크게 세가지 종류의 대기가 있음.

 

[ 대기(wait)의 종류]

① Idle 대기 : 처리할 것이 없어서 쉬고 있는 대기, SQL의 처리를 기다리게 하지 않음, 성능 분석 시 신경 안써도 됨

(ex. 'SQL*Net message from client', 'smon timer', 'pmon timer', 'rdbms ipc message', 'wakeup time manager', 'Quene Monitor Wait' 등)

② Non-Idle 대기 : 이유가 있어 어쩔 수 없이 하는 대기

(ex. 디스크 I/O 대기)

③ 이상 상태 등 쓸데없이 SQL을 기다리게 하는 대기

(ex. 한 사용자가 어떤 테이블에 Lock을 걸어버린 후 식사하러 간 경우 등)

 

8.3.1 Non-Idle 대기는 주의

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

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

· 대기 이벤트는 스태츠팩(또는 AWR)이나 v$session_wait에서 볼 수 있음

 

8.3.2 Lock에 의한 대기는?

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

· Lock 정보는 v$lock 뷰 등에서 확인할 수 있음

 

[ v$lock 조회 시 내용]

· 'HELD' 컬럼에 Lock이 보이면 Lock을 보유하고 있는 것

· 'REQUESTED' 컬럼에 보이면 Lock을 요청하고 있는 것

· 'LOCK TYPE'은 Lock의 종류를 의미

① TX : 로우와 관련된 Lock

② TM : 테이블에 거는 Lock

③ MODE : 동시성 제어를 위한 것으로서 Lock이 어떤 형태로 걸려 있는지를 표시. 이를 통해 상호배제를 구현 및 동시성 실현 가능

④ MR : 인스턴스가 기동하면 자동으로 얻는 Lock

 

8.3.3 Deadlock의 구조

· DeadLock는 고장 나서 작동하지 않는 열쇠이며, 구체적으로는 서로가 상대방이 보유하고 있는 Lock을 기다리느라 영원히 작업 처리를 진행할 수 없는 상태

· Deadlock(ORA-00060 발생)일 때는 한쪽의 처리가 오라클에 의해 자동으로 롤백되며, ALERT 파일과 트레이스 파일에 정보가 표시됨

 

 

8.4 | Latch의 구조

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

· 메모리나 데이터를 조작할 때 상호 배타적(mutual exclusive)으로 처리하지 않아 데이터가 손상되는 것을 방지하기 위해 사용

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

· 하지만 현실에서는 Latch 경합을 많은 시스템에서 볼 수 있는데, 이는 CPU와 OS가 관련이 있음. OS에서는 여러 처리를 동시에 실행하는 멀티태스킹이 당연시되고 있으며, 선점(preemptive)이라고 불리는 처리 중인 CPU를 가로채는 동작도 존재

· Lock(Latch)를 가진 세션(프로세스)에서 CPU를 가로채면 (1) Latch를 가진 세션은 CPU를 사용할 수 없어 처리를 못하고, (2) CPU를 사용할 수 있는 세션은 Latch를 얻지 못해 처리를 진행하지 못하는 상황이 발생

· CPU의 스케줄링 외에도 CPU자원의 부족이나 페이징이 그 원인이 되며, 바람직하지 않은 대기와 Lock 경합이 격렬하게 발생하는 상황은 오라클에 국한되지 않고 OS 등 IT의 여러 분야에서 많이 볼 수 있음

 

 

8.5 | 요약

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

· idle 대기 이벤트와 Non-idle 대기 이벤트가 있음

· Lock은 데이터를 보호하기 위해 존재

· DeadLock은 상대방이 소유하고 있는 Lock을 요청해서 작업의 처리를 진행하지 못하는 상태

· Lock 경합을 해소하기 위해서는 애플리케이션 측에서 대처해야 할 때가 많음

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

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

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