[LG U+ 유레카 4기] WEEK 06 - 데이터베이스 (4)

DML, 윈도우 함수, 트랜잭션

---

1. 오늘의 학습 흐름

• DML (INSERT, UPDATE, DELETE)
• TCL (COMMIT, ROLLBACK, SAVEPOINT)
• 윈도우 함수 (Window Function)
• 트랜잭션 ACID, 격리 수준 (Isolation Level)
• MVCC, Lock, Deadlock

---

2. DML - 데이터 조작

INSERT

-- 컬럼 지정 (지정한 컬럼에만 값 입력)
INSERT INTO emp (empno, ename, sal)
VALUES (3, 'kdg', 10000);

-- 전체 컬럼 순서대로 입력
INSERT INTO emp VALUES (3, 'kdg', 'CLERK', NULL, '2024-01-01', 10000, NULL, 10);

UPDATE

-- 조건에 맞는 행만 수정
UPDATE emp SET sal = 5000 WHERE ename = 'ureca';

-- 서브쿼리를 이용한 UPDATE
-- MySQL에서 같은 테이블의 서브쿼리 사용 시 FROM절로 한 번 더 감싸야 함
UPDATE emp
SET sal = (SELECT * FROM (SELECT AVG(sal) FROM emp) avgsal);

DELETE

-- 조건에 맞는 행 삭제
DELETE FROM emp WHERE empno = 3;

-- 전체 삭제
DELETE FROM emp;

-- 같은 테이블 서브쿼리 (FROM절로 감싸야 함)
DELETE FROM emp
WHERE sal < (SELECT * FROM (SELECT AVG(sal) FROM emp) avgsal);

---

3. TCL - 트랜잭션 제어

DML 수행 결과는 임시 저장소에 저장된다. 실제 DB에 반영하려면 TCL이 필요하다.

-- autocommit 확인 및 설정
SELECT @@autocommit;   -- Workbench 기본값: 1 (자동 commit)
SET autocommit = 0;    -- 수동 commit으로 전환

-- 트랜잭션 시작
START TRANSACTION;

-- 중간 저장 위치 설정
SAVEPOINT a;

-- 작업 수행 후 a지점으로 롤백
ROLLBACK TO a;

-- 모든 작업 취소 (START TRANSACTION 지점까지)
ROLLBACK;

-- DB에 반영
COMMIT;

UPDATE/DELETE 수행 시 해당 레코드에 Row Lock이 걸린다. COMMIT 또는 ROLLBACK 전까지 다른 트랜잭션은 해당 행에 DML을 수행하면 대기 상태가 된다.

---

4. 윈도우 함수 (Window Function)

GROUP BY는 그룹당 1개 행으로 합치지만, 윈도우 함수는 기존 행을 유지하면서 통계 정보를 추가한다.

함수() OVER (
    PARTITION BY 컬럼   -- GROUP BY처럼 그룹 분리
    ORDER BY 컬럼       -- 그룹 내 정렬
    ROWS/RANGE 범위     -- 계산 범위 지정
)

주의: 윈도우 함수는 WHERE에서 사용할 수 없다. 서브쿼리로 감싸야 한다.

-- 불가
SELECT * FROM emp WHERE RANK() OVER(ORDER BY sal DESC) <= 3;

-- 가능
SELECT * FROM (
    SELECT ename, sal, RANK() OVER(ORDER BY sal DESC) AS rnk FROM emp
) t
WHERE rnk <= 3;

ROW_NUMBER / RANK / DENSE_RANK

-- ROW_NUMBER: 중복 없는 행 번호
SELECT ename, sal,
       ROW_NUMBER() OVER(ORDER BY sal DESC, ename) AS 번호
FROM emp;

-- RANK: 공동 순위 처리, 다음 순위는 공동 순위 인원만큼 건너뜀
-- 예: 1위 2명 → 1, 1, 3, 4...
SELECT ename, sal,
       RANK() OVER(ORDER BY sal DESC) AS 순위
FROM emp;

-- DENSE_RANK: 공동 순위 처리, 다음 순위는 건너뛰지 않음
-- 예: 1위 2명 → 1, 1, 2, 3...
SELECT ename, sal,
       DENSE_RANK() OVER(ORDER BY sal DESC) AS 순위
FROM emp;

집계 함수 + OVER

-- 누적합
SELECT ename, sal,
       SUM(sal) OVER(ORDER BY sal, ename) AS 누적합
FROM emp;

-- 전체 평균 (OVER에 아무것도 없으면 전체 대상)
SELECT ename, sal,
       AVG(sal) OVER() AS 전체평균
FROM emp;

-- 부서별 평균 (PARTITION BY로 그룹 분리)
SELECT ename, sal, deptno,
       AVG(sal) OVER(PARTITION BY deptno) AS 부서별평균
FROM emp;

-- 부서 내 급여 순위
SELECT empno, ename, sal, deptno,
       RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS 부서내순위
FROM emp;

LAG / LEAD

