SQL·DB 다시 잡기 - 조인·인덱스·트랜잭션을 내 프로젝트 테이블로

이 장에서 배우는 것

팀 프로젝트에서 목록 조회와 등록 기능을 만들었다면 이미 데이터베이스를 사용해 서비스를 완성한 경험이 있다. 이제 그때 작성한 쿼리를 다시 꺼내 결과가 맞는 이유와 데이터가 늘어났을 때의 동작을 설명할 차례다. 짧은 일정 안에 팀으로 서비스를 끝낸 경험은 출발점이 된다. 여기에 조회 결과와 실행 계획, 수정 충돌 기록을 붙이면 자신이 내린 결정을 구체적으로 보여 줄 수 있다.

앞 장에서 프로젝트의 기능과 CS 개념을 연결했다면, 여기서는 테이블과 쿼리로 범위를 좁힌다. 지원 기록을 관리하는 작은 데이터베이스를 만들고, 같은 조회를 인덱스 생성 전후에 비교한다. 이어서 두 사용자가 같은 지원 기록을 수정하는 상황을 재현한다. 완성 코드는 데이터베이스를 임시로 만들고 점검 결과를 출력한 뒤 종료하는 도구다. 자신의 프로젝트에서도 같은 순서로 점검하고 그 결과를 이번 주의 완료 증거로 남긴다.

조인(join) 결과의 한 행이 무엇을 뜻하는지 설명하고 예상 행 수를 계산한다.
느린 조회의 후보를 찾고 실행 계획으로 인덱스(index)의 사용 여부를 확인한다.
트랜잭션(transaction)의 경계와 동시 수정 충돌을 구분한다.
자신의 프로젝트에서 쿼리 하나를 골라 변경 전후와 선택 이유를 기록한다.

문제 상황

팀 프로젝트에 취업 준비 기록 기능이 있다고 가정한다. 기업 정보는 companies 테이블에, 지원 기록은 applications 테이블에 저장한다. 기업 하나에 여러 번 지원할 수 있으므로 기업과 지원 기록은 일대다 관계다. 지원 기록에는 기업 번호, 진행 상태, 수정 버전을 둔다.

처음에는 지원 기록이 몇 건 없어 기업별 목록이 빠르게 나타났다. 시연용 데이터를 늘리자 특정 기업의 제출 완료 기록을 찾는 조회가 느려졌다. 담당자는 기업 이름까지 보여 주려고 두 테이블을 연결했지만, 조인 조건을 빼먹은 조회에서는 같은 지원 기록이 여러 기업 이름과 함께 나타났다. 다른 화면에서는 지원하지 않은 기업까지 보여 주어야 하는데 내부 조인을 사용해 일부 기업이 사라졌다.

수정 화면에도 문제가 있다. 두 사람이 같은 지원 기록을 열었다. 한 사람이 검토 완료로 바꾼 뒤, 다른 사람이 이전 화면에서 면접 예정으로 저장했다. 마지막 저장이 앞선 변경을 덮었는데도 화면에는 성공만 표시된다. 조회 속도, 결과의 의미, 수정 충돌은 서로 다른 문제다. 따라서 하나의 해결책으로 묶지 않고 각각의 예상 결과를 먼저 적는다.

이 예제에서 기업과 지원 기록은 연습용 데이터다. 실제 프로젝트에 적용할 때는 팀원의 동의를 받은 개발용 데이터나 직접 만든 가상 데이터를 사용한다. 완료 증거에는 개인정보가 포함된 데이터 덤프 대신 테이블 구조, 가상 입력, 쿼리, 결과를 남긴다.

테이블을 연결하기 전에 결과의 단위를 정한다

기본 키(primary key)는 테이블 안에서 행을 식별한다. 외래 키(foreign key)는 다른 테이블의 행을 참조하는 관계를 표현한다. applications.company_id가 companies.id를 참조하면 지원 기록이 어느 기업에 속하는지 정할 수 있다. SQLite에서는 외래 키 검사를 연결마다 켜야 하므로 완성 코드의 두 연결 모두에서 이를 설정한다.

