[신입 SQL 교육 자료] GROUP BY-HAVING, ORDER BY, 윈도우함수, 테이블락, 트랜잭션, 동시성제어

7. GROUP BY-HAVING, ORDER BY

7.1. 집계 함수 (Aggregate Function)

7.1.1. 개념

- 여러 행들의 그룹이 모여서 그룹당 단 하나의 결과를 돌려주는 다중행 함수이다.

* 함수를 단일행 값이 입력되는 단일행 함수와 여러 행의 값이 입력되는 다중행 함수로 구분한다.

 

7.1.2. 특징

- GROUP BY 절에 명시한 컬럼의 값이 같은 행들을 소그룹화하고 그룹당 하나의 결과를 반환한다.

- SELECT 절, HAVING 절, ORDER BY 절에 사용한다.

 

7.1.3. 키워드

- ALL : 기본설정으로 생략 가능하다.

- DISTINCT : 같은 값을 하나의 데이터로 간주한다.

 

7.1.4. 종류

- COUNT(*) : NULL 값을 포함한 행의 개수를 출력

- COUNT(표현식) : 표현식 값이 NULL이 아닌 행의 수를 출력

- SUM([DISTINCT | ALL] 표현식) : 표현식의 NULL 값을 제외한 합계를 출력

- AVG([DISTINCT | ALL] 표현식) : 표현식의 NULL 값을 제외한 평균을 출력

- MAX([DISTINCT | ALL] 표현식) : 표현식의 최대값을 출력(숫자, 문자, 날짜 데이터 타입 가능)

- MIN([DISTINCT | ALL] 표현식) : 표현식의 최소값을 출력(숫자, 문자, 날짜 데이터 타입 가능)

 

7.1.5. 예문

SELECT COUNT(*)             "팀별 전체 행수" 

      , COUNT(HEIGHT)        "팀별 키 건수" 

      , MAX(HEIGHT)          "팀별 최대키" 

      , MIN(HEIGHT)          "팀별 최소키" 

      , ROUND(AVG(HEIGHT),2) "팀별 평균키" 

FROM PLAYER

GROUP BY TEAM_ID

 

7.2. GROUP BY 절

7.2.1. 용도

- 테이블에 1차적으로 존재하는 데이터 이외의 가공된 정보를 조회할 때 사용한다. 예를 들어, 팀별 선수들의 수, 팀별 선수들의 평균 신장과 몸무게 등은 테이블에 1차적으로 존재하는 데이터가 아니고 가공해서 조회해야 하는 데이터이다.

 

7.2.2. 형식

SELECT  [DISTINCT] 칼럼명  [ALIAS명]  
	, 집계함수([DISTINCT | ALL] 컬럼명) [ALIAS명]  
FROM 테이블명  
[WHERE 조건식]  
[GROUP BY 칼럼(Column)이나 표현식]  
[HAVING 그룹조건식];

 

7.2.3. 특징

- GROUP BY 절을 통해 소그룹화하고 SELECT 절에서 집계함수를 사용한다.

- GROUP BY 절에서는 ALIAS 사용이 불가하다.

- WHERE 절이 먼저 실행되고 GROUP BY 절을 통해 소그룹화 한다.

- HAVING 절을 통해 GROUP BY 절의 기준 항목이나 집계함수를 이용한 조건을 주어 제한한다.

- 원칙적으로 GROUP BY 절만으로 정렬이 되지는 않는다.

 

7.2.4. 주의사항

- GROUP BY 절 없이 집계함수와 단일 컬럼을 동시에 조회할 수 없다.

- GROUP BY 절이 있어도 GROUP BY 절에 명시한 컬럼만 집계함수와 동시에 조회할 수 있다.

 

7.3. HAVING 절

7.3.1. 용도

- GROUP BY 절로 소그룹화한 로우들 마다 추가적인 조회 조건을 주기 위해 사용한다.

 

7.3.2. 주의사항

- WHERE 절은 GROUP BY 절 이전에 실행되므로 집계 함수를 허용하지 않는다.

- 논리적으로 GROUP BY -> HAVING 순서를 지켜서 작성하기를 권고하나 오라클에서는 HAVING 절을 먼저 적어도 동일한 결과를 출력한다. 단, SQL SERVER 에서는 반드시 순서를 지켜야 한다.

 

