SQL-DML문의 이해, 트랜잭션과 잠금(lock)

실질적으로 데이터양의 변동이 일어남. 모든 작업은 테이블에서 행 단위로 일어나기 때문에 행을 일부만 지우거나 수정할 수는 없다.

* INSERT, UPDATE, DELETE
INSERT INTO <테이블> [(컬럼, 컬럼, ... )]
VALUES (값, 값, ...);

UPDATE 테이블
SET 컬럼 = 값, 컬럼 = 값, ...
[WHERE 조건]; // 수정하려는 특정 컬럼 테이블행의 조건을 기술

DELETE FROM 테이블
[WHERE 조건]; // 조건에 맞는 행들만 삭제

---------------------------------------------------------------
COMMIT; // 작업이 완료됐음을 의미.
ROLLBACK; // 작업을 취소한다.

COMMIT이나 ROLLBACK 하기전까지 데이터를 가지고 있는 공간을 undo segment라 한다.

[예제] EMP 테이블에 아래 제공된 값을 입력

INSERT INTO emp (eno, ename, sex, job, mgr, hdate, sal, comm, dno)
VALUES ('1001', '문시현', '남', '모델링', NULL, '1991/02/01', 4500, 520, '10');
// 날짜부분이 정상적으로 입력되지만 항상 TO_DATE를 입력해야 한다.

[예제 5]날짜 형식을 확인하고 아래 제공된 값을 입력. 날짜 입력에 TO_DATE()를 사용한다.

ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
INSERT INTO emp (eno, ename, hdate)
VALUES ('5002', '안영숙', TO_DATE('1992/09/05:08:15:25', 'YYYY/MM/DD:HH24:MI:SS'));
// 가장 좋은 입력값. 앞으로 최대한 이렇게 입력할수 있도록 한다.

[예제 6]날짜에 적용되는 디폴트 값 확인

ALTER SESSION SET nls_date_format='YYYY-MM-DD:HH24:MI:SS';
INSERT INTO emp (eno,hdate) VALUES ('01', TO_DATE('2000', 'YYYY')); // 년도만 2000년도로 설정
INSERT INTO emp (eno,hdate) VALUES ('02', TO_DATE('99', 'YY'));
// YY설정은 뒤에 두 숫자만 바꾸고 앞에 두자리는 그대로
INSERT INTO emp (eno,hdate) VALUES ('03', TO_DATE('99', 'RR')); // 뒷자리가 99인 가장 가까운 년도로 설정
COMMIT;
SELECT eno, hdate FROM emp
ORDER BY 1;

예제 7. 김주란의 부서 번호를 10번으로 수정하고 급여를 10% 인상(단, 수행 전 실습용 테이블을 초기화)

@c:\sql\school // 초기화
SELECT * FROM emp WHERE ename = '김주란'; // 기존 정보 확인
UPDATE emp SET dno = '10', sal = sal*1.1
WHERE ename = '김주란';
COMMIT;
SELECT * FROM emp WHERE ename = '김주란'; // 갱신 정보 확인

[실습]
1. 다음 INSERT 문을 실행하고 입력 결과를 확인한다. 입력값에 포함되지 않은 년도, 월, 날짜, 시, 분, 초에 입력된 값을 확인.

ALTER SESSION SET nls_date_format='YYYY-MM-DD:HH24:MI:SS';

SQL> INSERT INTO emp (eno, hdate) VALUES (5, TO_DATE('12', 'MM')); // 월을 12월로 지정
SQL> INSERT INTO emp (eno, hdate) VALUES (6, TO_DATE('10', 'DD')); // 달을 10일로 지정
SQL> INSERT INTO emp (eno, hdate) VALUES (7, TO_DATE('9', 'HH24')); // 시간을 09시로 지정
SQL> INSERT INTO emp (eno, hdate) VALUES (8, TO_DATE('12', 'MI')); // 분을 12일로 지정
SQL> INSERT INTO emp (eno, hdate) VALUES (9, TO_DATE('20', 'SS')); // 초를 20초로 지정
SQL> INSERT INTO emp (eno, hdate) VALUES (10, sysdate); // 현재시간으로 지정