조인을 쓰기 전에는 “결과 한 행은 지원 기록 하나다”처럼 단위를 문장으로 적는다. 지원 기록과 기업을 연결하면 지원 기록마다 기업 이름이 붙는다. 반면 기업별 지원 건수를 구하면 결과 한 행은 기업 하나가 된다. 이 둘을 혼동하면 합계가 부풀거나 화면에 중복이 생겨도 원인을 찾기 어렵다.

기업 하나에 여러 지원 기록이 연결되므로 조인 결과의 단위를 먼저 정해야 한다

내부 조인(INNER JOIN)은 연결 조건에 맞는 양쪽 행을 반환한다. 지원 기록이 있는 기업만 필요할 때 사용할 수 있다. 왼쪽 외부 조인(LEFT JOIN)은 왼쪽 테이블의 행을 유지한다. 모든 기업과 지원 건수를 보여 주려면 기업을 왼쪽에 두고 지원 기록을 연결한다. 연결된 지원 기록이 없는 기업에는 오른쪽 열에 NULL이 들어간다.

SELECT c.id, c.name, COUNT(a.id) AS submitted_count
FROM companies AS c
LEFT JOIN applications AS a
  ON a.company_id = c.id
 AND a.status = 'submitted'
GROUP BY c.id, c.name
ORDER BY c.id;

여기서 상태 조건은 ON에 있다. 기업은 모두 유지하되 제출 완료 지원 기록만 연결하겠다는 뜻이다. 같은 조건을 WHERE로 옮기면 연결되지 않아 NULL이 된 행이 걸러져 제출 완료 기록이 없는 기업이 빠진다. COUNT(a.id)는 실제로 연결된 지원 기록의 식별자만 센다. COUNT(*)를 사용하면 지원 기록이 없는 기업도 외부 조인이 만든 한 행으로 계산된다.

연결 조건이 있다고 해서 결과가 항상 원하는 단위가 되는 것은 아니다. 지원 기록 하나에 첨부 파일이 세 개 있고 파일 테이블까지 연결하면 그 지원 기록은 세 행으로 펼쳐진다. 이때 지원 횟수를 단순히 세면 파일 수를 세는 결과가 된다. 중복 제거를 먼저 붙이기보다 어느 관계에서 행이 늘어났는지 확인해야 한다.

연습 데이터는 기업 40개와 지원 기록 1,200개다. 각 기업에 지원 기록이 30개씩 배정되고, 지원 기록 번호가 3의 배수이면 제출 완료 상태가 된다. 기업 7의 지원 번호는 7, 47, 87처럼 40씩 증가한다. 그중 3의 배수는 87부터 1,167까지 120 간격으로 10개다. 쿼리를 실행하기 전에 이 수를 적어 두면 결과 검증의 기준이 생긴다.

느린 쿼리는 실행 계획과 함께 읽는다

조회가 느리다고 느꼈을 때 바로 인덱스를 추가하지 않는다. 먼저 실제로 실행한 SQL과 매개변수, 결과 행 수, 정렬 조건을 기록한다. 반환할 필요가 없는 열이나 행이 많은지, 조인으로 행 수가 늘어났는지부터 확인한다. 실행 시간만 보고 판단하면 데이터 양이나 측정 환경의 차이를 쿼리 개선으로 오해할 수 있다.

이번 조회의 조건은 기업 번호와 진행 상태의 동등 비교다. 인덱스가 없으면 지원 테이블의 행을 훑으며 조건을 검사할 수 있다. 두 열을 묶은 복합 인덱스(composite index)를 만들면 해당 조건에 맞는 범위를 찾는 경로가 생긴다. 예제에서는 기업별 조회를 기준으로 company_id를 앞에 놓고 status를 뒤에 둔다.

CREATE INDEX idx_app_company_status
ON applications(company_id, status);

열 순서는 다른 조회까지 보고 결정한다. 이 인덱스는 기업 번호만으로 찾는 조회에도 활용될 수 있지만, 상태만으로 찾는 조회에 같은 효율을 기대해서는 안 된다. 실제 선택은 데이터 분포와 통계, SQLite 버전에 따라 달라질 수 있다. 복합 인덱스의 첫 열을 생략하는 조회가 어떤 계획을 갖는지 따로 확인하는 습관이 필요하다.

인덱스 전후에 결과는 유지하고 지원 테이블의 접근 경로가 달라지는지 확인한다