7.4. 응용

- 집계함수([CASE문 | DECODE문]) 을 통해 월별 데이터 집계를 수행할 수 있다.

 

7.4.1. CASE문을 활용한 부서별로 월별 급여 평균 조회

SELECT DEPTNO,  
       AVG(CASE MONTH WHEN   1 THEN SAL END) M01, 
	... 생략 
       AVG(CASE MONTH WHEN 12 THEN SAL END) M12  
 FROM 
    (
      SELECT ENAME  
           , DEPTNO  
           , EXTRACT(MONTH FROM HIREDATE) MONTH -- 날짜형 데이터에서 월만 조회
           , SAL  
        FROM EMP  
    )  
GROUP BY DEPTNO

 

7.4.2. DECODE문을 활용한 부서별로 월별 급여 평균 조회

SELECT DEPTNO,  
       AVG(DECODE( MONTH ,   1 , SAL )) M01, 
	... 생략
       AVG(DECODE( MONTH , 12 , SAL )) M12  
 FROM
    (  
      SELECT DEPTNO  
           , EXTRACT(MONTH FROM HIREDATE) MONTH -- 날짜형 데이터에서 월만 조회
           , SAL  
        FROM EMP  
    )  
GROUP BY DEPTNO

 

7.5. 집계 함수와 NULL

- 다중 행 함수는 입력 값으로 전체 건수가 NULL 값인 경우만 함수의 결과가 NULL이 나오고 전체 건수 중에서 일부만 NULL인 경우는 NULL인 행을 다중 행 함수의 대상에서 제외한다.

- NULL 대신 0을 지정하면 불필요한 연산이 추가되어 자원의 사용이 많아진다.

SELECT TEAM_ID  
      , NVL(SUM(CASE POSITION WHEN 'FW' THEN 1 ELSE 0 END),0) FW -- 불필요 연산 발생
        -- SUM은 NULL을 제외 하고 합산 하므로 SUM안이 아닌 밖에 NVL를 사용 하여 건수들 만큼 자원의 낭비를 줄임  
      , NVL(SUM(CASE POSITION WHEN 'MF' THEN 1 END),0) MF 
      , COUNT(*)   SUM  
   FROM PLAYER  
  GROUP BY TEAM_ID;

 

7.6. ORDER BY 정렬

7.6.1. 용도

- SQL문장으로 조회된 데이터들을 다양한 목적에 맞게 특정 컬럼을 기준으로 정렬하여 출력한다.

 

7.6.2. 특징

- ORDER BY 절에는 컬럼명, SELECT 절에서 사용한 ALIAS 명 또는 컬럼 순서를 사용한다.

- 기본적으로 오름차순 정렬한다. (ASC : 오름차순(DEFAULT), DESC : 내림차순)

- ORDER BY 절은 SQL문장의 제일 마지막에 위치한다.

- 오라클에서는 NULL값이 맨 마지막(가장 큰 값)이고 SQL SERVER에서는 NULL 값이 맨 앞(가장 작은 값)이다.

 

7.7. ORDER BY절의 SELECT문장에서 실행 순서

7.7.1. 실행 순서

5. SELECT 칼럼명  [ALIAS명]   
1. FROM 테이블명   
2. WHERE 조건식   
3. GROUP BY 칼럼(Column)이나 표현식   
4. HAVING 그룹조건식   
6. ORDER BY 칼럼(Column)이나 표현식;

- 1. 발췌 대상 테이블을 조회한다. (FROM)

- 2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)

- 3. 행들을 소그룹화 한다. (GROUP BY)

- 4. 그룹핑된 값의 조건에 맞는 것들만 출력한다. (HAVING)

- 5. 데이터의 값을 출력하고 계산한다. (SELECT)

- 6. 데이터를 정렬한다. (ORDER BY)

 

7.7.2. 주의사항

- GROUP BY절을 사용하는 경우에는 FROM절의 테이블 구조를 그대로 가지고 가는 것이 아니라 그룹핑 기준에 사용된 컬럼과 집계 함수에 사용될 숫자형 데이터 컬럼들의 집합을 새로 만든다. 그리고 개별 데이터는 필요 없으므로 저장하지 않는다. 그래서 GROUP BY 이후 수행되는 SELECT 절이나 ORDER BY 절에 개별 데이터를 사용하는 경우 에러가 발생할 수 있다. 한편, SELECT 절에 사용 가능한 집계함수를 ORDER BY 절에서도 사용할 수 있다.

 

