[ SQL ] Transaction and Sub Query

이본 포스팅은 트랜잭션의 기본 개념과 실무 SQL에서 정말 많이 사용하는 서브쿼리에 대해서 포스팅해보려 합니다.

 

■ 트랜잭션(Transaction)

 

트랜잭션이란 '거래'라는 뜻으로 데이터베이스 내에서 하나의 그룹으로 처리되어야 하는 명령문들을 모아 놓은 논리적인 작업 단위입니다. 데이터베이스 응용프로그램은 트랜잭션들의 집합으로 정의 할 수 있는데, 여러 단계의 처리를 하나의 처리처럼 다루는 기능입니다.

 

트랜잭션의 정의는 다음과 같습니다.

 

• 여려 개의 명령어의 집합이 정상적을도 처리되면 정상 종료됩니다.
• 하나의 명령어라도 잘못되면 전체 취소 됩니다,
• 트랜잭션을 쓰는 이유는 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구하기 위함입니다.

 

트랜잭션이 필요한 이유는 안전성을 확보하는 방법으로 트랜잭션이 있는데, 데이터베이스에선 테이블에서 데이터를 읽어 온 후 다른 테이블에 데이터를 입력하거나 갱신, 삭제하는데 처리 도중 오류가 발생하면 모든 작업을 원상태로 되돌립니다. 즉, 데이터베이스에선 처리 과정이 모두 성공했을 때만 최종적으로 데이터베이스에 반영하기 때문에 그 안전성을 확보하는 방법이 트랜잭션입니다.

 

■ 트랜잭션 특징

 

• 원자성(Atomicity)

트랜잭션이 DB에 모두 반영되거나, 혹은 전혀 반영되지 않아야 됩니다.

• 일관성(Consistency)

트랜잭션의 작업 처리 결과는 항상 일관성 있어야 합니다.

• 독립성(Isolation)

둘 이상의 트랜잭션이 동시에 병행 실행되고 있을 때, 어떤 트랜잭션도 다른 트랜잭션 연산에 끼어들 수 없습니다.

• 지속성(Durability)

트랜잭션이 성공적으로 완료되었으면, 결과는 영구적으로 반영되어야 합니다.

 

■ TCL(Transaction control language)

TCL은 트랜잭션 제어어로 DML문이 실행되어 DB에 저장되거나(commit) 되돌리기(rollback) 위해서 실행해야 하는 SQL문입니다.

 

• COMMIT

COMMIT은 저장을 의미하며 모든 작업들을 정상적으로 처리하겠다고 확정하는 명령어로 처리과정을 DB에 영구저장 합니다. 또한 COMMIT을 수행하면 하나의 트랜잭션 과정을 종료하는 것이고, 이전 데이터가 완전히 UPDATE 됩니다. 따라서 DB가 일관성 있는 상태일 때 이를 알려주기 위해 사용하는 연산입니다.

 

• Rollback

Rollback은 작업 중 문제가 발생되어 트랜잭션의 처리과정에서 발생한 변경사항을 취소하는 명령어입니다. 즉, 하나의 트랜잭션 처리가 비정상적으로 종료되어 트랜잭션 원자성이 깨진 경우에 사용합니다.

 

• SavePoint

SavePoint는 임시저장을 의미합니다, Rollback을 명시하면 insert, delete, update 등 작업 전제가 취소되는데 SavePoint는 전체가 아닌 특정 부분에서 트랜잭션을 취소시킬 수 있습니다. 또한 SavePoint를 쓰면 현재의 트랜잭션을 작게 분할할 수 있고, 여려 개의 SQL문의 실행을 수행하는 트랜잭션의 경우에 사용자가 트랜잭션 중간 단계에서 SavePoint를 지정할 수 있습니다.

 

■ Sub Query

서브 쿼리란 위치에 따라 Select절, From절, Where절로 나뉘는데, 하나의 SQL문장 내부에 존재하는 또 다른 Select문입니다. 가장 일반적인 것은 Where절이며, 실무에선 From절과 Where절을 가장 많이 사용합니다.

 

그럼 각 절마다 특성에 대해서 알아봅시다.

(예제 코드는 이전 포스팅에서 부터 활용한 테이블을 활용합니다.)

 

1 ) Select절(일반 서브쿼리)

 

select절에 사용되는 서브쿼리는 하나의 열처럼 사용됩니다.

 

select절 예제로는 사원마다 각각의 부서번호가 반복적으로 진행됨으로 특정 부서번호의 데이터 출력을 가져옵니다.

select e.ename, e.deptno,(select d.dname  from dept d   where deptno = 20) as dname --컬럼역할하는 서브쿼리
from emp e;

 

※Select절 서브쿼리는 데이터의 양이 많을수록 실행속도가 느려지기 때문에 거의 사용되지 않는 명령어입니다.

 

2 ) From절(inline view)

 

From절에 사용되는 서브쿼리는 하나의 테이블처럼 사용됩니다.

 

※ 테이블처럼 사용되므로, 열 이름과 테이블명을 꼭 명시해줘야 합니다.

 

From절 예제로는 사원들의 평균 급여보다는 높고 최대 급여보다는 낮은 급여를 받는 사원들을 조회하는 쿼리입니다.

--사원들의 평균 급여보다는 높고 최대 급여보다는 낮은 급여를 받는 사원들을 조회
select e.empno,e.ename,e.sal
from emp e, (select avg(sal) avgs, max(sal) maxs from emp) e2 -- 인라인 뷰(view)
where e.sal > e2.avgs and e.sal < e2.maxs
order by e.sal desc;

 

3 ) Where절(중첩쿼리)

 

Where절은 Select절과 동일한 서브쿼리에서 일반 서브쿼리입니다. 왜냐면 가장 대표적인 형태이기 때문이고, 활용성이 높기 때문입니다. 또한 중첩 서브쿼리로 활용할 수도 있습니다.

 

그럼 예제를 통해서 일반쿼리와 중첩쿼리에 대해서 알아봅시다.

 

먼저 중첩이 아닌 일반 서브쿼리에 Where절 예제로는 From절과 동일하게 사원들의 평균 급여보다 높고 최대 급여보다는 낮은 급여를 받는 사원들을 조회하는 쿼리입니다.

 

<일반쿼리>

select e.empno, e.ename, e.sal
from emp e
where e.sal > (select avg(sal) avgs from emp) and e.sal < (select max(sal) maxs from emp)
order by e.sal desc;

 

중첩쿼리는 부서 평균 급여보다 적은 사원들만 조회하는 쿼리입니다.

 

<중첩쿼리>

  select ename, sal, deptno, (select avg(sal) from emp where deptno = e.deptno) 부서평균급여
 from emp e
 where sal < (select avg(sal) from emp where deptno=e.deptno)
 order by deptno;

 

---

마치며

 

오늘은 트랜잭션과 서브쿼리에 대해서 알아봤습니다.

다음 포스팅으로는 테이블과 제약조건으로 찾아뵙겠습니다.