데이터 동시성(data concurrency)은 다수의 사용자가 동일한 데이터에 동시에 접근할 수 있는 것을 말한다.

 

[락킹 메커니즘]

오라클 데이터베이스는 데이터 동시성을 보장하기 위해 락(lock)을 사용한다. 락은 자원의 사용을 직렬화하기 위한 방법 중 하나다.

(오라클 데이터베이스는 락 외에도 래치(latch)와 뮤텍스(mutex) 등을 사용하여 자원의 사용을 직렬화한다.)

 

오라클 데이터베이스는 다양한 락을 사용한다.

V$LOCK_TYPE 뷰에서 락의 종류를 조회할 수 있다.

DML 문은 TM 락과 TX 락을 사용한다.

TM 락 (table lock)은 테이블, TX 락(row lock)은 트랜잭션에 설정되는 락이다.

TX 락은 로우 레벨 락(low level lock)과 연결된다.

(로우 레벨 락은 데이터 블록의 로우에 설정된다. 하나의 TX 락이 다수의 로우 레벨 락과 연결되는 구조다.)

 

락은 종류에 따라 아래의 모드로 설정될 수 있다.

Y로 표시된 항목은 자원을 동시에 사용할 수 있는 조합이다.

DML 문은 TM 락을 RX 모드, TX 락을 X 모드로 설정한다.

 

RS - RX - S - SRX - X

RS (Row Share) Y - Y - Y - Y

S (Share) Y - Y

SRX (Share Row eXclusive) Y

X (eXclusive)

 

2개의 세션에서 아래의 쿼리를 순서대로 수행해보자.

S1 세션의 SID는 100, S2 세션의 SID는 200이다.

S1 세션은 CD가 1인 행, S2 세션은 CD가 2인 행을 갱신한다.

 

S1 (100)

SELECT USERENV ('SID') FROM DUAL; -- 100

 

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

 

 

S2 (200)

SELECT USERENV ('SID') FROM DUAL -- 200

 

UPDATE T1 SET VL = VL - 10 WHERE CD = 2;

 

DBA_LOCK 뷰 또는 V$LOCK 뷰에서 현재 설정된 락에 대한 정보를 조회할 수 있다.

또 다른 세션에서 DBA_LOCK 뷰를 조회해보자.

아래 쿼리는 S1 세션(SID = 100)의 락 정보를 조회한다.

TM 락은 RX 모드 ,TX 락은 X 모드로 설정되어 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 100

AND LOCK_TYPE IN ('DML', 'Transaction')

 

아래 쿼리는 S2 세션(SID = 200)의 락 정보를 조회한다.

TM 락은 RX 모드, TX 락은 X 모드로 설정되어 있다.

TM 락이 동일한 테이블(T1)에 설정되었지만 RX 모드는 서로 호환되기 때문에 블로킹되지 않고 락을 획득할 수 있다.

TX 락의 X 모드는 서로 호환되지 않지만 다른 행을 갱신했기 때문에 블로킹되지 않는다.

(갱신되는 행에 로우 레벨 락을 설정하고, 로우 레벨 락을 X 모드의 TX 락에 연결한다.)

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 200

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

-- 다음 예제를 위해 두 세션에서 롤백을 수행

 

S1 (100) - S2 (200)

ROLLBACK; - ROLLBACK;

 

롤백을 수행하면 트랜잭션이 종료되고, 트랜잭션이 종료되면 락도 해제된다.

DBA_LOCK 뷰를 다시 조회하면 락이 해제된 것을 확인할 수 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID IN (100, 200)

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

-- 아래의 예제를 순서대로 수행해보자. 동일한 행(CD = 1)을 갱신했기 때문에 S2 세션은 S1 세션의 락이 해제될 때까지 블로킹(BLOCKING) 된다.

 

S1 (100) - S2 (200)

UPDATE T1 SET VL = VL + 10 WHERE CD = 1; UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

-- 블로킹

 

 

-- 아래 쿼리는 S1 세션(SID = 100)의 락 정보를 조회한다.

BLOCKING_OTHERS 열에서 TX 락이 다른 세션을 블로킹하고 있는 것을 확인할 수 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 100

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