7.8. ROWNUM과 TOP N 쿼리 비교

7.8.1. TOP N 쿼리의 용도

- ROWNUM을 사용하는 경우와 달리 정렬 작업 후에 데이터를 추출한다.

* 오라클에서는 TOP N 쿼리 사용불가 

 

7.8.2. 비교

7.8.2.1. 4건을 추출한 후에 정렬 작업이 일어나는 경우

SELECT ENAME   
     , SAL   
  FROM EMP   
 WHERE ROWNUM < 4   
 ORDER BY SAL DESC;

 

7.8.2.2. 4건을 추출하되 정렬 작업 후에 추출하는 경우

SELECT ENAME   
     , SAL   
  FROM
     (   
       SELECT ENAME   
            , SAL   
         FROM EMP   
        ORDER BY SAL DESC   
     )   
 WHERE ROWNUM < 4

 

7.8.2.3. 데이터를 정렬한 후에 4건을 추출하는 경우

SELECT TOP(4), ENAME
      , SAL
   FROM EMP
 WHERE ROWNUM < 4
 ORDER BY SAL DESC

 

7.8.3. ROWNUM 과 TOP N 쿼리 비교 결론

- ORDER BY 정렬이 없다면 ROWNUM과 TOP()의 기능은 같다.

- 정렬 작업이 일어나면 SELECT 문장 실행 순서에 따라 다른 결과를 출력할 수 있다.

 

8. WINDOW 함수

8.1. 개념

- 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 WINDOW FUNCTION이다.

- 분석 함수 또는 순위 함수라고도 알려져 있다.

- WINDOW 함수는 다른 함수와 달리 중첩해서 사용하지 못한다.

- WINDOW 함수는 SUBQUERY에서 사용할 수 있다.

 

8.2. SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 컬럼]

[ORDER BY 절]

[WINDOWING 절] )

FROM 테이블명 …

 

8.2.1. WINDOW_FUNCTION (ARGUMENTS) – 기존 함수와 추가된 함수가 있다. 함수에 따라 0-N개의 인자를 지정할 수 있다.

8.2.2. PARTITION BY – 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

8.2.3. ORDER BY – 어떤 항목에 대해 순위를 지정할 지 기술한다.

8.2.4. WINDOWING 절 – 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다. (SQL SERVER는 WINDOWING 절을 지원하지 않는다.)

* ROWS와 RANGE의 차이 – ORDER BY으로 정렬된 값이 동일한 경우(정렬 순위가 같은 경우) ROWS는 각 ROW마다 계산하고 RANGE는 함께 계산한다.

 

8.2.4.1. BETWEEN 사용 타입

ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING

8.2.4.2. BETWEEN 미사용 타입

ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING

 

8.2.4.3. 키워드 설명

8.2.4.3.1. RANGE UNBOUNDED PRECEDING – 현재 행을 기준으로 맨 앞까지가 윈도우 범위

= RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = ORDER BY를 주었을 때 윈도우 절이 없으면 기본값

8.2.4.3.2. RANGE CURRENT ROW – 현재 행만이 윈도우 범위

8.2.4.3.3. RANGE 2 PRECEDING – 현재 행을 기준으로 2행 앞까지가 윈도우 범위

8.2.4.3.4. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING – 파티션별로 처음 행부터 마지막 행까지가 윈도우 범위

8.2.4.3.5. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING – 현재 행을 기준으로 앞뒤 1건 까지가 윈도우 범위

8.2.4.3.6. RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING – 현재 행부터 마지막 행까지가 윈도우 범위

 

8.3. 종류

8.3.1. 그룹 내 순위 함수

8.3.1.1. RANK 함수

8.3.1.1.1. 개념

 - ORDER BY를 포함한 쿼리문에서 특정 항목(컬럼)에 대한 순위를 구한다.

- 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 범위에서 구할 수도 있다.

- 동일한 값에 대해 동일한 순위를 부여하고 중복된 순위만큼 다음 순위에 반영된다. EX) 1, 2, 2, 4, 4, 4, 7, …

 

8.3.1.1.2. 예문

