15. Chapter10 Transaction Control(20081031)

Transaction (P10-2)

• 연관되는 연속적인 작업이 있을 때 모든 작업이 끝나던지, 중간에 문제가 생긴다면 완전 처음으로 되돌아가도록 해야함 -> 트랜잭션으로 묶으면 DB에서 알아서 해줌~
• Oracle에서는 SQLPLUS를 시작하면 트랜잭션이 자동으로 시작됨
• COMMIT, ROLLBACK을 사용하면 명시적으로 트랜잭션이 종료됨

COMMIT : 트랜잭션 시작 이후에 모든 정보 DB에 저장

ROLLBACK : 트랜잭션 시작 이후에 모든 정보 취소

• SQLPLUS에서 EXIT로 종료를 하면 COMMIT을 하고 종료되고 그냥 프로그램을 종료하면 ROLLBACK을 하고 종료된다.
• DML은 자동으로 트랙잭션 시작, DDL, DCL은 자동 COMMIT로서 ROLLBACK 안됨.

 

• COMMIT & ROLLBACK 예제

1. SQL> DELETE FROM emp;
2. 15 rows deleted.
3.  
4. SQL> DELETE FROM dept;
5. 6 rows deleted.
6.  
7. SQL> ROLLBACK;
8. Rollback complete.
9.  
10. SQL> SELECT count(*) FROM emp;
11.   COUNT(*)
    ----------
            15
12.  
13. SQL> SELECT count(*) FROM dept;
14.   COUNT(*)
    ----------
             6

 

• DDL 사용으로 인한 COMMIT

1. SQL> SELECT * FROM dept;
2.     DEPTNO DNAME          LOC
   ---------- -------------- -------------
           10 ACCOUNTING     NEW YORK
           20 RESEARCH       DALLAS
           30 SALES          CHICAGO
           40 OPERATIONS     BOSTON
           50 Local Branck   Korea
           60 HQs
3. 6 rows selected.
4.  
5. SQL> DELETE FROM dept
     2  WHERE deptno=60;
6. 1 row deleted.
7.  
8. // CREATE 문을 사용해서 이 시점에서 COMMIT이 일어남
9. SQL> CREATE TABLE t (id number);
10. Table created.
11.  
12. SQL> ROLLBACK;
13. Rollback complete.
14.  
15. // ROLLBACK을 했지만 CREATE에서 COMMIT이 일어나서 ROLLBACK이 안됨
16. SQL> SELECT * FROM dept;
17.     DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
            50 Local Branck   Korea

 

Tracsaction Control Statements (P10-5)

• SAVEPOINT x : x 위치에 롤백 시점 지정
• ROLLBACK TO SAVEPOINT x : x위치까지만 롤백 시킴, 트랜잭션이 종료되지는 않고 트랙잭션 시작위치는 그대로고 계속 진행됨.

 

• P10-6 예제

1. SQL> SELECT deptno FROM emp WHERE empno=7788;
2.     DEPTNO
   ----------
           20
3.  
4. SQL> UPDATE emp SET deptno=30 WHERE empno=7788;
5. 1 row updated.
6.  
7. SQL> SAVEPOINT a;
8. Savepoint created.
9.  
10. SQL> DELETE FROM emp;
11. 15 rows deleted.
12.  
13. SQL> ROLLBACK TO a;
14. Rollback complete.
15.  
16. // SAVEPOINT a까지만 롤백을 했기 때문에 UPDATE 이후 값으로 출력됨.
17. SQL> SELECT deptno FROM emp WHERE empno=7788;
18.     DEPTNO
    ----------
            30
19.  
20. // 트랜잭션 시작된 시점으로 롤백
21. SQL> ROLLBACK;
22. Rollback complete.
23.  
24. // UPDATE 전 값으로 롤백됨~
25. SQL> SELECT deptno FROM emp WHERE empno=7788;
26.     DEPTNO
    ----------
            20

 

 

State of the Data During Transaction (P10-7)

• SCOTT 계정으로 SQLPLUS를 2개 띄워서 각각 접속 (같은 아이디 다른 세션)
• SCOTT.A 계정으로 DML문을 사용하면 해당되는 레코드에 자동으로 LOCK이 걸림(COMMIT, ROLLBACK 되기전까지)
• SCOTT.B 계정의 락이 걸린 데이터를 변경 등 하려고하면 대기하고 있다가 SCOTT.A에서 LOCK을 풀면 그때서야 수행됨.
• SCOTT.A 계정에서 데이터를 변경하면 SCOTT.B에서 SELECT 구문을 이용해서 조회하면 SCOTT.A가 변경한 데이터는 안보이고 예전 데이터가 조회됨

 

