* Consisten Mode : 스칼라 서브쿼리는 특별한 이유가 없는한 항상 Consistent Mode로 읽는다.
스칼라 서브쿼리안에 Current Mode로 읽어야 할 컬럼이 명시되었다면 스칼라 서브쿼리까지도 Current Mode로 읽는다.
-- 테이블 생성
CREATE TABLE ACC_01
NOLOGGING
AS
SELECT EMPNO ACC_NO, ENAME ACC_NM, 1000 ACC_REMAIN FROM EMP;
CREATE TABLE ACC_02
NOLOGGING
AS
SELECT EMPNO ACC_NO, ENAME ACC_NM, 1000 ACC_REMAIN, 2000 ACC_TOT_REMAIN FROM EMP;
ALTER TABLE ACC_01 ADD CONSTRAINT ACC_01_PK PRIMARY KEY(ACC_NO);
ALTER TABLE ACC_02 ADD CONSTRAINT ACC_02_PK PRIMARY KEY(ACC_NO);
-- 초기화
UPDATE ACC_01 SET ACC_REMAIN = 1000;
UPDATE ACC_02 SET ACC_REMAIN = 1000, ACC_TOT_REMAIN = 2000;
COMMIT;
-- 조회
SELECT AC1.ACC_NO
, AC1.ACC_REMAIN
, AC2.ACC_REMAIN
, AC2.ACC_TOT_REMAIN
, AC1.ACC_REMAIN + AC2.ACC_REMAIN AS ACC_REMAIN2
FROM ACC_01 AC1
jOIN ACC_02 AC2 ON AC2.ACC_NO = AC1.ACC_NO
WHERE AC1.ACC_NO = 7698;
-- TEST 1
TX1 TX2
--------------------------------------------------------------------------------------------------------------------------------------------------------
-- 1.
UPDATE ACC_01 SET ACC_REMAIN = ACC_REMAIN + 100 WHERE ACC_NO = 7698;
UPDATE ACC_02 SET ACC_REMAIN = ACC_REMAIN + 200 WHERE ACC_NO = 7698;
-- 2. Blocking, Consistent Mode Select & Current Mode Update
UPDATE ACC_02 AC2 SET AC2.ACC_TOT_REMAIN = AC2.ACC_REMAIN + (SELECT AC1.ACC_REMAIN
FROM ACC_01 AC1
WHERE AC1.ACC_NO = AC2.ACC_NO)
WHERE AC2.ACC_NO = 7698;
-- 3.
COMMIT;
-- 4.
COMMIT;
-- 5. 결과 -> 1200(AC2.ACC_REMAIN) + 1000(AC1.ACC_REMAIN) = 2200
7698 1100 1200 2200 2300
-- TEST 2
TX1 TX2
--------------------------------------------------------------------------------------------------------------------------------------------------------
-- 1.
UPDATE ACC_01 SET ACC_REMAIN = ACC_REMAIN + 100 WHERE ACC_NO = 7698;
UPDATE ACC_02 SET ACC_REMAIN = ACC_REMAIN + 200 WHERE ACC_NO = 7698;
-- 2. Blocking, Current Mode Select & Update
UPDATE ACC_02 AC2 SET AC2.ACC_TOT_REMAIN = (SELECT AC1.ACC_REMAIN + AC2.ACC_REMAIN
FROM ACC_01 AC1
WHERE AC1.ACC_NO = AC2.ACC_NO)
WHERE AC2.ACC_NO = 7698;
-- 3.
COMMIT;
-- 4.
COMMIT;
-- 5. 결과 -> 1200(AC2.ACC_REMAIN) + 1100(AC1.ACC_REMAIN) = 2300
7698 1100 1200 2300 2300
SELECT * FROM V$VERSION;
---------------------------------------------------------------------------------------
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
"CORE 12.1.0.1.0 Production"
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
* ORACLE은 위 예제와 같이 Consistent Mode로 읽고, Current Mode로 UPDATE하기때문 일관성없는 갱신오류 발생할수 있다.
But SQL SEVER에서의 결과는 위 예제의 결과와 달리 쿼리형태와는 상관없어 모두 일괄성이 유지된다.
-- 또 다른 예제
TX1 TX2
--------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT * FROM TEST;
NO AMT L
---------- ---------- -
11 100 N
1 100 N
2 100 N
3 100 N
4 100 N
5 100 N
6 100 N
7 100 N
8 100 N
9 100 N
10 100 Y
-- 1.
UPDATE TEST SET AMT = 110
WHERE NO = 11;
-- 2. Blocking, 결과 -> SIGN(100 - 110) -> -1 -> 90
UPDATE TEST T SET T.AMT = DECODE(SIGN((SELECT D.AMT FROM TEST D WHERE D.NO = T.NO) - T.AMT), 1, 120, 0, 100, -1, 90)
WHERE T.NO = 11;
-- 2. Blocking, 결과 -> SIGN(110 - 110) -> 0 -> 100
UPDATE TEST T SET T.AMT = (SELECT DECODE(SIGN(D.AMT - T.AMT), 1, 120, 0, 100, -1, 90) FROM TEST D WHERE D.NO = T.NO)
WHERE T.NO = 11;
-- 3.
COMMIT;
-- 4.
1 행 이(가) 업데이트되었습니다.
-- 5.
COMMIT;