SELECT JOB, ENAME, SAL,
       RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK, 
       RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
  FROM EMP;
JOB       ENAME             SAL   ALL_RANK   JOB_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING             5000          1          1
ANALYST   FORD             3000          2          1
ANALYST   SCOTT            3000          2          1
MANAGER   JONES            2975          4          1
MANAGER   BLAKE            2850          5          2

 

8.3.1.2. DENSE_RANK 함수

8.3.1.2.1. 개념

- RANK함수와 같이 동일한 값에 대해 동일한 순위를 부여하지만 중복된 순위만큼 다음 순위에 반영되지 않는다. EX) 1, 2, 2, 3, 3, 3, 4, …

 

8.3.1.2.2. 예문

SELECT JOB, ENAME, SAL
     , RANK( ) OVER (ORDER BY SAL DESC) RANK
     , DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK
  FROM EMP; 
JOB       ENAME             SAL        RANK    DENSE_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING             5000          1          1
ANALYST   FORD             3000          2          2
ANALYST   SCOTT            3000          2          2
MANAGER   JONES            2975          4          3
MANAGER   BLAKE            2850          5          4

 

8.3.1.3. ROW_NUMBER 함수

8.3.1.3.1. 개념

- RANK, DENSE_RANK와 달리 동일한 값이라도 고유한 순위를 부여한다. EX) 1, 2, 3, 4, 5, 6, 7, …

 

8.3.1.3.2. 예문

SELECT JOB, ENAME, SAL 
     , RANK( ) OVER (ORDER BY SAL DESC) RANK
     , ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
  FROM EMP; 
JOB       ENAME             SAL       RANK ROW_NUMBER
--------- ---------- ---------- ---------- ----------
PRESIDENT KING             5000          1          1
ANALYST   FORD             3000          2          2
ANALYST   SCOTT            3000          2          3
MANAGER   JONES            2975          4          4
MANAGER   BLAKE            2850          5          5

 

8.3.2. 일반 집계 함수

8.3.2.1. SUM 함수

8.3.2.1.1. 용도

- 파티션별 윈도우의 합을 구한다.

 

8.3.2.1.2. 예문

SELECT MGR, ENAME, SAL
     , SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM 
  FROM EMP;
MGR  ENAME             SAL     MGR_SUM
---------- ---------- ---------- ----------
      7566 SCOTT            3000       6000
      7566 FORD             3000       6000
      7698 JAMES             950        950
      7698 WARD             1250       3450
      7698 MARTIN           1250       3450
      7698 TURNER           1500       4950
      7698 ALLEN            1600       6550

 

8.3.2.2. MAX 함수

- 파티션별 윈도우의 최대값을 구한다.

 

8.3.2.3. MIN 함수

- 파티션별 윈도우의 최소값을 구한다.

 

8.3.2.4. AVG 함수

- 파티션별 윈도우의 평균값을 구한다.

 

8.3.2.5. COUNT 함수

- 파티션별 윈도우의 카운트 값을 구한다.

 

8.3.3. 그룹 내 행 순서 함수

8.3.3.1. FIRST_VALUE 함수

- 파티션별 윈도우에서 가장 먼저 나온 값을 구한다.

SELECT DEPTNO, ENAME, SAL
     , FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP
  FROM EMP; 
DEPTNO     ENAME          SAL   RICH_EMP
---------- ---------- ---------- ----------
        10  KING           5000    KING
        10  CLARK          2450    KING
        10  MILLER         1300    KING
        20  FORD           3000    FORD
        20  SCOTT          3000    FORD

 

8.3.3.2. LAST_VALUE 함수

- 파티션별 윈도우에서 가장 마지막에 나온 값을 구한다.

SELECT DEPTNO, ENAME, SAL
     , LAST_VALUE(ENAME) OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR 
  FROM EMP;
DEPTNO     ENAME             SAL DEPT_POOR
---------- ---------- ---------- ----------
        10 KING             5000 MILLER
        10 CLARK            2450 MILLER
        10 MILLER           1300 MILLER
        20 SCOTT            3000 SMITH
        20 FORD             3000 SMITH

 

8.3.3.3. LAG 함수

- 파티션별 윈도우에서 이전 N번째 행의 값을 구한다. N은 기본 1이다.