SQLite의 EXPLAIN QUERY PLAN은 조회에 사용할 접근 경로를 보여 준다. SCAN은 테이블이나 인덱스 전체를 순회하는 접근을, SEARCH는 조건에 맞는 일부를 찾는 접근을 나타낸다. SCAN이라는 글자만으로 문제라고 판정할 수는 없다. 작은 테이블이나 대부분의 행을 읽는 조회에서는 전체 순회가 합리적일 수 있다. 또한 인덱스 전체를 순회하는 계획도 SCAN으로 나타날 수 있으므로 뒤의 설명까지 읽어야 한다.

완성 코드는 기업 7의 제출 완료 지원 수를 같은 매개변수로 조회한다. 인덱스 전후에 결과가 모두 10인지 확인하고, 지원 테이블 별칭 a에 해당하는 계획만 분류해 출력한다. 실행 계획의 원문은 SQLite 버전에 따라 표현이 달라질 수 있으므로 그대로 예상 출력에 넣지 않는다. 대신 원문에서 전체 순회인지, 지정한 인덱스를 사용하는 탐색인지 판별한다. 예상하지 못한 계획이 나오면 성공으로 표시하지 않고 예외를 발생시킨다.

이 도구는 실행 시간의 개선을 증명하지 않는다. 접근 경로가 바뀌고 결과가 유지되는지를 확인한다. 자신의 프로젝트에서는 같은 가상 데이터와 매개변수로 여러 번 측정하고, 첫 실행과 반복 실행의 차이도 기록한다. 인덱스는 저장 공간과 쓰기 비용을 추가한다. 자주 실행되는 조회에 이익이 있는지 확인한 뒤 유지 여부를 결정한다.

트랜잭션 경계와 동시 수정 규칙을 나눈다

트랜잭션은 여러 변경을 하나의 작업 단위로 묶는다. 지원 상태 변경과 변경 이력 추가가 한 업무라면 둘 다 성공했을 때 확정하고, 중간에 실패하면 둘 다 되돌려야 한다. COMMIT은 변경을 확정하고 ROLLBACK은 아직 확정하지 않은 변경을 취소한다. 화면을 열어 둔 시간 전체를 트랜잭션으로 유지하면 잠금이 오래 지속될 수 있으므로 사용자의 입력 대기와 저장 작업의 경계를 구분한다.

트랜잭션을 사용한다는 사실만으로 오래된 화면의 덮어쓰기가 모두 해결되지는 않는다. 데이터베이스가 각 저장을 차례로 처리해도, 두 번째 저장이 이전 화면에서 만든 값이라는 업무상의 충돌은 남을 수 있다. 이를 발견하는 한 방법이 버전 값을 이용한 낙관적 잠금(optimistic locking)이다.

UPDATE applications
SET status = ?, version = version + 1
WHERE id = ? AND version = ?;

조회 당시 버전이 0이었다면 저장 조건에도 0을 넣는다. 다른 사용자가 먼저 저장해 버전이 1이 되었다면 조건에 맞는 행이 없어 수정 행 수가 0이 된다. 프로그램은 이를 정상적인 충돌로 처리하고 최신 내용을 다시 보여 준다. 단순히 성공 메시지를 출력하거나 조건에서 버전을 빼고 다시 저장하면 충돌 감지의 의미가 사라진다.

완성 코드는 연결 A와 연결 B가 모두 버전 0을 읽게 한다. A가 검토 완료로 바꾸고 버전을 1로 올린 뒤, B가 자신이 읽었던 버전 0으로 저장을 시도한다. B의 수정 행 수는 0이다. 도구는 이를 출력하고 현재 상태와 버전을 다시 읽는다. 연습 시나리오에서는 최신 상태가 검토 완료일 때 면접 예정으로 바꾸는 규칙이 있다고 정하고 한 번 더 저장한다.

이 순서는 같은 시점에 SQL 두 개를 실행하는 부하 실험이 아니다. 두 사용자의 읽기와 쓰기가 엇갈려 오래된 값이 생기는 상황을 두 연결로 재현한 것이다. SQLite의 쓰기 잠금 경합과 오래된 버전에 의한 수정 거절은 다른 문제다. 실제 화면에서는 최신 변경을 사용자가 확인해야 할 수 있고, 금액이나 수량처럼 업무 규칙이 있는 값은 그 규칙을 다시 검증해야 한다.

