트랜잭션 처리는 DBMS의 핵심적인 기능입니다. 같은 자원을 여러 트랜잭션이 엑세스하는 다중 트랜잭션환경에서 데이터의 일관성과 무결성을 유지하려면 트랜잭션의 순차적 진행이 보장되야합니다. 이를 트랜잭션 직렬화라고 하는데 모든 DBMS는 직렬화를 위해 LOCK 메커니즘을 활용합니다. DBMS의 종류에 따라 LOCK의 구현방식이 조금씩 다르기 때문에 사용중인 DBMS의 LOCK 메커니즘을 정확히 이해해야 합니다.
공유 LOCK과 배타적 LOCK
DBMS는 트랜잭션의 오퍼레이션별로 적당한 수준의 LOCK을 자동으로 생성합니다. 일부 LOCK에 대해서는 사용자가 직접 제어하는 방법도 제공합니다. 가장 기본이 되는 LOCK 모드는 공유LOCK과 배타적LOCK이 입니다.
공유 LOCK
공유 LOCK은 일반적으로 데이터를 읽을 때 사용하고 배타적 LOCK은 일반적으로 데이터를 변경할 때 사용합니다. 공유 LOCK 끼리는 상호호환하지만 배타적 LOCK 과는 호환하지 않습니다. '호환한다' 라는 말은 한 리소스에 두 개 이상의 LOCK을 동시에 설정할 수 있음을 뜻합니다.
즉, 공유 LOCK을 설정한 리소스에 다른 트랜잭션이 추가로 공유 LOCK을 설정할 수는 있지만 배타적 LOCK을 설정할 수는 없습니다. 따라서 자신이 읽고 있는 리소스를 다른 사용자가 동시에 읽을 수는 있어도 변경은 불가합니다. 반대로 변경중인 리소스를 다른 트랜잭션이 읽을 수 없습니다.
배타적 LOCK
데이터를 변경할 때 사용합니다. 트랜잭션이 완료될 때까지 유지되며 다른 트랜잭션은 해당 리소스에 접근할 수 없습니다. 배타적 LOCK일 설정된 리소스에는 공유 LOCK과 배타적 LOCK 모두 추가로 설정할 수 없습니다.
블로킹과 교착상태
블로킹
블로킹은 LOCK 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰있는 상태를 말합니다. 공유 LOCK 끼리는 블로킹이 발생하지않지만 공유 LOCK과 베타적 LOCK, 베타적 LOCK 끼리는 블로킹이 발생할 수 있습니다. 블로킹 상태를 해소하는 방법은 커밋 또는 롤백입니다. LOCK 경합이 발생하면 먼저 LOCK을 설정한 트랜잭션이 완료될 때까지 후행 트랜잭션은 기다려야합니다. 커밋 또는 롤백을 통해 선행 트랜잭션을 완료할 수 있습니다.
LOCK에 의한 성능 저하를 최소화하는 방법은 다음과 같습니다
- 트랜잭션의 원자성을 훼손하지 않는 선에서 가능한 짧게 트랜잭션을 정의합니다.
- 배타적 LOCK 끼리 발생하는 경합은 피할 수 없으므로 불필요하게 트랜잭션을 길게 정의해선 안됩니다.
- 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계해야합니다.
- 블로킹 현상에 의해 사용자가 무한정 대기하지 않도록 TIMEOUT 시간을 지정해야 합니다.
ORACLE 이라면 update/delete 문장을 수행하기 전에 SELECT FOR UPDATE 옵션을 지정해 SELECT로 조회된 레코드에 대해 배타적 LOCK을 설정합니다. WAIT + 시간을 통해 LOCK 획득을 위해 대기할 최대 시간을 지정할 수 있습니다.
SELECT * FROM T WHERE NO = 1 FOR UPDATE WITH NO OPTION -- LOCK을 획득하기 전까지 무제한 대기합니다.
SELECT * FROM T WHERE NO = 1 FOR UPDATE NOWAIT --- LOCK 을 획득하지 못하면 대기없이 EXCEPTION을 던집니다.
SELECT * FROM T WHERE NO = 1 FOR UPDATE WAIT 3 --- 3초 대기 후 LOCK을 확득하지 못하면 EXCEPTION을 던집니다.
가장 효과적인 방법은 결과가 빨리나오는 SQL을 작성하는 것입니다. SQL SERVER에서 SHARE LOCK과 EXCLUSIVE LOCK 이 호환되지 않아 블로킹이 발생합니다.
교착상태
교착상태는 두 세션이 각각 LOCK을 설정한 리소스를 서로 엑세스하기위해 마주보며 진행되는 상황을 의미합니다. 교착상태가 발생하면 DBMS가 하나의 세션에 에러를 발생시켜 문제를 해결합니다. 여러 테이블을 엑세스하면서 발생하는 교착상태는 테이블 접근 순서를 동일하게 진행하도록 하면 해결할 수 있습니다. 모든 개발자가 동일한 순서로 테이블에 접근하면 교착상태는 발생하지 않습니다.
ORACLE LOCK
ORACLE은 공유 리소스와 사용자 테이블을 보호할 목적으로 DML LOCK, DDL LOCK, LATCH, BUFFER LOCK, LIBRARY CACHE LOCK/PIN 등 다양한 종류의 LOCK을 사용합니다. 이 중 개발 측면에서 가장 중요한 LOCK은 DML LOCK 입니다. DML LOCK은 다중 사용자에 의해 동시에 액세스되는 사용자 데이터의 무결성을 보호합니다. DML LOCK에는 ROW LOCK(TX LOCK) 과 TABLE LOCK(TM LOCK)이 있습니다.
ROW LOCK
오라클에서 로우 LOCK은 베타적 LOCK 입니다. INSERT, UPDTAE, DELETE, SELECT FOR UPDATE 를 수행한 트랜잭션에 의해 설정됩니다.
이 트랜잭션이 커밋 또는 롤백할 때까지 다른 트랜잭션은 해당 로우를 변경할 수 없습니다.
오라클에서 일반 SELECT 문에 의해 읽힌 레코드에느 LOCK이 설정되지 않습니다. 읽기 작업에 대한 공유 LOCK을 사용하지 않기 때문에 읽기와 갱신 작업은 서로 블로킹하지 않습니다.
- 읽으려는 데이터를 다른 트랜잭션이 갱신중이더라도 기다리지 않습니다.
- 갱신하려는 데이터를 다른 트랜잭션이 읽는 중이라도 기다리지 않습니다. (SELECT FOR UPDATE 구문으로 읽는 경우는 베타적 LOCK이 발생하므로 제외)
- 갱신하려는 데이터를 다른 트랜잭션이 갱신중이면 LOCK 발생
ORACLE이 읽기 작업에 대한 공유 LOCK을 사용하지 않고도 데이터의 일관성을 유지할 수 있는 이유는 UNDO 데이터를 이용한 다중버전 동시성 제어 메커니즘을 사용하기 때문입니다. 별도의 LOCK 메니저 없이 레코드의 속성으로서 로우 LOCK을 구현했기때문에 아무리 많은 레코드를 갱신하더라도 LOCK ESCALATION은 발생하지 않습니다.
TABLE LOCK
한 트랜잭션이 로우 LOCK을 얻을 때, 해당 테이블에 대한 테이블 LOCK을 동시에 얻습니다. 현재 트랜잭션이 갱신중인 테이블의 구조를 변경하지 못하게 막기 위함입니다. 테이블 LOCK의 종류는 5가지가 있습니다.
ROW SHARE(RS)
ROW를 LOCK 하고 UPDATE 하기위한 LOCK 입니다. 모든 로우에 대한 다른 트랜잭션의 SELECT를 허용합니다. 테이블에서 LOCK 되지 않은 ROW에 대한 다른 트랜잭션의 INSERT, UPDATE, DELETE를 허용합니다.
테이블에 대한 다른 트랜잭션의 RS, RX, S, SRX LOCK을 허용하고 X LOCK을 허용하지 않습니다. SELECT FOR UPDATE 명령의 대상이 되는 ROW에 대해서는 TX LOCK이 걸려야하는데 이때 TM ROCK으로 RS LOCK이 먼저 걸려야합니다. 대상 ROW에 대해 변경을 시도하면 TX LOCK이 걸려있으므로 commit/rollback 될때까지 기다려야합니다.
ROW EXCLUSIVE(RX)
INSERT, UPDATE DELETE, MERGE 를 위해 ROW LOCK을 설정하려면 먼저 획득해야하는 TM LOCK 입니다.
ROW를 LOCK 하고 UPDATE 하기위한 LOCK 입니다. 모든 로우에 대한 다른 트랜잭션의 SELECT를 허용합니다.
테이블에서 LOCK 되지 않은 ROW에 대한 다른 트랜잭션의 INSERT, UPDATE, DELETE를 허용합니다. 테이블에 대한 다른 트랜잭션의 RS, RX LOCK을 허용하고 S, SRX, X LOCK을 허용하지 않습니다.
SHARE(S)
다른 트랜잭션이 테이블의 특정 ROW에 LOCK 거는 것을 허용합니다. 다른 트랜잭션이 해당 테이블에 대한 다른 트랜잭션의 RS, S LOCK을 허용하고 RX SRX, X LOCK을 허용하지 않습니다.
SHARE ROW EXCLUSIVE(SRX)
테이블에 ROW를 INSERT, UPDATE, DELETE 하기 위해 걸린 테이블 LOCK 입니다.
동일 테이블에서 QUERY OR SELECT...FOR UPDATE 로 특정 ROW를 LOCK 하려는 다른 트랜잭션을 허용합니다. 즉 다른 트랜잭션의 RS LOCK을 허용합니다.
다른 트랜잭션의 TABLE 갱신을 불어합니다. 즉, RX, SRX, S, X, LOCK을 불허합니다. 주어진 table에 대해 하나의 share row excusive table lock 만 걸 수 있습니다.
EXCLUSIVE(X)
다른 트랜잭션의 LOCK을 허용하지 않습니다. 대표적으로 SELECT ... FOR UPDATE 문을 수행할 때 RS 모드 테이블 LOCK을 얻고, insert-update-delete 문을 수행할때 RX 모드 테이블 LOCK을 얻는다. DML 로우 LOCK을 처음 얻는 순간 묵시적으로 테이블 LOCK을 얻지만, 아래처럼 LOCK TABLE 명령어를 이용해 명시적으로 테이블 LOCK을 얻을 수도 있습니다.
lock table emp in row share mode;
lock table emp in row exclusive mode;
lock table emp in share mode;
lock table emp in share row exclusive mode;
lock table emp in excusive mode;
테이블 LOCK 긴의 호환성은 다음과 같습니다.
--- NULL RS RX S SRX X
--- NULL O O O O O O
--- RS O O O O O
--- RX O O O
--- S O O O
--- SRX O O
--- X O
SELECT ... FOR UPDATE 문을 수행할 때, ROW SHARE LOCK을 얻고 INSERT-UPDATE-DELETE 문을 수행할때는 RX 모드 TABLE LOCK을 얻습니다. ROW LOCK을 얻는 순간 묵시적으로 TABLE LOCK도 얻지만 LOCK TABLE 명령어를 통해 명시적으로 TABLE LOCK을 얻을 수 있습니다.
ORACLE에서 테이블 LOCK은, LOCK을 획득한 선행 트랜잭션이 해당 테이블에 현재 어떤작업을 수행중인지 알리는 푯말 역할을 합니다. 후행 트랜잭션은 어떤 테이블 LOCK이 설정돼 있는지 보고 그 테이블로의 진입여부를 결정할 수 있습니다.