- LAG(컬럼, N, NULL인 경우 대체할 값)

SELECT ENAME, HIREDATE, SAL
     , LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL 
  FROM EMP 
 WHERE JOB = 'SALESMAN';
ENAME      HIREDATE         SAL   PREV_SAL
---------- --------- ---------- ----------
ALLEN      20-FEB-81       1600          0
WARD       22-FEB-81       1250          0
TURNER     08-SEP-81       1500       1600
MARTIN     28-SEP-81       1250       1250

 

8.3.3.4. LEAD 함수

- 파티션별 윈도우에서 이후 N번째 행의 값을 구한다. N은 기본 1이다.

- LEAD(컬럼, N, NULL인 경우 대체할 값)

SELECT ENAME, HIREDATE
     , LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED" 
  FROM EMP;
ENAME      HIREDATE  NEXTHIRED
---------- --------- ---------
SMITH      17-DEC-80  20-FEB-81
ALLEN      20-FEB-81  22-FEB-81
WARD       22-FEB-81  02-APR-81
JONES      02-APR-81  01-MAY-81
BLAKE      01-MAY-81  09-JUN-81

 

8.3.4. 그룹 내 비율 함수

8.3.4.1. RATIO_TO_REPORT 함수

- 파티션 내 전체 SUM(컬럼) 값에 대한 행별 칼럼 값의 백분율을 구한다.

- 결과 값은 0초과 1이하의 값이다.

- 개별 RATIO의 합을 구하면 1이다.

SELECT ENAME, SAL
     , ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R 
  FROM EMP 
 WHERE JOB = 'SALESMAN'; 
ENAME             SAL        R_R
---------- ---------- ----------
ALLEN            1600        .29
WARD             1250        .22
MARTIN           1250        .22
TURNER           1500        .27

 

8.3.4.2. PERCENT_RANK 함수

- 파티션별 윈도우에서 제일 먼저 나오는 것을 0, 제일 나중에 나오는 것을 1로 하여 행의 순서별 백분율을 구한다.

- 결과 값은 0이상 1이하의 값이다.

SELECT DEPTNO, ENAME, SAL
     , PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R 
  FROM EMP; 
DEPTNO ENAME             SAL        P_R
---------- ---------- ---------- ----------
        10 KING             5000          0
        10 CLARK            2450         .5
        10 MILLER           1300          1
        20 SCOTT            3000          0
        20 FORD             3000          0
        20 JONES            2975         .5
        20 ADAMS            1100        .75
        20 SMITH             800          1

 

8.3.4.3. CUME_DIST 함수

- 파티션별 윈도우의 전체건수에서 현재 행보다 정렬순위가 작거나 같은 건수에 대한 누적백분율을 구한다.

- 결과 값은 0초과 1이하의 값이다.

SELECT DEPTNO, ENAME, SAL 
     , CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST 
  FROM EMP; 
DEPTNO      ENAME      SAL         CUME_DIST
---------- ---------- ---------- ----------
        10 KING             5000 .333333333
        10 CLARK            2450 .666666667
        10 MILLER           1300          1
        20 SCOTT            3000         .4
        20 FORD             3000         .4
        20 JONES            2975         .6
        20 ADAMS            1100         .8
        20 SMITH             800          1

 

8.3.4.4. NTILE 함수

- 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구한다.

SELECT ENAME, SAL
     , NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE 
  FROM EMP ;
ENAME             SAL  QUAR_TILE
---------- ---------- ----------
KING             5000          1
FORD             3000          1
CLARK            2450          2
ALLEN            1600          2
MILLER           1300          3
WARD             1250          3
ADAMS            1100          4
JAMES             950          4

 

9. 테이블 LOCK, 트랜잭션, 동시성 제어

9.1. LOCK 개념

- LOCK이란 같은 자원을 액세스하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화 장치이다.

 

9.2. 공유 LOCK과 배타적 LOCK

9.2.1. 공유 LOCK

- 데이터를 읽고자 할 때 사용한다.

- 다른 공유 LOCK과는 호환되지만 배타적 LOCK과는 호환되지 않는다.

 

9.2.2. 배타적 LOCK

- 데이터를 변경하고자 할 때 사용되며, 트랜잭션이 완료될 때까지 유지된다.