-- 아래 쿼리는 S2 세션 (SID = 200)의 락 정보를 조회한다. TM 락은 S1 세션의 RX 모드와 호환되기 때문에 블로킹되지 않고 락을 획득할 수 있다. TX 락은 동일한 행을 갱신했기 때문에 블로킹 된 상태로 X 모드를 요청하고 있다.

 

SELECT LOCK_TYPE, MODE_HELD, MODE_REQUESTED, BLOCKING_OTHERS

FROM DBA_LOCK

WHERE SESSION_ID = 200

AND LOCK_TYPE IN ('DML', 'TRANSACTION');

 

다음과 같은 과정으로 S2 세션이 블로킹된다.

(실제 수행 과정은 훨씬 더 복잡하다.)

 

순서 - 동작

1 - 로우 레벨 락을 설정하기 위해 블록을 방문하여 로우 헤더(C1 = 1)를 확인

2 - 로우 레벨 락이 설정되어 있는 것을 확인

3 - 로우 레벨 락과 연결된 S1 세션의 TX 락이 X 모드인 것을 확인

4 - 호환되지 않은 모드이므로 대기 목록에 트랜잭션을 등록하고 대기(블로킹)

 

또 다른 세션에서 아래 쿼리를 수행하면 에러가 발생한다. DROP TABLE 문은 DDL 문이다.

DDL 문은 TX 락을 X 모드로 설정한다. RX 모드와 X 모드는 호환되지 않기 때문에 DML 문이 수행 중인 오브젝트에 DDL 문을 수행하면 에러가 발생한다.

(DDL 문이 수행중인 오브젝트에 DML 문을 수행해도 에러가 발생한다. DDL 문이 장시간 수행되면 DML 문이 수행될 수 없기 때문에 장애가 발생할 수 있다.)

 

DROP TABLE T1;

 

ORA-00054: 리소스가 사용 중이어서 NOWAIT가 지정되었거나 시간 초과가 만료된 상태로 획득한다.

 

S1 세션에서 롤백을 수행하면 S2 세션의 블로킹이 해제된다.

다음 예제를 위해 S2 세션에서도 롤백을 수행하자.

(롤백을 수행할 수 없는 경우 블로킹하고 있는 세션을 강제 종료시켜야 한다.)

 

S1 (100) - S2 (200)

ROLLBACK; - ROLLBACK;

 

[동시성 제어]

MULTITIER 환경에서는 개발자가 직접 데이터 동시성을 제어해야 한다.

 

두 세션에서 아래 쿼리를 순서대로 실행해보자.

S2 세션의 갱신 결과가 유실된 것을 확인할 수 있다.

이런 현상을 LOST UPDATE라고 한다.

동시성 제어를 통해 LOST UPDATE를 방지할 수 있다.

 

S1

COLUMN VL NEW_VALUE V_VL

 

SELECT VL FROM T1 WHERE CD = 1;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S1

UPDATE T1

SET VL = &V_VL + 10

WHERE CD = 1;

 

SELECT VL FROM T1 WHERE CD = 1;

 

-- 다음 예제를 위해 아래와 같이 데이터를 원상 복구하자.

UPDATE T1 SET VL = 40 WHERE CD = 1;

COMMIT;

 

두 가지 방식의 동시성 제어를 사용할 수 있다.

비관적 동시성 제어와 낙관적 동시성 제어를 차례대로 살펴보자.

 

[비관적 동시성 제어]

비관적 동시성 제어(pessimistic concurrency control 또는 pessimistic locking)은 다수의 사용자가 동일한 데이터를 동시에 갱신하는 일이 빈번하다고 가정한다.

 

[FOR UPDATE 절]

비관적 동시성 제어는 FOR UPDATE 절을 사용한다.

SELECT 문에 FOR UPDATE 절을 기술하면 조회한 행에 로우 레벨 락이 설정된다.

 

FOR UPDATE [OF [{table | view}.] column [, [{table | view}.]column]...]

[{NOWAIT | WAIT integer | SKIP LOCKED}]

 

S1 세션에서 FOR UPDATE 절로 CD가 1인 행에 로우 레벨 락을 설정하고, S2 세션에서도 동일한 쿼리를 수행해보자. S2 세션은 S1 세션이 락이 해제될 때까지 블로킹된다. 갱신할 행에 로우 레벨 락을 설정하고, 갱신할 값을 계산하는 작업을 수행한 후, 행을 갱신하는 업무에서 활용할 수 있다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE; -- 작업

 

