개발자의 메모장

- 반복적으로 작동되는 쿼리

- 자료들의 계층을 파악하는데 용이하다.

구조

WITH RECURSIVE cte AS (
    -- NON-RECURSIVE 문, 처음 한 번 실행됨 (Anchor Member)
    SELECT 1 AS n
    -- 반드시 UNION 사용해야 함
    UNION ALL
    -- RECURSIVE 문 (Recursive Member)
    -- 서브 쿼리에서 가상 테이블 참조하는 문장 필요
    SELECT n + 1
    FROM cte
    -- Termination Condition (종료 조건)
    WHERE n < 10
)
SELECT * FROM cte

예시

2011년 ~ 2020년 출력하기

WITH RECURSIVE cte2 AS (
	SELECT 2011 AS yyyy
	UNION ALL
	SELECT yyyy + 1 FROM cte2
	WHERE yyyy < 2020
)
SELECT yyyy FROM cte2

2020년 1월 1일 ~ 2020년 2월 28일 출력하기

WITH RECURSIVE cte3 AS (
	SELECT DATE_FORMAT('2020-01-01', '%Y-%m-%d') AS dt FROM DUAL
	UNION ALL
	SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM cte3
	WHERE dt < '2020-02-28'
)
SELECT * FROM cte3

예시 문제

https://school.programmers.co.kr/learn/courses/30/lessons/59413

풀이 1

WITH RECURSIVE cte AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM cte
    WHERE HOUR < 23
), tmp AS (
    SELECT 
        CAST(DATE_FORMAT(DATETIME, '%H') AS UNSIGNED) AS HOUR
        , COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY HOUR
)

SELECT
    cte.HOUR
    , IFNULL(tmp.COUNT, 0) AS COUNT
FROM cte
LEFT JOIN tmp ON tmp.HOUR = cte.HOUR

풀이 2

WITH RECURSIVE cte AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM cte
    WHERE HOUR < 23
)

SELECT
    cte.HOUR
    , COUNT(DATETIME) AS COUNT
FROM cte
LEFT JOIN ANIMAL_OUTS AS ao ON cte.HOUR = HOUR(ao.DATETIME)
GROUP BY cte.HOUR

https://school.programmers.co.kr/learn/courses/30/lessons/301650

풀이

WITH RECURSIVE TMP AS (
    SELECT ID
        , PARENT_ID
        , 1 AS GEN
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT
        e.ID
        , e.PARENT_ID
        , (t.GEN + 1) AS GEN
    FROM ECOLI_DATA e
    INNER JOIN TMP t ON e.parent_id = t.id
)
SELECT id
FROM TMP
WHERE GEN = 3

출처

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

https://allmana.tistory.com/144

https://velog.io/@glad415/MySQL-RECURSIVE-QUREY-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

SQL (Structured Query Language)

• RDBMS에서 데이터를 관리하기 위해 설계된 언어
• 데이터는 정해진 스키마에 따라 테이블에 저장된다.
  • 스키마에 맞지 않는 형식의 데이터는 저장할 수 없다.
• 데이터는 관계에 따라 여러 테이블에 분산된다. (중복 회피)
• 수직적 확장만을 지원한다.

NoSQL

• 전통적인 RDBMS와 다른 방식으로 데이터베이스를 관리한다.
• 정해진 스키마가 없어서 보다 유연하게 데이터를 추가할 수 있다.
  • 다른 구조의 데이터 타입이 컬렉션에 들어갈 수 있다.
  • 데이터 중복 위험이 존재한다. (데이터 수정/삭제시 관계된 모든 컬렉션을 수정해야 한다.)
• 조인 연산이 존재하지 않는다. (RDBMS 방식에 비해 데이터 읽는 속도가 빠르다고 볼 수 있다.)
• 수직, 수평적 확장 전부 가능하다.

트랜잭션 (Transation)

• 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위, 한꺼번에 모두 수행되어야 하는 일련의 연산들을 의미한다.

특성

• 원자성 (Atomicity) : 트랜잭션이 DB에 모두 반영되거나, 혹은 모두 반영되지 않아야 한다.
• 일관성 (Consistency) : 트랜잭션의 작업 처리 결과는 항상 일관성이 있어야 한다.
• 독립성 (Isolation) : 복수 개의 트랜잭션이 동시에 병행 실행되는 경우 어떤 트랜잭션도 다른 트랜잭션에 끼어들 수 없어야 한다. 각각의 트랜잭션은 서로 간섭없이 독립적으로 수행되어야 한다.
• 지속성 (Durability) : 성공적으로 완료된 트랜잭션의 결과는 영구적으로 반영되어야 한다.