- 해당 LOCK이 해제될 때까지 다른 트랜잭션은 해당 자원에 접근할 수 없다.

 

9.3. 블로킹과 교착상태

9.3.1. 블로킹

9.3.1.1. 개념

- LOCK 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태이다.

 

9.3.1.2. 발생 원인

- 공유 LOCK과 배타적 LOCK은 함께 설정될 수 없으므로 블로킹이 발생한다.

 

9.3.1.3. 해소 방법

- 블로킹을 해소할 수 있는 방법은 COMMIT 또는 ROLLBACK 뿐이다.

- LOCK 경합이 발생하면 먼저 LOCK이 종료될 때까지 후행 트랜잭션을 기다려야 한다.

 

9.3.1.4. LOCK에 의한 성능 저하 최소화하기

- 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션을 가능한 짧게 정의한다.

- 오라클은 데이터를 읽을 때 공유 LOCK을 사용하지 않기 때문에 상대적으로 LOCK 경합이 적다.

- 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계한다.

- 주간에 대용량 갱신 작업이 불가피하다면, 블로킹 현상에 의해 사용자가 무한정 기다리지 않도록 적절한 프로그램 기법을 도입해야 한다.

 

9.3.1.5. 주의 사항

- 트랜잭션 격리성 수준을 불필요하게 상향 조정하지 않는다. (트랜잭션 격리 수준이 높아질수록 각각의 트랜잭션을 확실히 처리할 수 있지만 동시 작업이 줄어서 속도가 느려질 수 있다.)

- SQL 문이 가능한 빠르게 처리되도록 하는 것이 LOCK 튜닝의 기본이고 가장 효과적이다.

 

9.3.2. 교착상태

- 두 세션이 각각 LOCK을 설정한 자원을 서로 액세스하려고 마주보며 진행하는 상황이다.

- 두 세션이 서로에게 블로킹하고 있는 상태이다.

- 두 세션 중 하나가 뒤로 물러가지 않으면 영영 풀릴 수 없다.

 

 

9.4. ORACLE LOCK

9.4.1. 개요

- 오라클은 공유 자원과 사용자 데이터를 보호할 목적으로 DML LOCK, DDL LOCK, 래치, 버퍼 LOCK, 라이브러리 캐시 LOCK/PIN 등 다양한 종류의 LOCK을 사용한다.

 

9.4.2. ROW LOCK

- 오라클에서 ROW LOCK은 항상 배타적이다.

- INSERT, UPDATE, DELETE 문을 수행한 트랜잭션에 의해 ROW LOCK이 설정되면, 트랜잭션이 COMMIT 또는 ROLLBACK 할 때까지 다른 트랜잭션은 해당 ROW를 변경할 수 없다.

- 오라클에서는 읽는 과정에서는 어떤 LOCK도 설정하지 않음으로써 읽기와 갱신 작업은 서로 방해하지 않는다.

 

9.4.3. TABLE LOCK

- 한 트랜잭션이 ROW LOCK을 얻는 순간, 해당 테이블에 대한 TABLE LOCK도 동시에 얻어 현재 트랜잭션이 갱신 중인 테이블에 대한 호환되지 않는 DDL 수행을 방지한다.

- 후행 트랜잭션은 해당 테이블 LOCK이 설정되어 있는지 확인하고 해당 테이블로의 진입 여부를 결정한다.

 

9.5. 트랜잭션(TRANSACTION)

9.5.1. 개념 - 트랜잭션이란 업무 처리를 위한 논리적인 작업 단위이다.

 

9.5.2. 특징

9.5.2.1. 원자성(ATOMICITY) : 트랜잭션은 더 이상 분해가 불가능한 업무의 최소 단위이므로, 전부 처리되거나 아예 하나도 처리되지 않아야 한다.

9.5.2.2. 일관성(CONSISTENCY) : 일관된 상태의 데이터베이스에서 하나의 트랜잭션을 성공적으로 완료하고 나면, 그 데이터베이스는 여전히 일관된 상태여야 한다.

9.5.2.3. 격리성(ISOLATION) : 실행 중인 트랜잭션의 중간 결과를 다른 트랜잭션이 접근할 수 없다.

9.5.2.4. 영속성(DURABILITY) : 트랜잭션이 일단 실행을 성공적으로 완료하면 그 결과는 데이터베이스에 영속적으로 저장된다.

 

