SQL - 트랜잭션 제어와 세션

 

1. 하나의 단위로 데이터를 처리하는 트랜잭션

 

1.1 트랜잭션이란?

 

- 더 이상 분할할 수 없는 최소 수행 단위를 뜻한다.

- 하나의 작업 또는 밀접하게 연관된 작업을 수행하기 위해 한 개 이상의 데이터 조작어로 이루어진다.

- 트랜잭션은 하나의 트랜잭션 내에 있는 여러 명령어를 한 번에 수행하여 작업 완료 or 모든 작업 취소한다. 

  (이를 트랜잭션의 ALL OR NOTHING 원자성이라고도함) 

- 이러한 트랜잭션을 제어하는 명령어를 TCL이라고 한다.

- 트랜잭션은 SCOTT같은 데이터베이스 계정을 통해 접속하는 동시에 실행된다.

- 트랜잭션이 종료되기 전까지 여러 SQL문을 실행하고 트랜잭션 제어하는 TCL을 실행할 때 기존 트랜잭션이 끝난다.

 

 

2. 트랜잭션을 제어하는 명령어 

 

- 하나의 트랜잭션에 묶여 있는 데이터 조작어의 수행 상태는 수행 완료 혹은 수행 취소 두 가지 상태로만 존재할 수 있다.

- 즉 데이터 조작을 데이터 베이스에 영구히 반영 혹은 전체 취소한다.

 

2.1 트랜잭션을 취소하고 싶을 때 ROLLBACK

INSERT INTO DEPT_TCL VALUES (50,'DATABASE','SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH'; 
ROLLBACK;

 

2.2 트랜잭션을 영원히 반영하고 싶을 때 COMMIT

 

 -롤백과 달리 트랜잭션 명령어를 데이터 베이스에 영구히 반영 

INSERT INTO DEPT_TCL VALUES (50,'NETWORK','SEOUL');
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 20;
DELETE FROM DEPT_TCL WHERE DEPTNO = 40; 
COMMIT;

- COMMIT 이후에는 새로운 트랜잭션이 실행된다.

- DDL은 자동으로 트랜잭션이 종료됨 

 

- savepoint를 지정해서 롤백 위치를 지정할 수도 있다.

UPDATE DEPT_TCL SET LOC = 'JEJU' WHERE DEPTNO = 50;
savepoint P1;
UPDATE DEPT_TCL SET LOC = 'SEOUL' WHERE DEPTNO = 30;
ROLLBACK to P1;

 

3. 세션과 읽기 일관성의 의미

 

3.1 세션이란

 

- 일반적으로 세션은 어떤 활동을 위한 시간 혹은 기간을 의미한다.

- 오라클 DB에서 세션은 DB접속 시작으로 DB 관련 작업을 수행한 후 접속을 종료하기까지 전체 기간을 의미합니다.

- 세션이 여러개라는 말은 DB 사용 중인 연결이 여러 개 있다는 뜻입니다.

 

* 세션은 접속,connection을 의미하기도함

 

3.2 읽기 일관성의 중요성

 

- 데이터베이스는 여러 곳(여러 사용자, 여러 응용 프로그램)에서 동시에 접근하여 관리 및 사용하는 것이 목적이므로

  대부분 많은 세션이 동시에 연결되어 있다.

 

- 읽기 일관성이란 어떤 세션에서 테이블 데이터 변경 중일 때 다른 세션에서는 변경 사항을 알 필요가 없으므로, 변경 중인 세션 외 나미지 세션에서는 진행 중인 변경과 무관한 본래의 데이터를 보여주는 특성을 의미한다.

 

*SQL-Developer 외 cmd창을 통해 SQL-PLUS를 실행시켜서 새로운 접속을 만들어서 실습해보자 

--sqldeveloper
update dept_TCL set loc='seoul' where deptno=30;
select * FROM DEPT_TCL;

--SQLPLUS 
SELECT * FROM DEPT_TCL;

 

- 두 결과는 다르다. 트랜잭션이 각각 따로 유지되기 때문이다.

- sql-developer에서 commit을 한 뒤에 다시 sql-plus에서 확인해보면 결과가 잘 반영된 것을 볼 수 있다. 

 

* 같은 아이디로 접속했지만, 두개는 다른 세션이다. (같은 아이디로 인증받은 것 뿐, DB는 다른 세션으로 인식)

* 접속하는 단위에 따라 세션 구분 -> sql디벨로퍼 여러개 켜도 다른 세션 (각각 다른 connetion 생성된다고 봅시다)

 

4. 수정 중인 데이터 접근을 막는 LOCK

 

4.1 LOCK란?

 

- 트랜잭션이 완료되기 전까지 다른 세션에서 조작할 수 없는 상태가 된다.

- 조작 중인 데이터를 다른 세션은 조작할 수 없도록 접근을 보류시키는 것을 뜻한다.

 

4.2 LOCK 개념 살펴보기

 

* LOCK을 sql-plus와 sql-developer를 통해 실습해보자 

--sql-developer
update dept_TCL set loc='ooo' where deptno=30;

- 한 세션에서 update한 뒤에 commit이 이루어지지 않았음으로, 다른 세션은 update를 반영하지 못하고 대기

- 한 쪽에서 commit이 완료되면, 후에 다른 세션 작업이 반영된다.

 

4.3 LOCK 종류

 

- 행 레벨 락(row level lock):  SQL을 통해 조작하는 대상 데이터가 테이블의 특정 행 데이터일 경우 해당 행만 LOCK

 

- 테이블 레벨 락(table level lock): WHERE절을 지정하지 않은 UPDATE,DELETE 등에 발생 

                                                      - 테이블 레벨 락 발생시 다른 세션이 UPDATE,DELETE 수행 불가

                                                      - 타 세션 DDL사용불가

                                                      - INSERT문은 사용가능   

* 실무에서 데이터 관련 작업을 할 때 실제 서비스에 사용하는 운영 DB에 바로 작업 수행하는 경우 흔치 않다. 대부분 테스트 혹은 개발 전용 DB에서 시험 삼아 테스트해 본 후에 문제가 없으면 실제 운영 중인 DB에 적용한다.