완성 코드

다음 내용을 main.py로 저장한다. Python 3.12 이상에서 표준 라이브러리만 사용한다. 임시 디렉터리에 데이터베이스를 만들고 두 연결을 닫은 뒤 디렉터리를 정리한다. 반복 실행해도 이전 실행의 데이터가 남지 않는다. 예제에서는 SQL로 트랜잭션 경계를 직접 제어하도록 isolation_level을 None으로 지정한다.

import sqlite3
from pathlib import Path
from tempfile import TemporaryDirectory


QUERY = """
SELECT c.name, COUNT(*) AS submitted_count
FROM companies AS c
JOIN applications AS a ON a.company_id = c.id
WHERE a.company_id = ? AND a.status = ?
GROUP BY c.id, c.name
"""
PARAMS = (7, "submitted")
INDEX_NAME = "idx_app_company_status"


def connect(path):
    conn = sqlite3.connect(path, isolation_level=None)
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def seed(conn):
    conn.executescript("""
        CREATE TABLE companies (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL
        );
        CREATE TABLE applications (
            id INTEGER PRIMARY KEY,
            company_id INTEGER NOT NULL REFERENCES companies(id),
            status TEXT NOT NULL,
            version INTEGER NOT NULL DEFAULT 0
        );
    """)
    conn.execute("BEGIN")
    try:
        conn.executemany(
            "INSERT INTO companies(id, name) VALUES (?, ?)",
            [(n, f"기업{n:02d}") for n in range(1, 41)],
        )
        rows = [
            (
                n,
                (n - 1) % 40 + 1,
                "submitted" if n % 3 == 0 else "draft",
            )
            for n in range(1, 1201)
        ]
        conn.executemany(
            "INSERT INTO applications(id, company_id, status) "
            "VALUES (?, ?, ?)",
            rows,
        )
        conn.execute("COMMIT")
    except Exception:
        conn.execute("ROLLBACK")
        raise


def access_path(conn):
    plan = conn.execute("EXPLAIN QUERY PLAN " + QUERY, PARAMS)
    details = [row[3] for row in plan]
    for detail in details:
        words = detail.split()
        if len(words) < 2 or words[1] != "a":
            continue
        if words[0] == "SEARCH" and INDEX_NAME in detail:
            return "복합 인덱스 탐색"
        if words[0] == "SCAN" and "INDEX" not in words:
            return "테이블 전체 순회"
    raise RuntimeError("지원 테이블의 실행 계획을 확인해야 한다")


def save_status(conn, app_id, expected_version, new_status):
    conn.execute("BEGIN")
    try:
        cursor = conn.execute(
            "UPDATE applications "
            "SET status = ?, version = version + 1 "
            "WHERE id = ? AND version = ?",
            (new_status, app_id, expected_version),
        )
        if cursor.rowcount != 1:
            conn.execute("ROLLBACK")
            return False
        conn.execute("COMMIT")
        return True
    except Exception:
        conn.execute("ROLLBACK")
        raise


def inspect(a, b):
    seed(a)
    print("데이터: 기업 40개, 지원 기록 1200개")
    before = a.execute(QUERY, PARAMS).fetchall()
    print(f"인덱스 전: {access_path(a)}")
    a.execute(
        f"CREATE INDEX {INDEX_NAME} "
        "ON applications(company_id, status)"
    )
    after = a.execute(QUERY, PARAMS).fetchall()
    print(f"인덱스 후: {access_path(a)}")
    if before != after or after != [("기업07", 10)]:
        raise RuntimeError("조회 결과가 예상과 다르다")
    print("결과 검증: 기업07, 제출 완료 10건, 전후 동일")

    version_a = a.execute(
        "SELECT version FROM applications WHERE id = ?", (7,)
    ).fetchone()[0]
    version_b = b.execute(
        "SELECT version FROM applications WHERE id = ?", (7,)
    ).fetchone()[0]
    if not save_status(a, 7, version_a, "reviewed"):
        raise RuntimeError("첫 저장이 거절되었다")
    print("연결 A 저장: 성공")
    if save_status(b, 7, version_b, "interview"):
        raise RuntimeError("오래된 버전의 저장이 허용되었다")
    print("연결 B 저장: 충돌 감지, 변경 취소")

    current_status, current_version = b.execute(
        "SELECT status, version FROM applications WHERE id = ?", (7,)
    ).fetchone()
    if current_status != "reviewed":
        raise RuntimeError("상태 전환 규칙을 다시 확인해야 한다")
    if not save_status(b, 7, current_version, "interview"):
        raise RuntimeError("재조회 후 저장이 거절되었다")
    print("최신 상태 확인 후 재시도: 성공")
    status, version = a.execute(
        "SELECT status, version FROM applications WHERE id = ?", (7,)
    ).fetchone()
    if (status, version) != ("interview", 2):
        raise RuntimeError("최종 상태가 예상과 다르다")
    print(f"최종 기록: id=7, status={status}, version={version}")