9.5.3. 트랜잭션 격리성

9.5.3.1. 낮은 단계의 격리성 수준에서 발생할 수 있는 현상들

9.5.3.1.1. DIRTY READ

- 다른 트랜잭션에 의해 수정되었지만 아직 COMMIT되지는 않은 데이터를 읽는 것을 의미한다.

- 변경 후에 아직 COMMIT 되지 않은 값을 읽었는데 최종적으로는 ROLLBACK 된다면 그 값을 읽은 트랜잭션은 비일관된 상태에 놓이게 된다.

 

9.5.3.1.2. NON-REPETABLE READ

- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상이다.

 

9.5.3.1.3. PHANTOM READ

- 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서는 나타나는 현상이다.

 

9.5.3.2. 트랜잭션 격리성 수준

9.5.3.2.1. READ UNCOMMITTED

- 트랜잭션에서 처리 중인 COMMIT되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다.

 

9.5.3.2.2. READ COMMITTED

- 트랜잭션이 COMMIT되어 확정된 데이터만 다른 트랜잭션이 읽도록 허용한다.

- 값을 읽는 순간 공유 LOCK을 걸었다가 다음 레코드로 이동할 때 LOCK을 해제한다.

- COMMIT된 데이터만 읽더라도 NON-REPEATABLE READ와 PHANTOM READ 현상을 막지는 못한다.

 

9.5.3.2.3. REPEATABLE READ

- 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지해 준다. PHANTOM READ 현상을 막지는 못한다.

 

9.5.3.2.4. SERIALIZABLE READ

- 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론이고 새로운 레코드가 나타나지도 않는다.

 

9.5.3.2.5. 트랜잭션 격리성 수준 정리

레벨	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	가능	가능	가능
Read Committed	불가능	가능	가능
Repeatable Read	불가능	불가능	가능
Serializable Read	불가능	불가능	불가능

- 오라클에서는 READ COMMITTED와 SERIALIZABLE READ만 지원한다.

- 대부분의 DBMS는 READ COMMITTED를 기본 트랜잭션 격리성 수준으로 사용한다.

- 트랜잭션 격리성 수준이 높아질수록 동시성이 떨어진다. 이것을 보완하기 위한 것으로 다중 버전 동시성 제어(=스냅샷 격리성 수준)를 사용할 수 있다.

 

9.6 동시성 제어(CONCURRENCY CONTROL)

9.6.1. 개요

- DBMS는 다수의 사용자를 가정하며, 동시에 작동하는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이스를 보호할 수 있어야 하며, 이를 동시성 제어라고 한다.

- 동시성 제어를 위해 모든 DBMS가 제공하는 것이 LOCK 기능이다.

- SET TRANSACTION 명령어를 통해 트랜잭션 격리성 수준을 조정할 수 있다.

- 동시성이 보장될수록 일관성은 저해될 수 있다.

 

9.6.2. 비관적 동시성 제어와 낙관적 동시성 제어

9.6.2.1. 비관적 동시성 제어

- 사용자들이 같은 데이터를 동시에 수정할 것이라고 가정한다.

- 데이터를 읽는 시점에 LOCK을 걸고 트랜잭션이 완료될 때까지 이를 유지한다.

- SELECT 시점에 LOCK을 거는 비관적 동시성 제어는 시스템 동시성을 크게 떨어뜨릴 수 있다.

- 아래와 같이 WAIT 또는 NOWAIT 옵션을 함께 사용하는 것이 바람직하다.

[SELECT문] for update nowait --> 대기 없이 Exception을 던짐
[SELECT문] for update wait 3 --> 3초 대기 후 Exception을 던짐

 

9.6.2.2. 낙관적 동시성 제어

- 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정한다.

- 이런 이유로 데이터를 읽을 때는 LOCK을 설정하지 않는다.

- 대신 수정 시점에 다른 사용자에 의해 값이 변경되었는지 여부를 반드시 검사한다.

 

9.6.3. 다중버전 동시성 제어(Multiversion Concurrency Control)

9.6.3.1. 일반적인 LOCKING 매커니즘의 문제점

- 동시성 제어의 목표는 동시에 실행되는 트랜잭션 수를 최대화하면서도 입력, 수정, 삭제, 검색 시 데이터 무결성을 유지하는 것이다.