3. 모든 학생의 성적을 4.5만점 기준으로 수정.

UPDATE student
SET avr = avr*4.5 / 4;

4. 모든 교수의 부임일자를 100일 전으로 수정.

UPDATE professor
SET hiredate = hiredate - 100;

5. 화학과 2학년 학생의 정보를 삭제.
DELETE FROM student
WHERE major = '화학' AND syear = '2';

6. 조교수의 정보를 삭제.

DELETE FROM professor
WHERE orders = '조교수';

* 트랜잭션

commit 전

commit 후

[세션 1]에서 emp 테이블을 삭제해도 [세션 2]에서는 emp 테이블의 정보가 조회된다. 단 commit까지 하면 조회되지 않는다.

[세션 1]에서 dept를 삭제후 commit이나 rollback을 하지 않고 [세션 2]에서 dept에 대한 update를 수행하면 deadlock에 걸린다.

원자성(Atomicity)
- 트랜잭션은 최소의 작업 단위로서 전체가 처리되거나 취소될 수 있지만 일부만 처리될 수 없다.

일관성(Consistency)
- 트랜잭션이 실행된 이후 데이터베이스의 무결성은 반드시 유지돼야 한다.

독립성(Isolation)
- 트랜잭션을 여러 개 동시에 실행하더라도 각각의 트랜잭션은 서로 영향을 줄 수 없다. 즉, 실행이 종료되지 않은 트랜잭션의 결과는 다른 트랜잭션에서 참조하는 것이 불가능하다.

영속성(Durability)
- 종료된 트랜잭션의 결과는 반드시 데이터베이스에 반영돼야 한다.

* 트랜잭션의 시작과 종료
1. 시작
- 이전 트랜잭션이 종료된 이후 DML(INSERT, UPDATE, DELETE)문장이나
DDL(CREATE, LTER,DROP, TRUNCATE), DCL(GRANT, REVOKE)문장이 실행됐을 때 시작.

2. 종료
- COMMIT이나 ROLLBACK 명령이 실행 될 때 종료된다.
- DDL이나 DCL문장의 실행이 완료되면 자동으로 종료된다.
- 사용자의 정상 종료 시에 종료된다.
- 데드락(Deadlock)이 걸리면 트랜잭션의 일부만 종료된다.

* 트랜잭션 과정

테이블의 5를 7로 바꾸기 위한 순서
1. undo segment를 찾는다.
2. undo segment에 5를 저장
3. 7을 테이블에 새롭게 넣어준다.
4. 트랜잭션 종료 전 테이블은 행에 잠금(lock)이 발생했기 때문에 수정된 값인 7이 아닌 5가 조회된다.
5. 트랜잭션 종료 전 테이블은 잠금(lock)이 발생했기 때문에 해당 행에 대한 수정, 삭제 명령시 데드락이 발생한다.

* 독점 잠금(Exclusive lock)과 공유 잠금(Share lock)
독점 잠금은 트랜잭션의 독립성을 보장하기 위해서 현재 세션이외에는 접근을 불허하는 잠금이다. 트랜잭션으로 행에 잠금이 발생하면 다른 세션에서는 해당 행을 검색할 수 없고 단지 undo segment의 정보만 보게 된다.
그리고 이때 테이블에는 공유 잠금이 발생하는데 이것은 DML작업으로 행이 잠겨있는 테이블에 대해 DDL(DROP, ALTER)작업을 방지한다. 공유 잠금은 독점 잠금이 걸린 행 이외 행에 대한 접근을 방해하지 않는다.

독점 잠금은 행에 걸리고, 공유 잠금은 테이블에 걸린다.

[예제 환경설정]
- 두 개의 일반 사용자 세션.
- 동일한 st 계정으로 접속한 Sqlplus창을 두 개 실행.
- 두 개의 창은 [세션 1]과 [세션 2]로 구분.