def main():
    with TemporaryDirectory() as directory:
        path = Path(directory) / "practice.db"
        a = connect(path)
        try:
            b = connect(path)
            try:
                inspect(a, b)
            finally:
                b.close()
        finally:
            a.close()


if __name__ == "__main__":
    main()

줄별 해설

가져오기와 상수. sqlite3는 SQL 실행과 연결 관리를 맡는다. Path는 임시 디렉터리와 파일 이름을 연결하고, TemporaryDirectory는 실행이 끝난 뒤 파일을 정리한다. QUERY와 PARAMS를 분리해 인덱스 전후에 같은 SQL과 조건을 사용한다. 기업 이름으로 묶은 결과가 아니라 기업 식별자와 이름을 함께 묶어, 이름이 같은 기업도 별개로 취급한다.

connect 함수. 두 연결에 같은 설정을 적용한다. 외래 키 검사를 켜면 존재하지 않는 기업 번호를 참조하는 지원 기록을 거절할 수 있다. isolation_level=None은 이 코드에서 BEGIN, COMMIT, ROLLBACK으로 경계를 직접 정하기 위한 선택이다. 연결을 만드는 것과 트랜잭션을 시작하는 것은 별개의 작업이다.

seed 함수의 테이블 정의. 기업 번호와 지원 번호는 각각 행의 식별자다. version은 지원 기록을 수정할 때마다 증가한다. 여기서는 진행 상태를 문자열로 저장하지만, 서비스에 적용할 때는 허용 상태와 전환 규칙도 정해야 한다. 예제의 version 기본값은 0이므로 삽입할 때 따로 전달하지 않는다.

seed 함수의 데이터 삽입. 테이블을 만든 뒤 BEGIN으로 삽입 작업을 묶는다. executemany는 같은 형태의 SQL에 여러 매개변수를 전달한다. 나머지 연산으로 기업 번호를 순환시켜 기업마다 30건을 배정한다. 삽입이 모두 끝나면 확정하고, 도중에 실패하면 되돌린 뒤 원래 예외를 다시 전달한다.

access_path 함수. 실행 계획 행의 네 번째 값인 설명 문자열을 모은다. 이 쿼리의 지원 테이블 별칭은 a이므로 해당 접근만 찾는다. 지정한 인덱스 이름을 포함한 SEARCH는 복합 인덱스 탐색으로 분류하고, 인덱스를 쓰지 않는 SCAN은 테이블 전체 순회로 분류한다. 이 함수는 이번 쿼리의 점검용이며 모든 SQL을 분석하는 범용 도구가 아니다. SQLite의 계획 출력 형식이 바뀌면 이 부분을 확인해야 한다.

save_status 함수. 매개변수 바인딩(parameter binding)으로 값들을 SQL에 전달한다. SQL 문자열에 사용자 값을 이어 붙이지 않는다. UPDATE가 끝나면 rowcount로 수정 행 수를 검사한다. 한 행이 수정되었을 때만 확정한다. 식별자가 존재하지 않아도 수정 행 수가 0이므로 실제 서비스에서는 삭제 여부와 버전 충돌을 추가 조회로 구분할 수 있다.

