오라클 락(Lock) 기본 개념

데이터베이스 운영 시 많은 대기가 발생해서 성능에 문제가 있거나 처리가 지연되기도 합니다. 일반적으로 많이 발생하는 Lock wait나 Dead lock을 접하게 됩니다.

그러면, 데이터베이스에 왜 락이 필요한 것일까요?

고객이 창고에 맡긴 물건의 내용을 변경하고 싶다고 가정하면, 물건의 내용을 변경하고 싶다고 했지만 단순히 숫자 한 개를 큰 값으로 바꿔 쓰고 싶을 뿐입니다.

즉, 우선 물건을 알고 싶으니까 물건을 꺼내 주세요(select).

그 후 꺼낸 값에 1을 더해서 다시 맡겨주세요(update).

오라클은 병렬 처리를 가능케 하고 높은 처리량을 실현을 목표로 합니다. 병렬 처리를 하면 실행하는 타이밍에 따라서는 값이 늘어나지 않는 현상이 발생할 수 있습니다.

값을 변경하는 작업 때에는 변경하기 전의 값을 바라봐도 상관없는 검색을 제외하고는 누구도 그 값에 접근할 수 없도록 해야 합니다. 락은 이렇게 다중 처리를 실현하기 위해 처리를 보호하는 것입니다. 다음과 같은 select로 SQL에 락을 걸 수 있습니다. 해당 select는 '지금부터 이것은 내가 사용할 테니까 누구도 건드려서는 안 돼'라는 락을 건다고 생각하시면 됩니다.

 

SELECT test 1 FROM test_tab WHERE id = 1

FRO UPDATE; - - -> 락이 걸림

UPDATE test_tab SET test1 = 값

WHERE id = 1;

 

select문에 의해 id가 1인 행에 락이 걸립니다. Row 락이 걸렸으므로 같은 행을 대상으로 한 update나 delete문, 그리고 다른 select for update문은 대기하게 됩니다. Row 락이 해제되는 시점은 커밋 또는 롤백할 때입니다.

값을 1 증가시킨다 라는 것을 하나의 update문으로 구현합니다.

 

UPDATE test_tab SET test1 = test1 + 1 WHERE id = 1;

 

update 등은 자동으로 락을 걸기 때문에 문제가 없습니다. 병렬로 처리하더라고 값이 틀리지 않습니다. 하지만 이 update문을 대량으로 동시에 수행하려고 하면 id가 1인 행에 락이 걸려서 성능이 나오지 않을까 하는 의문이 생길 수도 있습니다. 이 경우에는 락 대기(락에 의한 대기)가 발생하는 게 맞습니다. 하지만 이 트랜잭션을 보호해야 하므로 당연하게 발생합니다. 다중으로 처리하기 위해서는 애플리케이션에서 처리를 해야 합니다.

 

그럼 대기와 락 대기란? 보편적으로 대기는 성능에 대해 부정적인 이지미가 있지만, 실제로는 '기다림'을 표시하는 것뿐입니다. 또한, 대기 이벤트를 간단히 말하면 '기다리고 있는 일'이라 할 수 있습니다. 대기로 인해 SQL 처리가 늦어질 수 있습니다. 이 대기에는 아이들 대기(idle wait: '처리할 것이 없는 대기'), 아이들이 아닌 대기('이유가 있는 대기', '이상 상태 등 쓸데없이 SQL을 기다리게 하는 대기')가 있습니다.

 

1. SQL문을 데이터베이스에 요청하고 결과 대기
2. 서버 프로세스는 SQL문이 도착할 때까지 아이들 대기 이벤트 'SQL*Net message from client'를 대기
3. 도착하면 아이들 대기 이벤트 상태가 끝나며 처리를 시작
4. 대부분의 백그라운드 프로세스는 서버 프로세스로부터 요청받기 전에는 아이들 상태, 아이들일 때는 'rdbms ipc message'를 기다리는 프로세스가 많음
5. 스토리지로부터 데이터 파일을 읽어옴
6. SQL 처리 도중이라도 필요에 따라서는 I/O 등의 대기 이벤트가 발생 (데이터 파일에서 데이터를 읽어 오고 있는 ‘db file sequential read’, ‘db file scattered read’, 락을 기다리는 ‘enqueue’, 내부 락을 기다리는 latch free, 등)
7. SQL 처리가 끝나면 ‘SQL*Net message from client를 기다림

아이들이 아니 대기에 주의해야 합니다. 문제는 아이들이 아니 대기 이벤트입니다. 이상 상태 등 쓸데없이 SQL를 기다리게 하는 대기는 판단이 어렵습니다. 예를 들면 어떤 사용자가 테이블에 락을 걸어 버린 후 잊어버리고 있는 상태 등 의 경우를 말합니다. 이렇게 되면 다른 사용자가 해당 테이블의 데이터를 변경할 수 없습니다. 락을 통해 데이터는 보호되지만 쓸데없는 대기 시간이 발생합니다. SQL 튜닝 관점에서는 '아이들이 아닌 대기 이벤트 + SQL 처리에 사용되는 CPU 시간'이 SQL 걸리는 시간이므로 이 부분을 상항 신경 써야 합니다. 대기 이벤트는 스태츠 팩 또는 AWR이나 v$session_wait에서 볼 수 있습니다. 

 

락에 의한 대기란? 락을 걸었다는 것 자체만으로는 대기가 발생하지 않습니다. 락이 걸려 있는 대상에 다시 락을 걸려고 할 때 대기가 발생합니다. 락 정보는 v$lock 뷰 등에서 확인할 수 있습니다.

자주 볼 수 있는 락은 TX와 TM입니다. TX는 행과 관련된 락이며, TM은 테이블에 거는 락입니다. '모드'는 동시성 제어를 위한 것으로서 락이 어떤 형태로 걸려있는지 표시해 줍니다. 예를 들면, TX 락의 X(배타적)는 같은 행에 대하여 다른 모드의 락을 허용하지 않습니다. 반면에, DML을 할 때 자주 발생하는 RX 또는 RS 모드의 TM 락(테이블 락)은 다른 세션이 같은 테이블에 대해 RX 또는 RS 모드로 TM 락을 걸 수 있습니다. RX 모드의 TM 락이 걸렸다면 테이블의 정의를 변경하는 작업은 할 수 없지만, 테이블에 대해 여러 트랜잭션을 수행할 수 있습니다. 이런 식으로 모드를 잘 활용하면 필요한 상호 배제를 구현하는 동시성을 실현할 수 있습니다.