예제 1. 트랜잭션과 잠금의 이해

[세션 1]

SELECT eno, ename, sal FROM emp
WHERE ename = '문시현'; // 검색됨

UPDATE emp SET sal = sal*2
WHERE ename = '문시현'; // 급여를 2배로 수정

SELECT eno, ename, sal FROM emp
WHERE ename = '문시현'; // 급여가 수정된 채로 조회되지만, commit하지는 않음.

[세션 2]

SELECT eno, ename, sal FROM emp
WHERE ename = '문시현'; // 수정 전 연봉 값으로 조회됨.(undo segment 값으로 조회됨)

[세션 1]

COMMIT;

[세션 2]

SELECT eno, ename, sal FROM emp
WHERE ename = '문시현'; // 수정된 값으로 조회 됨.

[예제 2] 트랜잭션에 의한 대기 현상 확인
[세션 1]

SELECT eno, ename, sal FROM emp
WHERE ename = '안영희';

UPDATE emp SET sal = sal * 1.5
WHERE ename = '안영희'; // 수정 전 연봉 값은 undo segment에 저장

SELECT eno, ename, sal FROM emp
WHERE ename = '안영희';

[세션 2]

SELECT eno, ename, sal FROM emp
WHERE ename = '안영희'; // 수정 전 연봉 값으로 조회됨.(undo segment 값으로 조회됨)

UPDATE emp SET comm = 900
WHERE ename = '안영희'; // 세션 1에서 잠금이 걸려있기 때문에 데드락에 걸림

[세션 1]

COMMIT;

SELECT eno, ename, sal, comm FROM emp
WHERE ename = '안영희'; // comm값은 세션 2에서 아직 COMMIT이 되지 않았기때문에 0으로 나옴.

[세션 2]

UPDATE emp SET comm = 900
WHERE ename = '안영희'; // [세션 1]의 값이 COMMIT됐기 때문에 데드락에 걸렸던 명령이 수행됨
COMMIT;

[세션 1]

SELECT eno, ename, sal, comm FROM emp
WHERE ename = '안영희'; // 세션2에서 COMMIT됐기 때문에 comm의 명령도 900으로 출력

[예제 3]데드락(Dead Lock)을 발생하고 RDBMS의 처리 과정을 확인.
- [세션 1]과 [세션 2]는 각각 서로 다른 행에 독점 잠금을 걸고 서로 상대 트랜잭션이 독점 잠금을 걸어둔 행에 DML을 실행해 본다.

[세션 1]

SELECT eno, ename, dno FROM emp
WHERE ename IN ('안영희','문시현');

UPDATE emp SET dno = '02'
WHERE ename = '안영희'; // 안영희의 dno를 수정. 안영희의 세션에 lock이 걸림

SELECT eno, ename, dno FROM emp
WHERE ename IN ('안영희','문시현');

[세션 2]

SELECT eno, ename, dno FROM emp
WHERE ename IN ('안영희','문시현');

UPDATE emp SET dno = '20'
WHERE ename = '문시현'; // 문시현의 dno를 수정. 문시현의 세션에 lock이 걸림

SELECT eno, ename, dno FROM emp
WHERE ename IN ('안영희','문시현');

UPDATE emp SET sal = sal * 1.5
WHERE ename = '안영희'; // 세션 1에서 잠금이 걸린 상태이기때문에 웨이팅에 걸림.

[세션 1]

UPDATE emp SET sal = sal * 1.5
WHERE ename = '문시현'; // 세션 2에서 잠금이 걸린 상태이기때문에 웨이팅에 걸림.

[세션 2]

UPDATE emp SET sal = sal * 1.5
WHERE ename = '안영희'; // 둘다 웨이팅에 빠져서 무한 교착상태이기때문에 명령을 취소시킨다.

* 용어 및 개념 정리
DB에서의 segment : 물리적인 공간이 필요한 object