inspect 함수의 조회 비교. 먼저 인덱스가 없는 결과를 저장하고 실행 경로를 출력한다. 인덱스를 만든 뒤 같은 조회를 실행해 결과와 경로를 다시 확인한다. before와 after의 비교만으로 끝내지 않고 미리 계산한 10건과도 비교한다. 두 쿼리가 똑같이 잘못된 결과를 반환하는 경우를 줄이기 위해서다. 결과가 기업 하나인 조회이므로 행 순서의 차이는 비교에 영향을 주지 않는다.

inspect 함수의 수정 비교. A와 B가 각각 읽은 버전을 따로 보관한다. A의 저장 뒤 B의 이전 버전으로 저장하면 실패해야 한다. 이후 최신 상태를 읽고 허용된 상태 전환인지 검사한 다음 현재 버전으로 저장한다. 마지막에는 다른 연결 A에서 최종 상태를 읽어 확정된 변경이 보이는지 확인한다.

main 함수와 종료. finally는 점검 중 예외가 나도 연결을 닫도록 한다. sqlite3 연결의 문맥 관리 구문은 연결 종료 자체를 대신하지 않으므로 close를 명시했다. 두 연결을 닫은 뒤 임시 디렉터리의 수명이 끝난다. 임시 경로와 실행 시간은 출력하지 않아 실행마다 달라지는 값을 결과에 섞지 않는다.

실행 결과

main.py를 저장한 디렉터리에서 다음 명령을 실행한다. 외부 패키지 설치나 네트워크 연결은 필요하지 않다.

python3 main.py

예상 출력은 다음과 같다. 마지막 버전 2는 성공한 수정이 두 번 있었다는 뜻이다. 거절된 수정 시도는 버전을 증가시키지 않는다.

데이터: 기업 40개, 지원 기록 1200개
인덱스 전: 테이블 전체 순회
인덱스 후: 복합 인덱스 탐색
결과 검증: 기업07, 제출 완료 10건, 전후 동일
연결 A 저장: 성공
연결 B 저장: 충돌 감지, 변경 취소
최신 상태 확인 후 재시도: 성공
최종 기록: id=7, status=interview, version=2

다른 SQLite 환경에서 실행 계획 분류 예외가 발생하면 분류 조건을 지워 성공으로 만들지 않는다. 먼저 sqlite3.sqlite_version으로 사용 중인 SQLite 버전을 확인하고 EXPLAIN QUERY PLAN의 설명을 별도로 확인한다. Python 버전과 내장 SQLite 버전은 구분해서 기록한다. 계획 문자열의 형식과 의미를 확인할 때는 SQLite 실행 계획 공식 안내를 사실 확인 자료로 사용할 수 있다.

실무에서 자주 틀리는 것

조인 조건을 빠뜨리고 중복 제거로 덮는다

다음 쿼리는 기업과 지원 기록 사이의 관계를 지정하지 않는다. 결과가 많다고 DISTINCT를 붙여도 잘못된 연결이 올바르게 바뀌지는 않는다.

SELECT c.name, a.id
FROM companies AS c
JOIN applications AS a;

두 테이블을 연결하는 키를 명시한다. 한 지원 기록에 기업 이름 하나가 붙는지 작은 데이터로 확인한다.

SELECT c.name, a.id
FROM companies AS c
JOIN applications AS a ON a.company_id = c.id;

외부 조인의 오른쪽 조건을 WHERE에 둔다

모든 기업을 유지하려는 요구에서 다음 쿼리는 제출 완료 기록이 없는 기업을 제외한다.

SELECT c.id, COUNT(a.id)
FROM companies AS c
LEFT JOIN applications AS a ON a.company_id = c.id
WHERE a.status = 'submitted'
GROUP BY c.id;

연결할 지원 기록의 조건을 ON으로 옮긴다. COUNT(a.id)를 유지하면 연결된 지원이 없는 기업의 건수는 0이다.

SELECT c.id, COUNT(a.id)
FROM companies AS c
LEFT JOIN applications AS a
  ON a.company_id = c.id AND a.status = 'submitted'
GROUP BY c.id;

조회 조건과 관계없는 인덱스를 추가한다

지원 기록의 기업과 상태를 함께 찾는 조회인데 기업 이름 인덱스만 만들면 지원 테이블을 찾는 경로는 개선되지 않을 수 있다.