S2

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE; -- 블로킹

 

S1

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S2

UPDATE T1 SET VL = VL - 10 WHERE CD = 1;

COMMIT;

 

-- 아래 쿼리는 S2 세션에서 FOR UPDATE 절에 NOWAIT를 지정했기 때문에 즉시 에러가 발생한다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE;

 

S2

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE

NOWAIT;

 

S1

COMMIT;

 

S2

ORA-00054: 리소스가 사용 중이어서 NOWAIT이 지정되었거나 시간 초과가 만료된 상태로 획득합니다.

 

-- 아래 쿼리는 S2 세션에서 FOR UPDATE 절에 WAIT 10으로 지정했기 때문에 10초 후 에러가 발생한다.

블로킹이 발생하면 동시성이 저하될 수 있다. FOR UPDATE 절에 NOWAIT이나 WAIT integer를 지정하여 에러를 발생시킨 후 에러에 대한 예외 처리를 통해 동시성을 향상시킬 수 있다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE;

 

S2

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE

WAIT 10;

 

S1

COMMIT;

 

S2

ORA-30006: 리소스 사용 중. WAIT 시간 초과로 획득이 만료됨

 

FOR UPDATE 절에 SKIP LOCKED를 지정하면 락이 설정된 행을 조회하지 않는다.

아래 예제의 S2 세션에 락이 설정되지 않은 C1이 2인 행만 조회된다.

SKIP LOCKED는 동시성 제어보다 다중 큐(multiconsumer queue)를 구현할 때 사용되는 기능이다.

 

S1

SELECT * FROM T1 WHERE CD = 1 FOR UPDATE;

 

S2

SELECT * FROM T1 FOR UPDATE SKIP LOCKED;

 

S1, S2

COMMIT;

 

-- 아래 쿼리는 조인이 포함된 SELECT 문에 FOR UPDATE 절을 사용했다.

FOR UPDATE 절에 열을 지정하면 열을 지정한 테이블의 행에만 락이 설정된다.

 

S1

SELECT *

FROM EMP A, DEPT B

WHERE B.DEPTNO = A.DEPTNO

FOR UPDATE OF A.DEPTNO;

 

S2

SELECT * FROM DEPT FOR UPDATE NOWAIT;

 

SELECT * FROM EMP FOR UPDATE NOWAIT;

 

ORA-00054: 리소스가 사용 중이어서 NOWAIT이 지정되었거나 시간 초과가 만료된 상태로 획득합니다.

 

S1, S2

COMMIT;

 

[선분 이력 변경]

선분 이력 변경은 기존 이력을 단절(update)하고, 신규 이력을 생성(insert)하는 두 가지 작업을 하나의 트랜잭션으로 수행해야 한다. FOR UPDATE 절로 갱신은 방지할 수 있지만, 삽입은 방지할 수 없다.

이런 경우 FOR UPDATE 절로 부모 테이블에 락을 설정하는 기법을 사용할 수 있다.

 

-- 예제를 위해 아래와 같이 테이블을 생성

-- TP 테이블이 부모 테이블, TC 테이블이 선분 이력을 관리하는 자식 테이블.

-- TC 테이블의 PK는 코드(CD), 시작일자(BD)다.

 

DROP TABLE TP PURGE;

DROP TABLE TC PURGE;

 

CREATE TABLE TP (CD VARCHAR2(1), CONSTRAINT TP_PK PRIMARY KEY (CD));

CREATE TABLE TC (CD VARCHAR2(1) -- 코드

, BD DATE -- 시작일자

, ED DATE -- 종료일자

, VL NUMBER -- 값

, CONSTRAINT TC_PK PRIMARY KEY (CD, BD)

, CONSTRAINT TC_F1 FOREIGN KEY (CD) REFERENCES TP (CD));

 

INSERT INTO TP VALUES ('A');

INSERT INTO TC VALUES ('A', DATE '2050-01-01', DATE '9999-12-31', 1);

 

-- 아래 방식으로 선분 이력을 갱신할 수 있다.

-- 자식 테이블(TC)을 갱신하기 전에 부모 테이블(TP)에 락을 설정했기 때문에 데이터 동시성을 확보할 수 있다.

다른 세션에서 동시에 CD가 A인 선분 이력을 변경하면 부모 테이블(TP)에 락을 설정하는 단계에서 블로킹된다.

 