-- LAG: 이전 행 값 (이전 입사자 급여 비교)
SELECT ename, hiredate, sal,
       LAG(sal) OVER(ORDER BY hiredate) AS 이전입사자급여,
       sal - LAG(sal) OVER(ORDER BY hiredate) AS 급여차이
FROM emp;

-- LEAD: 다음 행 값
SELECT ename, sal,
       LEAD(sal) OVER(ORDER BY sal) AS 다음급여
FROM emp;

FIRST_VALUE / LAST_VALUE

-- FIRST_VALUE: 첫 번째 값 (최고 급여자를 모든 행에 표시)
SELECT ename, sal,
       FIRST_VALUE(ename) OVER(ORDER BY sal DESC) AS 최고급여자
FROM emp;

-- LAST_VALUE: 마지막 값. 기본 범위가 "처음~현재"라서 전체 범위 지정 필요
SELECT ename, sal,
       LAST_VALUE(ename) OVER(
           ORDER BY sal
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS 최저급여자
FROM emp;

ROWS 범위 지정

-- 최근 7일 이동 평균
AVG(sales) OVER(
    ORDER BY sales_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

-- 처음부터 현재까지 (누적)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- 전체 범위
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

NTILE

-- 급여 기준 상위 25%, 50%, 75%, 하위 4등분
SELECT ename, sal,
       NTILE(4) OVER(ORDER BY sal DESC) AS 급여등급
FROM emp;

---

5. 트랜잭션 (Transaction)

ACID

데이터베이스 트랜잭션이 보장해야 하는 4가지 특성이다.

속성	이름	설명
A	Atomicity (원자성)	트랜잭션은 전부 성공하거나 전부 실패. 부분 반영 불가
C	Consistency (일관성)	트랜잭션 전후로 모든 제약 조건(PK, FK 등)을 만족해야 함
I	Isolation (격리성)	동시에 수행되는 트랜잭션끼리 서로 영향을 주면 안 됨
D	Durability (지속성)	커밋된 트랜잭션은 영구적으로 DB에 반영됨

트랜잭션 격리 수준 (Isolation Level)

격리 수준이 낮을수록 동시성은 높지만 데이터 정합성 문제가 생긴다.

격리 수준	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITTED	발생	발생	발생
READ COMMITTED	없음	발생	발생
REPEATABLE READ	없음	없음	발생(MySQL은 없음)
SERIALIZABLE	없음	없음	없음

MySQL InnoDB 기본값: REPEATABLE READ

Dirty Read         : commit 안 된 변경 데이터를 다른 트랜잭션이 읽는 현상
Non-Repeatable Read: 같은 트랜잭션에서 같은 SELECT 결과가 달라지는 현상
Phantom Read       : 같은 조건 조회 시 없던 행이 생기거나 있던 행이 사라지는 현상

-- 격리 수준 설정
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MVCC (Multi-Version Concurrency Control)

MySQL InnoDB가 Lock 없이 동시성을 제어하는 방법이다. 변경이 완료(COMMIT)되기 전까지 이전 버전의 데이터를 언두 로그(Undo Log)에 보관해서 다른 트랜잭션이 읽을 수 있게 한다.

MVCC 덕분에 SELECT는 Lock 없이 조회할 수 있고, UPDATE/DELETE 중에도 다른 트랜잭션의 SELECT가 막히지 않는다.

Deadlock

두 트랜잭션이 서로 상대방이 점유한 자원을 기다리는 교착 상태다.

T1: bookid=1 lock → bookid=2 기다림
T2: bookid=2 lock → bookid=1 기다림
→ 서로 영원히 대기 → Deadlock

MySQL은 Deadlock을 감지하면 한쪽 트랜잭션을 강제로 ROLLBACK시켜 Lock을 풀어준다.

---

6. 오늘 리뷰

오늘 트랜잭션 격리 수준 실습에서 T1, T2 두 세션을 동시에 열어서 직접 Lock 대기 상태를 확인했다. Dirty Read, Non-Repeatable Read을 재현했다.

MVCC가 인상 깊었다. Lock을 최소화하기 위해 "이전 버전 데이터를 별도로 보관한다"는 발상이다. Lock을 쓰면 동시 요청 처리 속도가 떨어지기 때문에 스냅샷으로 과거 데이터를 보여주는 방식을 선택한 것이다. 이 Trade-off 설계 결정이 MySQL 성능의 핵심 중 하나다.

윈도우 함수에서 GROUP BY와의 차이도 중요하다. 부서별 평균을 구할 때 GROUP BY를 쓰면 부서 수만큼 행이 줄어들지만, OVER(PARTITION BY)를 쓰면 원본 행을 유지하면서 부서 평균을 추가할 수 있다. 리포트나 대시보드 쿼리에서 매우 유용하다.

---

7. 키워드 정리

INSERT UPDATE DELETE 윈도우 함수 ACID Deadlock

---

8. 내일의 목표

• DDL (CREATE, ALTER, DROP, 제약 조건)
• 자격증 공부