친절한 SQL 튜닝_6장.DML 튜닝_4

DML 튜닝

6.4 Lock과 트랜잭션 동시성 제어

6.4.1 오라클 Lock

오라클은 공유 리소스와 사용자 데이터를 보호할 목적으로 DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용한다. 이 외에도 내부에 더 많은 종류의 Lock이 존재한다.

래치는 SGA에 공유된 각종 자료구조를 보호하기 위해 사용하며, 버퍼 Lock은 버퍼 블록에 대한 액세스를 직렬화하기 위해 사용한다. 라이브러리 캐시 Lock과 Pin은 라이브러리 캐시에 공유된 SQL 커서와 PL/SQL 프로그램을 보호하기 위해 사용한다.

애플리케이션 개발 측면에서 가장 중요하게 다루어야 할 Lock은 무엇보다 DML Lock이다. DML Lock은 다중 트랜잭션이 동시에 액세스하는 사용자 데이터의 무결성을 보호해 준다. DML Lock에는 테이블 Lock과 로우 Lock이 있다.

 

DML 로우 Lock

DML 로우 Lock은, 두 개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지한다. 하나의 로우를 변경하려면 로우 Lock을 먼저 실행해야 한다.

다른 트랜잭션이 읽고 있는 로우를 변경하려면 다음 레코드로 이동할 때까지 기다려야 하고, 다른 트랜잭션이 변경 중인 로우를 읽으려면 커밋할 때까지 기다려야 한다.

DML 로우 Lock에 의한 성능 저하를 방지하려면, 온라인 트랜잭션을 처리하는 주간에 Lock을 필요 이상으로 오래 유지하지 않도록 커밋 시점을 조절해야 한다. 그에 앞서 트랜잭션이 빨리 일을 마치도록, 즉 Lock이 오래 지속되지 않도록 관련 SQL을 모두 튜닝해야 한다.

DML 테이블 Lock

오라클은 DML 로우 Lock을 설정하기에 앞서 테이블 Lock을 먼저 설정한다. 현재 트랜잭션이 갱신 중인 테이블 구조를 다른 트랜잭션이 변경하지 못하게 막기 위해서다. 테이블 Lock을 “TM Lock”이라고 부르기도 한다.

‘테이블 Lock’이라고 하면, 테이블 전체에 Lock이 걸린다고 생각하기 쉽다. 그래서 다른 트랜잭션이 더는 레코드를 추가하거나 갱신하지 못하게 막는다고 생각하는 분들이 많다. 하지만 앞서 설명했듯이 DML을 수행하기 전에 항상 테이블 Lock을 먼저 설정하므로 그렇게 이해하는 것은 맞지 않는다. 하나의 로우를 변경하기 위해 테이블 전체에 Lock을 건다면 동시성이 좋은 애플리케이션을 구현하기 어렵다.

오라클에서 말하는 테이블 Lock은, 자신(테이블 Lock을 설정한 트랜잭션)이 해당 테이블에서 현재 어떤 작업을 수행 중인지를 알리는 일종의 푯말(Flag)이다.

Lock을 푸는 열쇠, 커밋

가끔 블로킹과 교착상태를 구분 못 하는 분들을 만난다. 블로킹(Blocking)은 선행 트랜잭션이 설정한 Lock 때문에 후행 트랜잭션이 작업을 진행하지 못하고 멈춰 있는 상태를 말한다. 이것을 해소하는 방법은 커밋(또는 롤백)뿐이다.

교착상태(Deadlock)는 두 트랜잭션이 각각 특정 리소스에 Lock을 설정한 성태에서 맞으편 트랜잭션이 Lock을 설정한 리소스에 또 Lock을 설정하려고 진행하는 상황을 말한다. 교착상태가 발생하면 둘 중 하나가 뒤로 물러나지 않으면 영영 풀릴 수 없다. 좁은 골목길에 두 대의 차량이 마주 선 것에 비유할 수 있다.

오라클에서 교착상태가 발생하면, 이를 먼저 인지한 트랜잭션이 문장 수준 롤백을 진행한 후에 아래 에러 메시지를 던진다. 교착상태를 발생시킨 문장 하나만 롤백하는 것이다.

ORA-00060: deadlock detected while waiting for resource

이제 교착상태는 해소됐지만 블로킹 상태에 놓이게 된다. 따라서 이 메시지를 받은 트랜잭션은 커밋 또는 롤백을 결정해야만 한다. 만약 프로그램 내에서 이 에러에 대한 예외처리(커밋 또는 롤백)를 하지 않는다면 대기 상태를 지속하게 되므로 주의가 필요하다.

 

6.4.2 트랜잭션 동시성 제어

동시성 제어는 비관적 동시성 제어와 낙관적 동시성 제어로 나뉜다.

비관적 동시성 제어(Pessimistic Concurrency Control)는 사용자들이 같은 데이터를 동시에 수정할 것으로 가정한다. 따라서 한 사용자가 데이터를 읽는 시점에 Lock을 걸고 조회 또는 갱신처리가 완료될 때까지 이를 유지한다. Lock은 첫 번째 사용자가 트랜잭션을 완료하기 전까지 다른 사용자들이 같은 데이터를 수정할 수 없게 만들기 때문에 비관적 동시성 제어를 잘못 사용하면 동시성이 나빠진다.

반면, 낙관적 동시성 제어(Optimistic Concurrency Control)는 사용자들이 같은 데이터를 동시에 수정하지 않을 것으로 가정한다. 따라서 데이터를 읽을 때 Lock을 설정하지 않는다. 그런데 낙관적 입장에 섰다고 해서 동시 트랜잭션에 의한 잘못된 데이터 갱신을 신경 쓰지 않아도 된다는 것은 절대 아니다. 읽는 시점에 Lock을 사용하지 않았지만, 데이터를 수정하고자 하는 시점에 앞서 읽은 데이터가 다른 사용자에 의해 변경되었는지 반드시 검사해야 한다.

 

6.4.3 채번 방식에 따른 INSERT 성능 비교

출처 : 친절한 SQL 튜닝 - 조시형 지음