SELECT * FROM TP WHERE CD = 'A' FOR UPDATE;

 

UPDATE TC -- 기존 이력 단절

SET ED = DATE '2050-02-01' - 1

WHERE CD = 'A'

AND ED = DATE '9999-12-31';

 

INSERT

INTO TC -- 신규 이력 생성

VALUES ('A', DATE '2050-02-01', DATE '9999-12-31', 2);

 

COMMIT;

 

아래는 TC 테이블을 조회한 결과다.

 

SELECT * FROM TC ORDER BY 2;

 

TC 테이블의 PK를 코드(CD), 최종일자(ED)로 변경해보자.

성능상의 이유로 PK에 최종일자 속성을 사용하기도 한다.

 

ALTER TABLE TC DROP CONSTRAINT TC_PK;

ALTER TABLE TC ADD CONSTRAINT TC_PK PRIMARY KEY (CD, ED);

 

아래 방식으로 선분 이력을 갱신할 수 있다.

기본 식별자가 변경되지 않아야 한다는 원칙을 준수하기 위해 다소 복잡한 방식으로 처리해야 한다.

 

SELECT * FROM TP WHERE CD = 'A' FOR UPDATE;

 

INSERT

INTO TC -- 기존 이력 단절

SELECT CD, BD, DATE '2050-03-01' - 1 AS ED, VL

FROM TC

WHERE CD = 'A'

AND ED = DATE '9999-12-31';

 

UPDATE TC -- 신규 이력 생성

SET BD = DATE '2050-03-01', VL = 3

WHERE CD = 'A'

AND ED = DATE '9999-12-31';

 

아래는 TC 테이블을 조회한 결과

 

SELECT * FROM TC ORDER BY 2;

 

[낙관적 동시성 제어]

낙관적 동시성 제어(Optimistic Concurrency Control 또는 optimistic locking)은 다수의 사용자가 동일한 데이터를 동시에 갱신하는 일이 드물다고 가정한다.

 

[컬럼 확인 방식]

칼럼 확인 방식은 변경할 값을 조회하여 저장하고, 값을 변경하기 전에 저장한 값의 변경 여부를 확인하는 방식이다. 변경할 값이 많은 경우 모든 값을 비교해야 하기 때문에 쿼리가 길어질 수 있다.

 

아래 쿼리는 컬럼 확인 방식으로 낙관적 동시성 제어를 구현했다.

S1 세션에서 값을 조회한 후 S2 세션에서 값을 변경했기 때문에 S1 세션이 UPDATE 문에서 행이 갱신되지 않았다.

 

S1

COLUMN VL NEW_VALUE V_VL

 

SELECT VL FROM T1 WHERE CD = 1;

 

S2

UPDATE T1 SET VL = VL + 10 WHERE CD = 1;

COMMIT;

 

S1

UPDATE T1

SET VL = VL + 10

WHERE CD = 1

AND VL = &V_VL;

 

[버전 확인 방식]

버전 확인 방식은 버전 정보를 변수에 저장하고, 값을 변경하기 전에 저장한 버전 정보의 변경 여부를 확인하는 방식이다. 버전 정보만 확인하면 되기 때문에 쿼리를 간결하게 작성할 수 있는 장점이 있지만, 추가적인 버전 속성을 생성해야 하는 단점도 있다.

 

-- 예제를 위해 아래와 같이 열을 추가하자. 버전 정보는 주로 시스템 속성으로 관리된다.

ALTER TABLE T1 ADD MD DATE DEFAULT DATE '2050-01-01' NOT NULL;

 

-- 아래 쿼리는 버전 확인 방식으로 낙관적 동시성 제어를 구현한다. 갱신할 열이 많더라도 버전 정보가 저장된 MD 열만 비교하면 된다.

 

S1

COLUMN MD NEW_VALUE MD

 

SELECT MD FROM T1 WHERE CD = 1;

 

S2

UPDATE T1

SET VL = VL - 10, MD = SYSDATE

WHERE CD = 1;

 

COMMIT;

 

S1

UPDATE T1

SET VL = VL - 10

WHERE CD = 1

AND MD = TO_DATE ('&md', 'YYYY-MM-DD HH24:MI:SS');

 

불친절한 SQL 프로그래밍저자정희락출판디비안(DBian)발매2018.09.10.

 

 

+ Recent posts