[MySQL] 뷰, 트랜잭션

1. 뷰 (View)

1) 뷰

하나 이상의 테이블에서 원하는 데이터를 선택하여 새로운 가상 테이블로 만들어주는 것이다. 다른 테이블에 있는 데이터를 보여줄 뿐이며 데이터 자체를 포함하고 있는 것은 아니다. (저장장치 내에 물리적으로 존재하지 않고 가상테이블로 만들어진다.) 데이터베이스의 구조를 변경하여도 테이블에 의존하는 어플리케이션을 변경할 필요가 없다. 복잡한 쿼리를 단순하게 만들 수 있어 편리하며 사용자에게 필요 없는 정보를 숨길 수 있다.

 

뷰를 사용하는 이유는 애플리케이션이 DBMS에 의존적인 경우에 DBMS 구조가 변경이 되어도 애플리케이션을 변경할 필요가 없으며 복잡한 쿼리를 단순하게 사용하고자 할 때 사용한다. 보안적인 관점에서는 애플리케이션에 테이블 내의 데이터를 노출하지 말아야 할 때나 전체 테이블의 내용 중에 일부분만을 보여주고자할 때 사용된다.

뷰

2) 뷰 생성, 수정 및 조회

뷰를 조회하는 방법은 일반적인 DML과 차이가 없다. 만약 원본 테이블에 데이터를 추가하거나 수정할 경우, 뷰 테이블에도 동일하게 추가 및 수정이 된다. 반대로 뷰 테이블은 하나 이상의 테이블에서 데이터를 가져와 만들 수 있기 때문에 데이터 입력에 제한이 있다. (하나의 테이블로만 만들어진 뷰는 제약사항에 위배되지 않는 이상 사용이 가능하다.)

// 뷰 생성
mysql> CREATE VIEW [VIEW명] AS SELECT ...;

// 뷰 조회
mysql> SELECT [COLUMN명] FROM [VIEW명];

// 뷰 수정
mysql> ALTER VIEW [VIEW명] AS SELECT ...;

3) 뷰 삭제

뷰를 삭제해도 원본 테이블은 남아있다.

mysql> DROP VIEW [VIEW명];

 

 

 

2. 트랜잭션

1) 트랜잭션

한번에 수행되어야할 최소 작업 단위이다. 모든 작업은 한번에 완료가 되어야하며, 그렇지 않은 경우에는 한번에 취소가 되어야한다. 사용자가 시스템에 요구를 시작하여 시스템 내의 처리, 시스템에서 사용자에게 응답하는 모든 처리를 포함한다. 대표적인 예로 은행의 입금, 출금 과정에서 일어나는 문제에 대한 해결책으로 생각할 수 있다.

2) 트랜잭션 기술적 요건 (ACID)

항목	설명
원자성 (Atomicity)	분리할 수 없는 하나의 단위로 작업은 모두 완료되거나 모두 취소되어야 한다. 트랜잭션의 처리가 취소된 경우 전혀 이루어지지 않은 것과 같아야하며 작업중 에러가 발생할 경우 ROLLBACK이 이루어져야한다. (ALL OR NOTHING)
일관성 (Consistency)	트랜잭션들간의 영향이 한 방향으로만 전달되어야 하며 오류 없이 유효한 데이터만 저장되어야 한다.
고립성 (Isolation)	트랜잭션이 완료되기 전까지 다른 트랜잭션에 영향을 주어선 안된다. 접근하고 있는 데이터는 다른 트랜잭션으로부터 격리되어야 한다.
영구성 (Durability)	트랜잭션이 완료된 경우 다른 시스템 고장이나 네트워크 장애 등으로 데이터가 유실되지 않고 정상적으로 기록되어야 한다. (COMMIT)

3) 트랜잭션 범위

범위	설명
로컬 트랜잭션	단위 시스템에서 처리되는 트랜잭션으로 트랜잭션에 대한 관리를 DBMS에서 담당한다.
분산 트랜잭션	여러 개의 시스템에서 처리되는 트랜잭션이다. 여러 DBMS에 걸친 트랜잭션을 담당하는 경우도 많아서 주로 미들웨어에서 트랜잭션 관리를 담당한다.

4) 트랜잭셔널 데이터베이스

트랜잭션을 지원하는 데이터베이스를 의미하며, 현재 대부분의 관계형 데이터베이스 관리시스템은 트랜잭션 기능을 지원한다. MySQL에서는 InnoDB 엔진만 지원하며 기본적으로 MySQL은 명령어 수행 시 자동으로 Commit 하도록 되어 있다.

5) 트랜잭셔널 파일 시스템

트랜잭션을 지원하는 파일시스템을 의미하며 리눅스의 Namesys Reiser4 파일 시스템과 마이크로소프트 NTFS 새로운 버전은 모두 트랜잭션을 지원한다.

6) 트랜잭션 SQL

트랜잭션에서 사용되는 SQL은 DCL에 포함되지만 따로 TCL (Transaction Control Language)로 분류되기도 한다.

SQL	설명
COMMIT	트랜잭션 작업이 정상적으로 완료되었음을 의미하며 변경 내용을 영구 적용시킨다. 모든 SAVEPOINT를 삭제한다.
SAVEPOINT [SAVEPOINT명]	현재 트랜잭션 작업 시점에 이름을 지정한다. 하나의 트랜잭션 안에서 구역을 나눌 수 있다.
ROLLBACK	트랜잭션 작업을 모두 취소하고 최근 COMMIT 시점으로 이동한다.
ROLLBACK TO [SAVEPOINT명]	지정된 SAVEPOINT까지의 트랜잭션 작업을 취소하고 SAVEPOINT 시점으로 이동한다.

7) 트랜잭션 시작 알림

MySQL은 기본 AUTOCOMMIT 기능이 활성화 되어 있기 때문에 SQL 입력 시 자동 COMMIT이 된다. 임시로 AUTOCOMMIT 사용하지 않도록 설정할 수 있다. 하나의 트랜잭션 작업이 끝나면 설정이 삭제되고, 트랜잭션 작업을 다시 하기 위해서는 새로운 시작을 알려주어야 한다.

mysql> START TRANSACTION;

---

AUTOCOMMIT 기능 비활성화

mysql> SELECT @@AUTOCOMMIT;
mysql> SET AUTOCOMMIT=0;

8) 트랜잭션 LOCK

트랜잭션 작업 중인 데이터를 다른 트랜잭션이 사용하지 못하도록 격리 시킬 수 있다. 사용하던 터미널과 새로운 터미널에서 동일한 데이터를 업데이트했을 경우 일정 시간 대기 하다 LOCK으로 인한 TIMEOUT 메시지가 발생하면서 업데이트 내용이 적용되지 않는다.

9) 트랜잭션 주의 사항

우선, 각 단위 프로그램이 커넥션을 유지하는 시간이 길어질 수록 사용 가능한 여유 커넥션의 개수가 감소되며 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 가능성이 높기 때문에 트랜잭션 범위를 최소화해야한다.

 

두 번째로는 메일 전송, FTP, 원격 서버와 통신과 같은 네트워크를 통한 작업은 프로그램이 실행되는 동안 서버와 통신할 수 없는 상황이 발생할 경우 DBMS까지 위험해지는 상황이 발생할 수 있기 때문에 트랜잭션에서 제외하도록 해야한다.

 

마지막으로 일부 SQL문은 ROLLBACK이 되지 않으며, DDL문을 사용할 때에는 자동으로 COMMIT이 적용이 되기 때문에 ROLLBACK 가능 여부를 확인해주어야 한다.

---

[참고] Database - Practice - 뷰, 트랜잭션