Operation

• Commit : 하나의 트랜젝션이 성공적으로 끝났고, DB가 일관된 상태일 때 이 트랜젝션이 완료된 것을 트랜젝션 관리자에게 알려주기 위해 사용하는 연산
• Rollback : 하나의 트랜젝션 처리가 비정상적으로 종료되어 원자성이 깨진 경우, 이 트랜젝션이 행한 모든 연산을 취소하는 연산

즉, Commit은 해당 트랜젝션으로 반영된 DB 변경사항을 저장하는 것이고, Rollback은 해당 트랜젝션으로 반영된 DB 변경사항을 취소하는 것을 말한다.

교착상태
둘 이상의 트랜잭션이 특정 자원의 락을 보유한 채 다른 트랜잭션이 소유하고 있는 락을 요구했을 때, 아무리 기다려도 상황이 바뀌지 않는 상태

UNDO
수정된 페이지들이 버퍼 교체 알고리즘에 따라서 디스크에 출력될 수 있다. 트랜잭션과 무관하게 버퍼의 상태에 따라서 버퍼가 교체된다. 이로 인해 정상 종료되지 않은 트랜잭션이 변경한 페이지들은 원상 복구되어야 한다.

격리 수준 (Isolation Level)

여러 트랜잭션이 동시에 처리될 때, 트랜잭션끼리 얼마나 고립되어 있는지 나타내는 것을 말한다.

즉, 특정 트랜잭션에서 다른 트랜잭션이 변경한 데이터를 볼 수 있도록 허용 여부를 결정한다.

독립성을 보장하기 위해 엄격하게 락을 걸어서 트랜잭션을 순차 처리하면 퍼포먼스가 떨어지므로 효율적인 locking 방법이 필요하다.

• 레벨 0 : Read Uncomitted
  한 트랜잭션에서 처리중인 데이터에 다른 트랜잭션의 접근을 허용한다. 즉, 커밋하지 않은 데이터를 읽을 수 있다.
  DB의 일관성 유지 불가
• 레벨 1 : Read Comitted
  커밋된 트랜잭션만 읽을 수 있다.
• 레벨 2 : Repeatable Read
  트랜잭션이 완료되기 전가지 한 번 조회한 데이터를 반복해서 조회해도 내용이 항상 동일함을 보장한다.
  트랜잭션 영역에 해당하는 데이터의 수정 불가
• 레벨 3 : Serializable
  가장 엄격한 격리 수준, 트랜잭션 영역에 해당하는 데이터의 수정, 입력 불가

낮은 단계 Isolation Level 활용할 때 발생하는 현상들

• 레벨 0 : Dirty Read
  수정되었지만 commit 되지 않은 데이터를 다른 트랜잭션이 읽을 때 발생하는 현상
• 레벨 0, 1 : Non-repeatable Read
  한 트랜잭션에서 같은 쿼리를 두 번 수행할 때, 그 사이에 다른 트랜잭션이 값을 수정/삭제 하면서 일관성이 깨지는 현상
• 레벨 0, 1, 2 : Phantom Read
  한 트랜잭션에서 일정 범위의 레코드를 두 번 이상 읽었을 때, 첫 번째 쿼리에 없던 레코드가 두 번째 쿼리에서 나타나는 현상
  트랜잭션 도중 새로운 레코드 삽입을 허용하기 때문에 나타난다.

갱신 내용 손실 : 동시에 하나의 데이터가 갱신될 때 하나의 갱신이 누락되는 경우
현황 파악 오류 : 하나의 데이터 갱신이 끝나지 않은 시점에서 다른 트랜잭션이 해당 데이터를 조회하는 경우
모순성 : 두 트랜잭션이 동시에 실행될 때 데이터베이스가 일관성이 없는 모순된 상태로 남는 문제
연쇄 복귀 : 두 트랜잭션이 하나의 레코드를 갱신할 때 하나의 트랜잭션이 롤백하면 다른 하나의 트랜잭션이 마저 롤백되는 문제