- 읽기 작업에 공유 LOCK을 사용하는 일반적인 LOCKING 매커니즘에서는 읽기 작업과 쓰기 작업이 서로 방해를 일으키기 때문에 종종 동시성에 문제가 발생한다.

- 데이터 일관성에 문제가 생기는 경우도 있어 이를 해결하려면 LOCK을 더 오랫동안 유지하거나 테이블 레벨 LOCK을 사용해야 하므로 동시성 저하가 발생한다.

 

9.6.3.2. 다중버전 동시성 제어 동작방식

- 데이터를 변경할 때마다 그 변경사항을 UNDO 영역에 저장한다.

- 데이터를 읽다가 쿼리(또는 트랜잭션) 시작 시점 이후에 변경된(변경이 진행 중이거나 이미 커밋된)값을 발견하면 UNDO 영역에 저장된 정보를 이용해 쿼리(또는 트랜잭션) 시작 시점의 일관성 있는 버전을 생성하고 읽는다.

- 쿼리 도중에 배타적 LOCK이 걸린, 즉 변경이 진행 중인 레코드를 만나도 대기하지 않기 때문에 동시성 측면에서 유리하다.

- 문장수준과 트랜잭션 수준의 읽기 일관성이 존재한다.

 

9.6.3.3. 문장수준 읽기 일관성

- 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것을 말한다.

- 일관성 기준 시점은 쿼리 시작 시점이 된다.

- 시작된 쿼리가 시작 시점 이후에 변경된 데이터 블록을 만났을 때, ROLLBACK(=UNDO) 세그먼트에 저장된 정보를 이용해 변경 이전 시점으로 되돌리고 나서 값을 읽는다.

- SQL SERVER에서 아래 명령어를 수행하면 문장수준 읽기 일관성 모드로 DB를 운영할 수 있다.

alter database <데이터베이스 이름> set read_committed_snapshot on;

 

9.6.3.4. 트랜잭션 수준 읽기 일관성

- 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것을 말한다.

- READ COMMITTED에서 완벽한 문장수준의 읽기 일관성을 보장하는 MVCC(Multiversion Concurrency Control) 매커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다.

- 일반적인 LOCKING 매커니즘도 트랜잭션 수준의 읽기 일관성은 보장하지 않는다.

- 트랜잭션 수준으로 완벽한 읽기 일관성을 보장받으려면 격리성 수준을 SERIALIZABLE READ으로 올려주어야 한다.

- ISOLATION LEVEL을 SERIALIZABLE READ으로 상향조정하면, 일관성 기준 시점은 트랜잭션 시작 시점이 된다. 트랜잭션이 진행되는 동안 자신이 발생시킨 변경사항은 그대로 읽는다.

 

 

 

참고출처

“SQL 전문가 가이드 (2013년) – 과목2. 1장. 제7절. GROUP BY, HAVING 절”, www.gurubee.net, 2013년 3월 18일 수정, 2023년 5월 30일 접속, http://www.gurubee.net/lecture/2373.

“SQL 전문가 가이드 (2013년) – 과목2. 1장. 제8절. ORDER BY 절”, www.gurubee.net, 2013년 3월 22일 수정, 2023년 5월 130 접속, http://www.gurubee.net/lecture/2374.

“SQL 전문가 가이드 (2013년) – 과목2. 2장. 제6절. 윈도우 함수”, www.gurubee.net, 2023년 3월 24일 수정, 2023년 5월 30일 접속, http://www.gurubee.net/lecture/2382.

“SQL 전문가 가이드 (2013년) – 과목3. 2장. 제1절. LOCK”, www.gurubee.net, 2013년 4월 14일 수정, 2023년 5월 30일 접속, http://www.gurubee.net/lecture/2396.

“SQL 전문가 가이드 (2013년) – 과목3. 2장. 제2절. 트랜잭션”, www.gurubee.net, 2013년 4월 14일 수정, 2023년 5월 30일 접속, http://www.gurubee.net/lecture/2397.

“SQL 전문가 가이드 (2013년) – 과목3. 2장. 제3절. 동시성 제어”, www.gurubee.net, 2013년 4월 14일 수정, 2023년 5월 30일 접속, http://www.gurubee.net/lecture/2398.