* LAST_YN Flag 갱신 테스트
* ORACLE
CREATE TABLE TEST
(NO NUMBER(10)
,AMT NUMBER(10)
,LAST_YN VARCHAR2(1 CHAR) DEFAULT 'N');
ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY(NO);
-- 0. 초기화
DELETE FROM TEST;
INSERT INTO TEST
SELECT LEVEL, 100, 'N'
FROM DUAL
CONNECT BY LEVEL <= 10;
COMMIT;
SELECT * FROM TEST;
-- TEST 1
TX1 TX2
----------------------------------------------------------------------------------------------------------------
-- 1.
INSERT INTO TEST VALUES (11, 100, 'N');
-- 2. Blocking 안되고 처리되어 버린다.
UPDATE TEST T2 SET T2.LAST_YN = DECODE((SELECT MAX(T1.NO) FROM TEST T1), T2.NO, 'Y', 'N');
-- 2. 이렇게 해도 마찬가지다. Blocking 안되고 처리되어 버린다.
UPDATE TEST T2 SET T2.LAST_YN = (SELECT DECODE(MAX(T1.NO), T2.NO, 'Y', 'N') FROM TEST T1);
-- 3.
COMMIT;
-- 4.
COMMIT;
-- 5. 10 -> Y
SELECT * FROM TEST;
-- TEST 2 (TEST 1상황을 반대로 해도 결과 같다.)
TX1 TX2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 1. 두개 쿼리 어떻게 해도 같다.
UPDATE TEST T2 SET T2.LAST_YN = DECODE((SELECT MAX(T1.NO) FROM TEST T1), T2.NO, 'Y', 'N');
UPDATE TEST T2 SET T2.LAST_YN = (SELECT DECODE(MAX(T1.NO), T2.NO, 'Y', 'N') FROM TEST T1);
-- 2. Blocking 없다.
INSERT INTO TEST VALUES (11, 100, 'N');
COMMIT;
-- 3.
COMMIT;
-- 4. 10 -> Y
SELECT * FROM TEST;
* ORACLE은 UPDATE 시점기준으로 UPDATE 처리하므로 어떤게 하든 결과가 같다.
* SQL SERVER
-- 0. 초기화
CREATE TABLE TEST
(NO INTEGER NOT NULL
,AMT INTEGER
,LAST_YN VARCHAR(1) DEFAULT 'N');
ALTER TABLE TEST ADD CONSTRAINT TEST_PK PRIMARY KEY(NO);
DELETE FROM TEST;
INSERT INTO TEST VALUES (1, 100, 'N');
INSERT INTO TEST VALUES (2, 100, 'N');
INSERT INTO TEST VALUES (3, 100, 'N');
INSERT INTO TEST VALUES (4, 100, 'N');
INSERT INTO TEST VALUES (5, 100, 'N');
INSERT INTO TEST VALUES (6, 100, 'N');
INSERT INTO TEST VALUES (7, 100, 'N');
INSERT INTO TEST VALUES (8, 100, 'N');
INSERT INTO TEST VALUES (9, 100, 'N');
INSERT INTO TEST VALUES (10, 100, 'N');
SELECT @@TRANCOUNT;
SELECT * FROM TEST;
-- TEST 1
TX1 TX2
----------------------------------------------------------------------------------------------------------------
-- 1.
BEGIN TRAN
INSERT INTO TEST VALUES (11, 1000, 'N');
-- 2. Blocking 된다.
UPDATE T2 SET T2.LAST_YN = CASE WHEN (SELECT MAX(T1.NO) FROM TEST T1) = T2.NO
THEN 'Y'
ELSE 'N'
END
FROM TEST T2;
-- 이 조건을 넣어도 Blocking 걸린다. 즉 TX1에 추가되는 Record를 TX2가 처리될때 고려하지 않고 무조건 Blocking걸리다가
-- 실제 UPDATE 처리할때 해당값의 범위로 처리를 한다. 역시 Current Mode다.
-- 결과값은 스칼라 서브쿼리의 결과 11과 UPDATE 범위 조건 10이하의 조건이 맞는게 없어서 Y값으로 UPDATE되는 Record는 없다.
WHERE T2.NO <= 10;
-- 2. Blocking 된다. 밖깥쪽 UPDATE 쿼리의 컬럼이 스칼라 서브쿼리에 포함여부는 결과에 영향이 없다.
-- SQL SERVER는 쿼리의 형태와는 상관없이 무조건 Current Mode이다.
BEGIN TRAN
UPDATE T2 SET T2.LAST_YN = (SELECT CASE WHEN MAX(T1.NO) = T2.NO
THEN 'Y'
ELSE 'N'
END
FROM TEST T1)
FROM TEST T2;
-- 3.
COMMIT TRAN;
-- 4.
(11 row(s) affected)
-- 5. 11 -> Y
SELECT * FROM TEST;
* TX1에서 KEY Lock이 걸리고, TX2의 UPDATE 범위에 TX1에서 Lock이 걸린 Record가 포함되므로 Blocking 된다.
-- TEST 2 (TEST 1상황을 반대로하면 결과가 다른다.)
TX1 TX2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 1. 쿼리방식은 상관없다.
BEGIN TRAN
UPDATE T2 SET T2.LAST_YN = (SELECT CASE WHEN MAX(T1.NO) = T2.NO
THEN 'Y'
ELSE 'N'
END
FROM TEST T1)
FROM TEST T2;
-- 2. Blocking 없다.
-- But TX1에 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 지정하면 Blocking은 되나 결과는 같다.
INSERT INTO TEST VALUES (11, 100, 'N');
-- 3.
COMMIT TRAN
-- TX1에 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 지정시
(1 row(s) affected)
-- 4. 10 -> Y
SELECT * FROM TEST;
* TX1에서 KEY Lock이 걸리나 TX2의 INSERT 대상과 상관없으므로 Blocking 되지 않는다.
- ORACLE은 TX2의 처리범위가 TX1을 제외하여 Blocking없이 처리되었다. 즉 TX2의 처리당시 ACTIVE Record를 제외한 이전 값을 기준으로 처리되었다.
즉, Consistent mode로 UPDATE하는 반면
- SQL SERVER는 TX2가 처리될때 Blocking되고, TX1이 COMMIT되면 이때 다시 테이블의 최신 데이터를 조회하여 UPDATE처리를 한다.
즉 Current mode로 UPDATE 한다.