CREATE INDEX idx_company_name ON companies(name);

해당 조회의 조건에 맞는 후보를 만들고 실행 계획을 비교한다. 아래 인덱스를 선택했더라도 이름 검색이라는 별도 요구가 있다면 그 조회는 따로 검토한다.

CREATE INDEX idx_app_company_status
ON applications(company_id, status);

트랜잭션만 있으면 오래된 저장도 안전하다고 생각한다

다음 코드는 저장을 트랜잭션으로 묶지만, 조회 이후 다른 사람이 수정했는지는 확인하지 않는다. 이 코드 조각은 저장할 값이 status와 app_id에 들어 있다고 가정한다.

conn.execute("BEGIN")
conn.execute(
    "UPDATE applications SET status = ? WHERE id = ?",
    (status, app_id),
)
conn.execute("COMMIT")

조회 당시 버전인 read_version을 조건에 포함하고 수정 행 수를 확인한다. 0행이면 취소한 뒤 최신 내용을 다시 읽는다. 충돌 후의 재시도는 업무 규칙을 확인한 다음 결정한다.

conn.execute("BEGIN")
cursor = conn.execute(
    "UPDATE applications "
    "SET status = ?, version = version + 1 "
    "WHERE id = ? AND version = ?",
    (status, app_id, read_version),
)
if cursor.rowcount == 1:
    conn.execute("COMMIT")
else:
    conn.execute("ROLLBACK")

이 짧은 비교 코드는 충돌 처리의 핵심만 보여 준다. 실제 함수에는 완성 코드처럼 SQL 실행 중 예외가 발생했을 때 되돌리는 경로도 둔다.

한눈에 보기

조회와 수정에서 확인할 기준
주제	먼저 적을 것	확인 방법	남길 증거
조인	결과 한 행의 의미	연결 전후 행 수와 누락 확인	작은 입력과 예상 결과
인덱스	조회 조건과 정렬	결과 유지와 실행 계획 비교	변경 전후 계획과 선택 이유
트랜잭션	함께 성공해야 할 변경	실패 시 미확정 변경 취소	작업 경계와 실패 재현 기록
동시 수정	오래된 저장의 처리 규칙	버전 조건과 수정 행 수	두 연결의 실행 순서와 최종 값

이번 주에는 프로젝트 전체의 SQL을 다시 작성하지 않는다. 목록 조회 하나와 수정 기능 하나를 골라 설명 가능한 상태로 만든다. 각 작업은 예상 결과를 먼저 적고, 실행한 뒤 차이를 확인하는 순서로 진행한다.

이번 주에 할 일과 완료 증거
확인	시점	할 일	완료 증거
□	첫째 날	프로젝트의 관련 테이블 2~3개와 키를 정리한다	관계 그림과 결과 한 행의 의미
□	둘째 날	지원이 없는 기업을 포함하는 조회를 연습한다	가상 입력과 예상·실제 결과
□	셋째 날	조회 하나의 인덱스 전후를 비교한다	SQL, 매개변수, 데이터 수, 실행 계획
□	넷째 날	두 연결의 오래된 저장을 재현한다	읽기·쓰기 순서와 충돌 후 최종 값
□	다섯째 날	선택 이유와 남은 질문을 정리한다	실행 출력과 점검 기록 한 문서

완료 증거는 “SQL을 복습했다”라는 문장보다 구체적이어야 한다. 테이블 정의, 재현 가능한 가상 데이터, 예상 결과, 변경 전후 SQL과 실행 계획, 수정 충돌 순서, 선택 이유를 한 문서에 모은다. 인덱스를 추가하지 않기로 결정했어도 관찰 결과와 비용을 근거로 설명했다면 점검 결과가 된다. 실행 시간을 재지 않았다면 속도가 빨라졌다고 쓰지 않는다.

복습 자료는 막힌 질문에 맞춰 사용한다. 조인의 의미와 트랜잭션 경계가 헷갈리면 《데이터베이스 개론》에서 해당 주제를 읽고 자신의 테이블로 다시 설명한다. 동시 실행과 잠금의 배경은 《개발자를 위한 컴퓨터 사이언스》와 《비전공자를 위한 컴퓨터공학 지도》에서 확인한다. SQL 연구소는 외부 조인과 집계의 작은 변형을 반복하는 데, 코드패스는 이번 주 점검 기록을 일정한 간격으로 다시 확인하는 학습 동선에 둔다. 각 자료의 예제를 옮기는 대신 자신의 입력과 예상 결과를 새로 만든다.

