* Phantom Read현상을 확인하고 이를 방지를 위한 Isolation level serializable 테스트
* Oracle은 Isolation level 설정과 상관없이 Transaction lock이 발생하지 않으나(Undo Data를 이용한 CR활용),
SQL Server는 Isolation level을 Serializable로 설정하면 lock이 걸린다 (Lock이용).
Oracle이 동시성이 좋다.
-- Oracle 기본환경
------------------------------------------------------------------------------------------------
-- 1.
DROP TABLE TEST;
DROP TABLE TEST01;
DROP TABLE TEST02;
CREATE TABLE TEST
AS
SELECT EMPNO, ENAME, JOB, MGR, SAL, DEPTNO FROM EMP;
-- 2.
SELECT * FROM TEST;
-- 3.
CREATE TABLE TEST01
AS
SELECT JOB, COUNT(*) AS CNT
FROM TEST
WHERE 1 = 0
GROUP BY JOB;
-- 4.
CREATE TABLE TEST02
AS
SELECT DEPTNO, COUNT(*) AS CNT
FROM TEST
WHERE 1 = 0
GROUP BY DEPTNO;
-- 5.
DELETE FROM TEST01;
DELETE FROM TEST02;
DELETE FROM TEST WHERE ENAME = 'AIDEN';
COMMIT;
SELECT * FROM TEST01;
SELECT * FROM TEST02;
SELECT * FROM TEST;
EMPNO ENAME JOB MGR SAL DEPTNO
---------- ---------- --------- ---------- ---------- ----------
1111 PARK DEVELOPER 7782 1100 10
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
13 rows selected.
-- TEST 1 (Phantom Read 발생)
TX1 TX2
--------------------------------------------------------------------------------------------------------------------------------------------------------
-- 1.
INSERT INTO TEST01
SELECT JOB, COUNT(*) AS CNT
FROM TEST
GROUP BY JOB;
6 rows inserted.
-- 2. Blocking 없음
INSERT INTO TEST
VALUES ('7777', 'AIDEN', 'DEVELOPER', '7782', 1200, 10);
COMMIT;
1 row inserted.
Commit complete.
-- 3.
INSERT INTO TEST02
SELECT DEPTNO, COUNT(*) AS CNT
FROM TEST
GROUP BY DEPTNO;
3 rows inserted.
-- 4.
COMMIT;
Commit complete.
-- 5.
SELECT * FROM TEST01; <- 13건
JOB CNT
--------- ----------
CLERK 3
SALESMAN 4
PRESIDENT 1
MANAGER 3
DEVELOPER 1
ANALYST 1
6 rows selected.
SELECT * FROM TEST02; <- 14건 (Phantom Read현상 발생)
DEPTNO CNT
---------- ----------
30 6
20 3
10 5
3 rows selected.
SELECT * FROM TEST;
EMPNO ENAME JOB MGR SAL DEPTNO
---------- ---------- --------- ---------- ---------- ----------
1111 PARK DEVELOPER 7782 1100 10
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
7777 AIDEN DEVELOPER 7782 1200 10
14 rows selected.
-- TEST 2 (SERIALIZABLE 설정으로 Phantom Read방지)
TX1 TX2
--------------------------------------------------------------------------------------------------------------------------------------------------------
-- 0.
DELETE FROM TEST01;
DELETE FROM TEST02;
DELETE FROM TEST WHERE ENAME = 'AIDEN';
COMMIT;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 1.
INSERT INTO TEST01
SELECT JOB, COUNT(*) AS CNT
FROM TEST
GROUP BY JOB;
6 rows inserted.
-- 2. Blocking 없음
INSERT INTO TEST
VALUES ('7777', 'AIDEN', 'DEVELOPER', '7782', 1200, 10);
COMMIT;
1 row inserted.
Commit complete.
-- 3.
INSERT INTO TEST02
SELECT DEPTNO, COUNT(*) AS CNT
FROM TEST
GROUP BY DEPTNO;
3 rows inserted.
-- 4.
COMMIT;
Commit complete.
-- 5.
SELECT * FROM TEST01; <- 13건 (TEST 1과 같음)
JOB CNT
--------- ----------
CLERK 3
SALESMAN 4
PRESIDENT 1
MANAGER 3
DEVELOPER 1
ANALYST 1
6 rows selected.
SELECT * FROM TEST02; <- 13건 (Phantom Read현상 없음)
DEPTNO CNT
---------- ----------
30 6
20 3
10 4 <- TX2의 Insert Record무시
3 rows selected.
SELECT * FROM TEST; <- TEST 1과 당연히 같음.
EMPNO ENAME JOB MGR SAL DEPTNO
---------- ---------- --------- ---------- ---------- ----------
1111 PARK DEVELOPER 7782 1100 10
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7839 KING PRESIDENT 5000 10
7844 TURNER SALESMAN 7698 1500 30
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
7777 AIDEN DEVELOPER 7782 1200 10
14 rows selected.
ORACLE은 COMMIT이 되면 별도의 Isolation level 초기화 명령이나 재설정이 필요없다.
-- SQL Server 기본환경
------------------------------------------------------------------------------------------------
-- 0.
CREATE TABLE "TEST"
("EMPNO" INTEGER
,"ENAME" VARCHAR(10)
,"JOB" VARCHAR(10)
,"MGR" INTEGER
,"SAL" INTEGER
,"DEPTNO" INTEGER
);
-- 1.
DELETE FROM TEST;
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (1111,'PARK','DEVELOPER',7782,1100,10);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7369,'SMITH','CLERK',7902,800,20);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7499,'ALLEN','SALESMAN',7698,1600,30);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7521,'WARD','SALESMAN',7698,1250,30);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7566,'JONES','MANAGER',7839,2975,20);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7654,'MARTIN','SALESMAN',7698,1250,30);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7698,'BLAKE','MANAGER',7839,2850,30);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7782,'CLARK','MANAGER',7839,2450,10);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7839,'KING','PRESIDENT',NULL,5000,10);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7844,'TURNER','SALESMAN',7698,1500,30);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7900,'JAMES','CLERK',7698,950,30);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7902,'FORD','ANALYST',7566,3000,20);
INSERT INTO TEST (EMPNO,ENAME,JOB,MGR,SAL,DEPTNO) VALUES (7934,'MILLER','CLERK',7782,1300,10);
-- 2.
SELECT * FROM TEST;
-- 3.
SELECT JOB, COUNT(*) AS CNT
INTO TEST01
FROM TEST
WHERE 1 = 0
GROUP BY JOB;
-- 4.
SELECT DEPTNO, COUNT(*) AS CNT
INTO TEST02
FROM TEST
WHERE 1 = 0
GROUP BY DEPTNO;
-- 5.
DELETE FROM TEST01;
DELETE FROM TEST02;
DELETE FROM TEST WHERE ENAME = 'AIDEN';
SELECT * FROM TEST01;
SELECT * FROM TEST02;
SELECT * FROM TEST;
EMPNO ENAME JOB MGR SAL DEPTNO
-----------------------------------------------------------
1111 PARK DEVELOPER 7782 1100 10
7369 SMITH CLERK 7902 800 20
7499 ALLEN SALESMAN 7698 1600 30
7521 WARD SALESMAN 7698 1250 30
7566 JONES MANAGER 7839 2975 20
7654 MARTIN SALESMAN 7698 1250 30
7698 BLAKE MANAGER 7839 2850 30
7782 CLARK MANAGER 7839 2450 10
7839 KING PRESIDENT NULL 5000 10
7844 TURNER SALESMAN 7698 1500 30
7900 JAMES CLERK 7698 950 30
7902 FORD ANALYST 7566 3000 20
7934 MILLER CLERK 7782 1300 10
-- TEST 1 (Phantom Read 발생)
TX1 TX2
--------------------------------------------------------------------------------------------------------------------------------------------------------
-- 1.
BEGIN TRAN
INSERT INTO TEST01
SELECT JOB, COUNT(*) AS CNT
FROM TEST
GROUP BY JOB;
-- 2. 1
SELECT @@TRANCOUNT;
-- 3. Blocking 없음
INSERT INTO TEST
VALUES ('7777', 'AIDEN', 'DEVELOPER', '7782', 1200, 10);
(1개 행이 영향을 받음)
-- 4.
INSERT INTO TEST02
SELECT DEPTNO, COUNT(*) AS CNT
FROM TEST
GROUP BY DEPTNO;
COMMIT TRAN;
-- 5. 0
SELECT @@TRANCOUNT;
SELECT * FROM TEST01; <- 13건
JOB CNT
-------------------
ANALYST 1
CLERK 3
DEVELOPER 1
MANAGER 3
PRESIDENT 1
SALESMAN 4
SELECT * FROM TEST02; <- 14건 (Phantom Read현상 발생)
DEPTNO CNT
--------------------
10 5
20 3
30 6
-- TEST 2 (SERIALIZABLE 설정으로 Phantom Read방지)
TX1 TX2
--------------------------------------------------------------------------------------------------------------------------------------------------------
-- 0.
DELETE FROM TEST01;
DELETE FROM TEST02;
DELETE FROM TEST WHERE ENAME = 'AIDEN';
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 1.
BEGIN TRAN
INSERT INTO TEST01
SELECT JOB, COUNT(*) AS CNT
FROM TEST
GROUP BY JOB;
-- 2. 1
SELECT @@TRANCOUNT;
-- 3. Blocking 발생
INSERT INTO TEST
VALUES ('7777', 'AIDEN', 'DEVELOPER', '7782', 1200, 10);
-- 4.
INSERT INTO TEST02
SELECT DEPTNO, COUNT(*) AS CNT
FROM TEST
GROUP BY DEPTNO;
COMMIT TRAN;
(1개 행이 영향을 받음)
-- 5. 0
SELECT @@TRANCOUNT;
-- 6.
DBCC USEROPTIONS;
-- 7. 명시적으로 설정 원복하지 않으면 Session Level로 유지된다.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 8. isolation level 조회
DBCC USEROPTIONS;
SELECT * FROM TEST01; <- 13건
JOB CNT
-------------------
ANALYST 1
CLERK 3
DEVELOPER 1
MANAGER 3
PRESIDENT 1
SALESMAN 4
SELECT * FROM TEST02; <- 13건 (Phantom Read현상 없음)
DEPTNO CNT
--------------------
10 4
20 3
30 6