* Non-Repeatable Read 테스트
-- Oracle 기본환경 (SQLPLUS 환경)
------------------------------------------------------------------------------------------------------------------------
DROP TABLE TEST;
CREATE TABLE TEST
( NO NUMBER(10) NOT NULL
, AMT NUMBER(10) NOT NULL
, GRD VARCHAR2(10) NOT NULL);
INSERT INTO TEST VALUES (1, 40000, 'B');
COMMIT;
-- TEST 1 -> Non-Repeatable Read 발생
TX1 TX2
------------------------------------------------------------------------------------------------------------------------
-- 1.
VARIABLE V_AMT NUMBER;
-- 2.
BEGIN
SELECT AMT INTO :V_AMT
FROM TEST
WHERE NO = 1;
END;
/
-- 3.
PRINT V_AMT;
V_AMT
----------
40000
-- 4. Blocking 당연히 없음
UPDATE TEST SET AMT = 60000
, GRD = 'A'
WHERE NO = 1;
COMMIT;
-- 5.
BEGIN
IF :V_AMT >= 50000 THEN
UPDATE TEST SET GRD = 'A'
WHERE NO = 1;
ELSE
UPDATE TEST SET GRD = 'B'
WHERE NO = 1;
END IF;
COMMIT;
END;
/
-- 6. Lost Update 발생
SELECT * FROM TEST;
NO AMT GRD
---------- ---------- --------------------
1 60000 B
-- TEST 2 -> FOR UPDATE로 해결
TX1 TX2
------------------------------------------------------------------------------------------------------------------------
-- 0.
UPDATE TEST SET AMT = 40000
, GRD = 'B'
WHERE NO = 1;
COMMIT;
-- 1.
VARIABLE V_AMT NUMBER;
-- 2. FOR UPDATE 추가
BEGIN
SELECT AMT INTO :V_AMT
FROM TEST
WHERE NO = 1 FOR UPDATE;
END;
/
-- 3.
PRINT V_AMT;
V_AMT
----------
40000
-- 4. Blocking 발생
UPDATE TEST SET AMT = 60000
, GRD = 'A'
WHERE NO = 1;
-- 5.
BEGIN
IF :V_AMT >= 50000 THEN
UPDATE TEST SET GRD = 'A'
WHERE NO = 1;
ELSE
UPDATE TEST SET GRD = 'B'
WHERE NO = 1;
END IF;
COMMIT;
END;
/
-- 6.
COMMIT;
-- 7.
SELECT * FROM TEST;
NO AMT GRD
---------- ---------- --------------------
1 60000 A
-- TEST 3 -> SELECT한 값을 이용하지 않고, UPDATE구문에서 현재값으로 계산
TX1 TX2
------------------------------------------------------------------------------------------------------------------------
-- 0.
UPDATE TEST SET AMT = 40000
, GRD = 'B'
WHERE NO = 1;
COMMIT;
-- 1.
VARIABLE V_AMT NUMBER;
-- 2.
BEGIN
SELECT AMT INTO :V_AMT
FROM TEST
WHERE NO = 1;
END;
/
-- 3.
PRINT V_AMT;
V_AMT
----------
40000
-- 4. Blocking 당연히 없음
UPDATE TEST SET AMT = 60000
, GRD = 'A'
WHERE NO = 1;
COMMIT;
-- 5. UPDATE 구문에서 처리 (Current Mode)
UPDATE TEST SET GRD = DECODE(SIGN(AMT - 50000), -1, 'B', 'A')
WHERE NO = 1;
COMMIT;
-- 6.
SELECT * FROM TEST;
NO AMT GRD
---------- ---------- --------------------
1 60000 A
-- SQL SERVER 기본환경 (DB Server local에서 디버그모드)
------------------------------------------------------------------------------------------------------------------------
DROP TABLE TEST;
CREATE TABLE TEST
( NO NUMERIC NOT NULL
, AMT NUMERIC NOT NULL
, GRD VARCHAR(10) NOT NULL);
INSERT INTO TEST VALUES (1, 40000, 'B');
-- TEST 1 -> Non-Repeatable Read 발생
TX1 TX2
------------------------------------------------------------------------------------------------------------------------
-- 1.
DECLARE @AMT NUMERIC;
-- 2.
SELECT @AMT = AMT
FROM TEST
WHERE NO = 1;
-- 3.
SELECT @AMT;
40000
-- 4. Blocking 당연히 없음
UPDATE TEST SET AMT = 60000
, GRD = 'A'
WHERE NO = 1;
-- 5.
IF @AMT >= 50000
UPDATE TEST SET GRD = 'A'
WHERE NO = 1;
ELSE
UPDATE TEST SET GRD = 'B'
WHERE NO = 1;
-- 6. Lost Update 발생
SELECT * FROM TEST;
NO AMT GRD
---------- ---------- --------------------
1 60000 B
-- TEST 2 -> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 이용
TX1 TX2
------------------------------------------------------------------------------------------------------------------------
-- 0.
UPDATE TEST SET AMT = 40000
, GRD = 'B'
WHERE NO = 1;
-- 1. Isolation level 을 Repeatable read로 설정
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DBCC USEROPTIONS;
-- 2.
BEGIN TRAN
DECLARE @AMT NUMERIC;
/*
ORACLE의 For Update 구문과 같다.
이렇게 해도 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;설정한것과 결과 같다.
SELECT *
FROM TEST WITH (REPEATABLEREAD)
WHERE NO = 1;
*/
-- 3.
SELECT @AMT = AMT
FROM TEST
WHERE NO = 1;
-- 4.
SELECT @AMT;
40000
-- 5. Blocking 발생
UPDATE TEST SET AMT = 60000
, GRD = 'A'
WHERE NO = 1;
-- 6.
IF @AMT >= 50000
UPDATE TEST SET GRD = 'A'
WHERE NO = 1;
ELSE
UPDATE TEST SET GRD = 'B'
WHERE NO = 1;
COMMIT TRAN
-- 1. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 설정시에는
-- TX1혹은 TX2중하나가 강제종료 되어 버린다. (교착상태발생. 그래서 아래결과가 안나올때도 있음)
-- 2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 설정했을 경우
-- 교착상태 에러도 발생하지 않고 TX1이 종료되면 TX2가 정상처리되어 결과는 60000 A 에 된다.
(1개 행이 영향을 받음)
-- 7. 결과값은 아래 두개 중에 하나가 된다. 즉, 교착상태가 발생하여 어떤 Transaction이 종료되는냐에 따라 결과가 달라진다. 어찌되었든 일관성은 유지 되었다.
SELECT * FROM TEST;
NO AMT GRD
---------- ---------- --------------------
1 40000 B
NO AMT GRD
---------- ---------- --------------------
1 60000 A
-- TEST 3 -> SELECT한 값을 이용하지 않고, UPDATE구문에서 현재값으로 계산
TX1 TX2
------------------------------------------------------------------------------------------------------------------------
-- 0.
UPDATE TEST SET AMT = 40000
, GRD = 'B'
WHERE NO = 1;
-- 1.
DECLARE @AMT NUMERIC;
-- 2.
SELECT @AMT = AMT
FROM TEST
WHERE NO = 1;
-- 3.
SELECT @AMT;
40000
-- 4. Blocking 당연히 없음
UPDATE TEST SET AMT = 60000
, GRD = 'A'
WHERE NO = 1;
-- 5. UPDATE 구문에서 처리 (Current Mode)
UPDATE TEST SET GRD = CASE WHEN AMT >= 50000 THEN 'A' ELSE 'B' END
WHERE NO = 1;
-- 6.
SELECT * FROM TEST;
NO AMT GRD
---------- ---------- --------------------
1 60000 A
Oracle은 Read Committed와 Serializable Read만 지원한다.
Oracle에서 Repeatable Read를 구현하려면 for update 구문을 이용하면 된다.