• SCOTT.A

1. SQL> UPDATE emp
     2  SET sal=0
     3  WHERE deptno=10;
2. 3 rows updated.
3.  
4. SQL> ROLLBACK;
5. Rollback complete.
6.  
7. SQL>  UPDATE emp
     2   SET sal=0
     3   WHERE deptno=10;
8. 3 rows updated.
9.  
10. SQL> SELECT sal FROM emp WHERE deptno=10;
11.        SAL
    ----------
             0
             0
             0

 

• SCOTT.B

1.  SQL> SELECT sal
     2  FROM emp
     3  WHERE deptno=10;
2.        SAL
   ----------
         2450
         5000
         1300
3.  
4. SQL> UPDATE emp
     2  SET sal=5
     3  WHERE deptno=10;
5. 3 rows updated.   // SCOTT.A에서 롤백 등 해야 수행됨
6.  
7. SQL> ROLLBACK;
8. Rollback complete.
9.  
10. SQL> SELECT sal FROM emp WHERE deptno=10;   // SCOTT.A에서 sal=0으로 변경했지만 안보임~
11.        SAL
    ----------
          2450
          5000
          1300

 

 

State of Data After Transaction (P10-8)

SESSION #1

1. SQL> SELECT * FROM tr1;
   SELECT * FROM tr1
                 *
   ERROR at line 1:
   ORA-00942: table or view does not exist
2. 
   SQL> /
3. no rows selected
4.  
5. SQL> /
6. no rows selected
7.  
8. SQL> /
9.          A
   ----------
           10
10.  
11. SQL> UPDATE tr1 SET a=20;
12. 1 row updated.
13.  
14. SQL> SELECT * FROM tr1;
15.          A
    ----------
            20
16.  
17. SQL> COMMIT;
18. Commit complete.
19.  
20. SQL> SELECT * FROM tr1;
21.          A
    ----------
            20
22.  
23. SQL> /
24.          A
    ----------
            20
25.  
26. SQL> /
27.          A
    ----------
            30

 

SESSION #2

1. SQL> CREATE TABLE tr1 (a number);
2. Table created.
3.  
4. SQL> INSERT INTO tr1 VALUES(10);
5. 1 row created.
6.  
7. SQL> SELECT * FROM tr1;
8.          A
   ----------
           10
9.  
10. SQL> COMMIT;
11. Commit complete.
12.  
13. SQL> SELECT * FROM tr1;
14.          A
    ----------
            10
15.  
16. SQL> UPDATE tr1 SET a=30;
17. 1 row updated.
18.  
19. SQL> SELECT * FROM tr1;
20.          A
    ----------
            30
21.  
22. SQL> COMMIT;
23. Commit complete.

 

 

Set Transaction (P10-10)

• 읽기 일관성 : 한번 읽었던 것은 다른곳에서 내용이 변경되도 내가 변경안한거면 처음 내용 그대로 읽어지는것
  
• READ ONLY : 읽기전용

SESSION 1

1. (1)SQL> SET TRANSACTION READ ONLY;   // 읽기 일관성 제공
2. Transaction set.
3.  
4. (2)SQL> SELECT sal FROM emp WHERE deptno=20;
5.        SAL
   ----------
          800
         2975
         3000
         1100
         3000
6.  
7. // SESSION2에서 (3), (4) 수행한 결과가 안보임(READ ONLY)
8. (5)SQL> SELECT sal FROM emp WHERE deptno=20;
9.        SAL
   ----------
          800
         2975
         3000
         1100
         3000

 

SESSION 2

1. (3)SQL> UPDATE emp
     2  SET sal=2000
     3  WHERE deptno=20;
2. 5 rows updated.
3.  
4. (4)SQL> COMMIT;
5. Commit complete.

 

READ WRITE(기본값) :

 

SERIALIZABLE :

읽기 일관성 제공됨((P10-11 표 예제) 1~6번까지가 해당되는 내용)

자기 세션기준으로 초기값 그대로라면 수정가능(?) (다른 세션에 수정안하거나, commit 안한경우??) 

 

 

READ COMMITTED(기본값) :

읽기 일관성 제공안함 -> 다른 세션에서 COMMIT하면 변경된 내용 보임

한 세션에서 DML문장을 사용하는 경우 LOCK이 걸리고 다른 세션에서는 LOCK이 걸린 항목을 변경하려면 변경안되고 기다리고 있음(LOCK을 건 세션에서 COMMIT 등을 하기전까지~)

 

이 글은 스프링노트에서 작성되었습니다.