< 수행 SQL>
-- 1. 데이터 생성
DROP TABLE T;
CREATE TABLE T(X INT);
INSERT INTO T VALUES (1);
BEGIN
FOR i IN 1..10000 LOOP
UPDATE T
SET X = X + 1;
COMMIT;
END LOOP;
END;
/
-- 2. 트레이스 활성화
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE
PL/SQL procedure successfully completed.
;
-- 3. SQL 수행
SELECT *
FROM T
;
X
----------
10001
1 row selected.
;
UPDATE T T1
SET X = X + 1
;
1 row updated.
;
UPDATE T T2
SET X = X + 1
;
1 row updated.
;
< Trace 내용>
SELECT *
FROM T
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 15 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 16 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL T (cr=15 pr=0 pw=0 time=149 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.02 0.02
********************************************************************************
UPDATE T T1
SET X = X + 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.00 0.00 2 15 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 2 16 3 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T (cr=15 pr=2 pw=0 time=1648 us)
1 TABLE ACCESS FULL T (cr=15 pr=0 pw=0 time=165 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
UPDATE T T2
SET X = X + 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 15 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 16 1 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T (cr=15 pr=0 pw=0 time=407 us)
1 TABLE ACCESS FULL T (cr=15 pr=0 pw=0 time=167 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
<UPDATE 진행 순서>
| 시간 | Session_1 | Session_2 | 설명 |
|---|---|---|---|
| T1 | UPDATE T SET Y = 10 WHERE Y = 5; | 조건에 만족하는 한 로우를 수정한다. | |
| T2 | UPDATE T SET X = X + 1 WHERE Y = 5; | Consistent 읽기를 이용해 세션 1이 변경한 레코드를 찾을 수 있지만, 락이 걸렸으므로 update를 진행할 수 없다. 세션 2는 블로킹 상태로 락이 올라오길 기다린다. | |
| T3 | COMMIT; | 세션 2는 블로킹 상태에서 해제된다. 드디어 세션 1이 UPDATE 대상 로우를 포함하는 블록을 CURRENT 모드로 읽을 수 있게 됐다. UPDATE를 시작할 때 Y 값은 5 였지만, 지금 CURRENT 모드로 읽을 때 Y 값은 5가 아닌 10이다. |
<테스트>
-- 1. (Session_1) 데이터 생성
DROP TABLE T;
CREATE TABLE T AS
SELECT LEVEL X, LEVEL Y
FROM DUAL
CONNECT BY LEVEL <= 10
;
-- 2. (Session_1) UPDATE T
UPDATE T
SET Y = 10
WHERE Y = 5
;
1 row updated.
;
-- 3. (Session_2) Session_1에서 UPDATE 하는 같은 레코드를 수행
UPDATE T
SET X = X + 1
WHERE Y = 5
;
-- 4. (Session_3) Lock 모니터링
@LOCK
;
(Node)H-Sid (Node)W-Sid Lock Status W-Time Lock Type H L-Mode R L-Mode LOCKED_OBJ SID/SER# S TR/w/b MODULE PGM W_T WAIT_EVENT LCET SQL
----------- ----------- ----------- ------- --------- -------- -------- -------------------------------- ------------ - ------ ----------------------- ---- ------- ------------------------- ------ ---------------------------
(1)9640 ▽ holding 72 TX Ex None UNIQUE_GEORASTERS(I) 9640,93 I DI/F/F SQL*Plus sqlp 72 SQL*Net message from clie 72
(1)9765 waiting 48 TX None Ex T(T) 9765,45 A DI/F/F SQL*Plus sqlp 48 enq: TX - row lock conten 48 UPDATE T SET X =
;
-- 5. (Session_1) COMMIT
COMMIT;
Commit complete.
;
-- 6. (Session_2) 상태
0 rows updated.
;
-- 1. (Session_1) 데이터 생성
DROP TABLE T;
CREATE TABLE T (X INT, Y INT)
;
INSERT INTO T VALUES (1, 1)
;
COMMIT
;
-- 2. (Session_1) 트리거 생성
CREATE OR REPLACE TRIGGER T_BUTER
BEFORE UPDATE ON T FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE ('OLD.X = ' || :OLD.X || ', OLD.Y = ' || :OLD.Y ) ;
DBMS_OUTPUT.PUT_LINE ('NEW.X = ' || :NEW.X || ', NEW.Y = ' || :NEW.Y ) ;
END;
/
Trigger created.
;
-- 3. (Session_1) 데이터 수정
SET serveroutput ON;
UPDATE T
SET X = X + 1
;
OLD.X = 1, OLD.Y = 1
NEW.X = 2, NEW.Y = 1
1 row updated.
;
-- 4. (Session_2) 데이터 수정
SET serveroutput ON;
UPDATE T
SET X = X + 1
WHERE X > 0
;
-- 5. (Session_1) COMMIT
COMMIT;
Commit complete.
;
-- 6. (Session_2) 확인
OLD.X = 1, OLD.Y = 1
NEW.X = 2, NEW.Y = 1
OLD.X = 2, OLD.Y = 1
NEW.X = 3, NEW.Y = 1
1 row updated.
;
-- 1. (Session_1) 데이터 생성
DROP TABLE T;
CREATE TABLE T (X INT, Y INT)
;
INSERT INTO T VALUES (1, 1)
;
COMMIT
;
-- 2. (Session_1) 트리거 생성
CREATE OR REPLACE TRIGGER T_BUTER
BEFORE UPDATE ON T FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE ('fired') ;
END;
/
Trigger created.
;
-- 3. (Session_1) 데이터 수정
SET serveroutput ON;
UPDATE T
SET X = X + 1
;
OLD.X = 1, OLD.Y = 1
NEW.X = 2, NEW.Y = 1
1 row updated.
;
-- 4. (Session_2) 데이터 수정
SET serveroutput ON;
UPDATE T
SET X = X + 1
WHERE Y > 0
;
-- 5. (Session_1) COMMIT
COMMIT;
Commit complete.
;
-- 6. (Session_2) 확인
fired
1 row updated.
;
1. 트리거에서 처리한 로우 수를 저장하는 PL/SQL 글로벌 변수를 관리하고자 할 경우,
재시작 메커니즘이 작동해 문장을 롤백하더라도, PL/SQL 변수의 값은 변경되지 않음.
2. UTL_(UTL_FILE, UTL_HTTP, UTL_SMTP 등)로 시작하는 어떤 함수로 재시작 메커니즘이 작동하는 순간, 사실상 어떤 문제에 노출될 수밖에 없다고 생각해야 함.
예를들어 문장을 재시작했다고 해서, UTL_FILE이 파일에 스고 있던 내용을 되돌리지는 않음.
3. 자율(autonomous) 트랜잭션으로 선언된 트리거는 일단 의심해 봐야 함. 문장을 재시작하고 롤백하더라도 자율 트랜잭션은 롤백처리되지 않음.