완성 코드의 함수 경계와 이름을 검토할 때는 《클린 코드 노하우》를, 인덱스 추가의 이유를 동료에게 검토받을 때는 《코드 리뷰 실무》를 연결한다. 《개발자 실무 면접》은 관찰한 사실과 판단을 구분해 설명하는 연습에 사용한다. 다음 장에서 실패 사례를 입력으로 남기는 연습을 할 때는 《논리로 푸는 알고리즘》을 연결한다. 이번 주에는 SQL 점검의 완료 증거를 먼저 마무리한다.

연습 문제

companies에는 기업이 3개 있고 applications에는 첫 번째 기업의 draft 기록 2개와 두 번째 기업의 submitted 기록 1개만 있다고 하자. 기업별 제출 완료 건수를 모두 출력하는 SQL을 작성하고 결과를 기업 번호 순서로 적어라.
완성 코드에서 인덱스를 만들기 전과 후의 결과가 모두 10이었다. 이 사실과 실행 계획의 변화만으로 실행 시간이 줄었다고 말할 수 있는지 설명하라. 추가로 남겨야 할 측정 조건을 세 가지 적어라.
두 연결이 같은 지원 기록의 버전 4를 읽었다. A가 먼저 저장한 뒤 B가 버전 4로 저장했다. A의 저장이 성공했다면 각 수정 행 수와 B의 시도 직후 버전을 적어라. B가 해야 할 다음 작업도 설명하라.
자신의 프로젝트에서 조회 하나와 수정 하나를 고른다. 조회 결과 한 행의 의미, 인덱스 후보의 열 순서와 이유, 트랜잭션에 포함할 변경, 오래된 수정의 처리 방법을 각각 한 문장으로 적어라.

정답과 해설

기업을 왼쪽에 두고 제출 완료 조건을 ON에 둔다. 결과는 첫 번째 기업 0건, 두 번째 기업 1건, 세 번째 기업 0건이다. 첫 번째 기업에는 지원 기록이 있지만 제출 완료 기록은 없다는 점을 구분한다.
```
SELECT c.id, COUNT(a.id) AS submitted_count
FROM companies AS c
LEFT JOIN applications AS a
  ON a.company_id = c.id AND a.status = 'submitted'
GROUP BY c.id
ORDER BY c.id;
```
실행 시간이 줄었다고 단정할 수 없다. 결과 일치로는 관찰한 입력에서 조회 결과가 유지되었다는 사실을, 실행 계획으로는 선택한 접근 경로가 달라졌다는 사실을 말할 수 있다. 추가 측정 조건으로 같은 데이터 양과 분포, 같은 매개변수와 반환 행 수, 반복 횟수와 첫 실행 포함 여부를 기록할 수 있다. 프로젝트 기록에는 사용 환경과 측정 방법도 함께 적는다.
A의 수정 행 수는 1이고 버전은 5가 된다. B의 수정 행 수는 0이며 버전은 5로 유지된다. B는 자신의 미확정 작업을 취소하고 최신 상태를 다시 읽는다. 이어서 변경 내용을 확인하고 저장 의도를 다시 적용할 수 있는지 판단한다. 현재 버전만 가져와 무조건 덮어쓰는 방식은 앞선 변경의 의미를 확인하지 못한다.
예를 들어 “결과 한 행은 제출 완료 지원 기록 하나다”, “기업별 조회가 많아 기업 번호를 앞에 두고 상태를 뒤에 둔다”, “지원 상태 변경과 변경 이력 추가를 함께 확정한다”, “조회한 버전이 달라졌으면 저장을 거절하고 최신 변경을 보여 준다”라고 적을 수 있다. 자신의 테이블 이름과 실제 요구를 넣어 다시 작성해야 완료된다. 다음 장에서는 이처럼 예상과 실제의 차이를 기록하는 습관을 코딩 테스트의 실패 입력에 적용한다.