데이터베이스 질문 - 인덱스·트랜잭션·정규화

이 장에서 배우는 것

앞 장에서 웹 요청이 서버에 도착하는 과정을 살펴보았다. 이제 중고 거래 서비스의 상품 목록과 거래 상태를 저장하는 데이터베이스로 시선을 옮긴다. 면접관은 “인덱스를 쓰면 빠르다”는 문장보다 어떤 조회를 개선하려는지, 그 대가가 무엇인지, 거래 도중 실패하면 데이터가 어떻게 남는지 설명할 수 있는지를 확인한다.

이 장에서는 상품 조회와 거래 처리를 예로 들어 답변의 근거를 만든다. 실행 계획으로 접근 경로를 확인하고, 일부 변경이 이루어진 뒤 오류가 나더라도 롤백으로 이전 상태를 되찾는 작은 실험을 작성한다. 실험에서 관찰한 사실과 다른 데이터베이스까지 일반화할 수 있는 원리를 구분하는 것도 목표다.

• 인덱스의 탐색 원리와 저장 공간·쓰기 비용을 함께 설명한다.
• 실행 계획에서 전체 탐색, 인덱스 탐색, 별도 정렬의 흔적을 찾는다.
• 트랜잭션의 ACID와 격리 수준별 이상 현상을 구분한다.
• 정규화, 조인, N+1 문제를 상품 목록의 데이터 접근 방식과 연결한다.
• Python 표준 라이브러리 sqlite3로 인덱스 전후 조회와 실패한 거래의 롤백을 확인한다.

문제 상황

가상의 중고 거래 서비스 팀 면접에서 지원자는 자신이 만든 상품 목록 화면을 설명한다. 판매자별로 판매 중인 상품을 최신 등록 순으로 세 개씩 보여 주며, 거래가 성사되면 구매자와 판매자의 예치금도 변경한다. 처음에는 상품 수가 적어 모든 조회가 빠르게 느껴졌다. 데이터가 늘자 목록 응답이 느려졌고, 거래 처리 중 오류가 발생했을 때 예치금이 맞지 않는 사례도 발견했다.

면접관: 판매자별 상품 목록이 느리다면 어디부터 확인하겠는가. 인덱스를 추가하면 어떤 비용이 생기며, 예치금 변경 중 두 번째 작업이 실패하면 어떻게 처리하겠는가.

이 질문에는 성능과 정확성이 함께 들어 있다. 조회가 느린 이유를 확인하지 않고 인덱스부터 늘리거나, 예외를 출력하는 것만으로 거래 실패를 처리했다고 답하면 설명이 끊긴다. 먼저 조회 조건과 정렬 기준을 밝히고 실행 계획을 확인한다. 돈이 움직이는 작업은 성공과 실패의 경계를 정하고, 그 경계를 트랜잭션으로 구현한다.

데이터베이스 면접에서 확인하는 질문과 답변의 방향

질문	의도	답변 핵심	꼬리 질문
인덱스는 왜 조회를 빠르게 하는가.	자료구조와 조회 비용을 연결하는가.	정렬된 키를 이용해 탐색 범위를 줄인다.	인덱스가 있어도 전체 탐색을 하는 이유는 무엇인가.
인덱스의 단점은 무엇인가.	읽기와 쓰기의 비용을 함께 보는가.	저장 공간과 삽입·수정·삭제의 유지 비용이 든다.	모든 열에 인덱스를 만들면 어떠한가.
실행 계획에서 무엇을 보는가.	추측을 관찰로 바꿀 수 있는가.	접근 경로, 인덱스, 정렬, 조인 순서를 본다.	인덱스를 사용하면 개선이 증명되는가.
ACID를 거래 처리로 설명하라.	용어를 데이터의 상태 변화로 설명하는가.	원자성·일관성·격리성·지속성을 구분한다.	일관성은 데이터베이스가 모두 보장하는가.
격리 수준은 왜 나누는가.	동시성과 관찰 가능한 결과를 이해하는가.	동시에 실행되는 거래의 간섭과 처리 비용을 조절한다.	반복 불가능 읽기와 팬텀 읽기는 어떻게 다른가.
재고 하나를 두 명이 구매하면 어떻게 하는가.	읽은 값에 의존하는 갱신의 위험을 아는가.	조건부 갱신과 영향받은 행 수 확인 등을 사용한다.	격리 수준을 높이면 재시도가 없어지는가.
정규화가 필요한 이유는 무엇인가.	중복과 변경 이상을 설명하는가.	함수 종속을 기준으로 사실을 나눈다.	조인이 늘면 반정규화해야 하는가.
내부 조인과 왼쪽 외부 조인은 어떻게 다른가.	결과 행의 의미를 이해하는가.	대응 행이 없는 왼쪽 행의 보존 여부가 다르다.	오른쪽 테이블 조건을 WHERE에 쓰면 어떠한가.
N+1 문제는 무엇인가.	전체 요청의 조회 횟수를 보는가.	목록 한 번 뒤에 항목마다 추가 조회가 발생한다.	조인 외의 해결 방법은 무엇인가.
반정규화는 언제 선택하는가.	성능과 정합성의 교환 관계를 아는가.	측정한 병목을 근거로 중복과 동기화 정책을 도입한다.	중복 값이 어긋나면 어떻게 복구하는가.

인덱스와 실행 계획으로 조회를 설명한다

인덱스(index)는 특정 열의 값을 빠르게 찾기 위한 보조 구조다. 관계형 데이터베이스에서 흔히 사용하는 B 계열 트리는 키를 정렬해 관리하며, 비교 결과를 따라 필요한 범위로 내려간다. 인덱스가 없으면 조건을 만족하는 행을 찾기 위해 많은 행을 검사할 수 있다. 인덱스가 있으면 조건에 맞는 키 범위를 찾은 뒤 필요한 행을 읽는 경로를 선택할 수 있다.

다만 “전체 탐색은 O(n), 인덱스는 O(log n)”만으로 조회 전체의 비용을 설명하면 부족하다. 조건에 맞는 행이 k개라면 그 결과를 읽는 비용도 필요하다. 인덱스에서 위치를 찾은 뒤 테이블의 여러 페이지를 읽어야 할 수도 있다. 대부분의 행을 반환하는 조회에서는 순차적으로 넓게 읽는 편이 유리할 수 있으며, 인덱스가 있다는 사실만으로 사용이 결정되지는 않는다.

이 장의 조회 조건은 판매자와 상태의 동등 비교이며, 정렬 기준은 상품 번호의 내림차순이다. 상품 번호가 커질수록 나중에 등록되었다고 가정한다. 따라서 판매자 번호, 상태, 상품 번호 내림차순으로 구성한 복합 인덱스가 이 조회와 잘 맞는다. 앞의 두 조건으로 범위를 좁히고, 그 안에서 요구한 순서로 세 행을 읽을 수 있기 때문이다.

복합 인덱스는 열 순서에 따라 활용 방식이 달라진다. 첫 열인 판매자 조건을 빼고 상태만 검색할 때 같은 효과를 기대해서는 안 된다. 일부 엔진은 통계와 조건에 따라 다른 탐색 방법을 선택할 수 있지만, 설계 답변의 출발점은 실제 조회가 인덱스의 앞쪽 열을 어떻게 사용하는지다. 동등 조건, 범위 조건, 정렬 조건을 함께 놓고 검토해야 한다.

인덱스의 대가는 저장 공간과 쓰기 비용이다. 상품을 등록하거나 삭제할 때 보조 구조도 변경해야 한다. 인덱스에 포함된 상태를 바꾸는 작업에도 유지 비용이 생긴다. 조회에 필요한 열까지 담는 커버링 인덱스(covering index)는 테이블 접근을 줄일 수 있지만, 인덱스 크기와 갱신 비용을 키운다. 아래 실험은 제목과 가격을 인덱스에 넣지 않아 이 효과까지 주장하지 않는다.

깊게 답하기: 인덱스를 추가하면 빨라지는가

인덱스 질문에 대한 답변 수준 비교

구분	답변	면접관이 확인하는 점
나쁜 답	인덱스를 만들면 검색이 빨라지므로 모든 조건 열에 만들겠다.	쓰기 비용과 선택 기준이 빠졌다.
보통 답	인덱스는 탐색을 빠르게 하지만 저장 공간과 쓰기 비용이 든다.	원리는 알지만 대상 조회와 연결되지 않았다.
좋은 답	판매자·상태 조건과 최신순 정렬에 맞는 복합 인덱스를 검토하겠다. 실행 계획과 실제 부하를 확인하고 쓰기 비용도 비교하겠다.	조회 형태, 관찰 방법, 대가가 연결된다.

지원자: 해당 목록 조회에서는 복합 인덱스로 읽는 범위를 줄일 수 있다고 판단한다. 판매자와 상태로 범위를 좁힌 뒤 상품 번호 역순으로 세 행만 필요하기 때문이다. 제가 만든 실험에서는 같은 조회를 인덱스 전후로 실행해 전체 탐색이 인덱스 탐색으로 바뀌는지, 결과가 같은지 확인했다. 다만 이 관찰만으로 운영 환경의 응답 시간이 개선됐다고 말하지는 않겠다. 실제 데이터 분포와 동시 요청, 상품 등록·수정 비용을 함께 측정하겠다.

이 답변은 결론, 근거, 직접 확인한 예시, 한계와 대안의 순서다. 실험 경험을 실제 서비스 운영 경험으로 바꾸어 말하지 않는 것도 중요하다. 면접관이 “왜 시간을 출력하지 않았는가”라고 물으면, 작은 데이터의 실행 시간은 캐시와 실행 환경의 영향을 크게 받으므로 이 실험에서는 경로 변화와 결과 보존에 집중했다고 답한다.

실행 계획(execution plan)은 엔진이 선택한 접근 방법을 보여 준다. SQLite의 EXPLAIN QUERY PLAN에서 SCAN은 전체 순회 계열의 접근을, SEARCH는 조건에 따라 일부를 찾는 접근을 나타낸다. SCAN에도 인덱스가 등장할 수 있으므로 단어 하나만 읽지 않는다. 어떤 테이블에 어떤 인덱스를 쓰는지, 별도 정렬을 위한 임시 구조가 필요한지도 함께 본다.

아래 코드에서는 같은 조회가 인덱스 생성 전에는 listing을 순회하고, 생성 후에는 지정한 인덱스를 이용하는지 검사한다. 실행 계획의 원문은 SQLite 버전에 따라 표현이 달라질 수 있으므로 확인한 특징을 일정한 한국어 문장으로 출력한다. 실행 계획은 예상 접근 경로에 대한 근거이며, 실제 처리 시간이나 검사한 행 수 자체를 제공하는 측정값은 아니다.

트랜잭션은 실패와 동시 실행의 경계를 정한다

트랜잭션(transaction)은 여러 데이터 작업을 하나의 성공 또는 실패 단위로 다루는 경계다. 판매자의 예치금을 먼저 늘리고 구매자의 예치금을 줄인다면, 두 번째 변경이 실패할 때 첫 번째 변경도 취소해야 한다. Python에서 예외를 잡았다는 사실만으로 이미 실행한 SQL이 취소되는 것은 아니다. 커밋과 롤백을 어디에서 수행하는지 명시해야 한다.

ACID를 중고 거래 예치금 변경에 적용한 의미

속성	이 사례에서의 의미	주의할 경계
원자성	판매자 증가와 구매자 감소를 함께 반영하거나 함께 취소한다.	한 SQL의 실패와 전체 트랜잭션의 취소는 구분한다.
일관성	잔액이 음수가 되지 않는 등 정의한 규칙을 지킨다.	제약 조건과 애플리케이션의 거래 로직이 함께 필요하다.
격리성	동시에 실행되는 거래 사이의 간섭을 정해진 수준으로 제한한다.	허용되는 관찰 결과는 격리 수준과 구현에 달려 있다.
지속성	성공적으로 커밋한 변경이 장애 뒤에도 보존되도록 한다.	메모리 데이터베이스 실험으로는 검증할 수 없다.

깊게 답하기: 두 번째 갱신이 실패하면 어떻게 하는가

나쁜 답: 예외 처리를 했으므로 앞의 변경도 취소된다.

보통 답: 두 갱신을 트랜잭션으로 묶고 오류가 나면 롤백한다.

좋은 답: 두 갱신을 하나의 트랜잭션으로 묶고 모두 성공했을 때 커밋하겠다. 한쪽만 반영되면 예치금 합계가 바뀌기 때문이다. 실험에서는 판매자 잔액을 먼저 늘린 뒤 구매자의 음수 잔액 제약 위반을 발생시키고, 롤백 후 두 잔액이 시작 값으로 돌아오는지 확인한다. 다만 외부 결제 요청까지 데이터베이스 롤백으로 취소되지는 않으므로, 그 경우에는 별도의 상태 관리와 보상 처리가 필요하다.

면접관이 “CHECK 제약이 있으면 충분한가”라고 물을 수 있다. 잔액의 음수를 막는 제약은 유용하지만 잘못된 상대방에게 같은 금액을 이체하는 오류까지 찾지는 못한다. 데이터베이스가 표현할 수 있는 규칙은 제약으로 두고, 거래 당사자와 금액을 결정하는 로직도 올바르게 작성해야 한다. ACID의 일관성을 모든 업무 규칙의 자동 검증으로 설명하면 안 된다.

깊게 답하기: 격리 수준을 높이면 문제가 모두 해결되는가

동시에 실행되는 거래에서는 다른 작업의 어느 상태를 읽을 수 있는지가 중요하다. 더티 읽기(dirty read)는 다른 트랜잭션이 아직 커밋하지 않은 변경을 읽는 현상이다. 반복 불가능 읽기(non-repeatable read)는 같은 행을 다시 읽었을 때 다른 트랜잭션의 커밋으로 값이 달라지는 현상이다. 팬텀 읽기(phantom read)는 같은 조건으로 다시 조회했을 때 조건에 해당하는 행 집합이 달라지는 현상이다.

예를 들어 같은 상품의 가격이 바뀌어 보이는 것은 행의 값 변화이며, “판매 중인 상품”을 다시 조회했을 때 새 상품이 추가되어 보이는 것은 조건에 해당하는 집합의 변화다. 두 현상을 구분하면 격리 수준을 외운 표보다 구체적으로 설명할 수 있다.

표준적인 격리 수준과 읽기 이상 현상의 관계

격리 수준	더티 읽기	반복 불가능 읽기	팬텀 읽기
커밋 전 읽기	허용 가능	허용 가능	허용 가능
커밋된 읽기	방지	허용 가능	허용 가능
반복 가능한 읽기	방지	방지	허용 가능
직렬화 가능	방지	방지	방지

이 표는 표준적인 구분이며 제품의 동작 전체를 설명하는 표는 아니다. 같은 이름의 격리 수준에서도 스냅샷과 잠금 구현에 따라 더 강한 보장을 제공할 수 있다. 스냅샷은 일정한 시점의 데이터를 읽게 하는 방식이며, 잠금은 충돌하는 접근을 제한하는 방식이다. 실제 답변에서는 사용하는 엔진과 설정을 밝히고 확인한 동작을 설명한다.

나쁜 답: 격리 수준을 가장 높이면 동시성 문제를 신경 쓰지 않아도 된다.

보통 답: 격리 수준이 높으면 이상 현상이 줄지만 동시 처리 비용이 늘 수 있다.

좋은 답: 먼저 막아야 할 충돌을 정하고 그에 맞는 갱신 방법과 격리 수준을 선택하겠다. 상품 하나의 중복 판매라면 판매 중인 행만 예약 상태로 바꾸는 조건부 갱신을 하고, 바뀐 행 수가 하나인지 확인할 수 있다. 이 예에서는 먼저 읽은 상태를 무조건 덮어쓰는 것보다 판단과 변경을 한 SQL에 담는 편이 적절하다. 다만 여러 행에 걸친 규칙까지 이 방법 하나로 보장하지는 못한다. 직렬화 가능 수준을 사용하더라도 충돌에 따른 트랜잭션 실패와 재시도를 처리해야 한다.

꼬리 질문으로 “SQLite에서도 이 네 수준을 모두 선택할 수 있는가”가 나오면 구분해서 답한다. SQLite를 일반적인 서버형 데이터베이스의 네 수준 선택 방식과 같다고 설명하지 않는다. 기본적으로 연결 간 미커밋 변경을 보이지 않게 하며, 저널 모드와 연결 설정에 따라 동시 읽기·쓰기 동작이 달라진다. 아래 실험은 연결 하나에서 원자성과 롤백을 확인할 뿐, 여러 연결의 격리 이상 현상을 재현하지 않는다.

정규화와 조인은 데이터의 의미에서 출발한다

정규화(normalization)는 중복을 줄이기 위해 무조건 테이블을 잘게 나누는 작업이 아니다. 어떤 속성이 어떤 키에 의해 결정되는지, 즉 함수 종속을 기준으로 사실을 배치하는 과정이다. 상품마다 판매자 닉네임을 복사하면 판매자 한 명이 이름을 바꿀 때 여러 상품을 수정해야 한다. 일부만 바뀌면 같은 판매자의 현재 이름이 상품마다 달라지는 갱신 이상이 생긴다.

판매자 정보를 상품에만 저장하면 상품이 없는 판매자를 표현하기 어렵다는 삽입 이상도 생긴다. 마지막 상품을 지울 때 판매자 정보까지 사라지는 삭제 이상도 생길 수 있다. 판매자 번호가 현재 닉네임을 결정한다면 판매자 테이블에 그 사실을 두고, 상품 테이블은 판매자 번호를 참조하도록 설계하는 편이 자연스럽다.

첫째 정규형은 각 속성에 해당 도메인의 단일 값을 두고 반복 묶음을 피하는 데서 출발한다. 둘째 정규형은 복합 후보 키 일부에만 종속되는 비주요 속성을 분리한다. 셋째 정규형은 비자명한 함수 종속에서 결정자가 슈퍼키이거나 종속 속성이 후보 키에 포함되는 조건을 요구한다. 단순한 예에서는 판매자 번호를 거쳐 결정되는 판매자 이름을 상품 테이블에서 분리하는 방식으로 이해할 수 있다.

분리한 사실을 함께 읽을 때 조인(join)을 사용한다. 내부 조인은 조건에 맞는 양쪽 행을 결합한다. 왼쪽 외부 조인은 대응 행이 없어도 왼쪽 행을 남기고 오른쪽 열을 NULL로 채운다. 오른쪽 외부 조인과 완전 외부 조인은 각각 오른쪽 또는 양쪽의 대응 없는 행을 보존한다. 교차 조인은 양쪽 행의 모든 조합을 만들며, 자기 조인은 같은 테이블을 서로 다른 역할로 결합하는 사용 방식이다.

조인의 이름보다 결과가 몇 행이 되는지 먼저 생각해야 한다. 상품 하나에 사진 세 개를 조인하면 상품 정보도 세 행에 반복될 수 있다. 이 결과에 곧바로 LIMIT를 적용하면 상품 세 개가 아니라 사진과 결합된 행 세 개를 얻는다. 목록의 페이지 경계를 먼저 정하거나 사진을 별도로 묶어 읽는 등 결과 단위를 보존하는 설계가 필요하다.

깊게 답하기: 정규화하면 조회가 느려지는가

나쁜 답: 조인이 느리므로 한 테이블에 모두 넣겠다.

보통 답: 정규화는 중복을 줄이고 반정규화는 조회 성능을 높이는 데 쓴다.

좋은 답: 먼저 데이터가 뜻하는 사실을 기준으로 정규화하겠다. 판매자의 현재 닉네임은 판매자에게 속하고 상품 가격은 상품에 속하기 때문이다. 상품 목록에서는 조인이나 일괄 조회로 두 정보를 함께 읽을 수 있다. 조회가 병목이라면 실행 계획과 접근 횟수를 확인한 뒤 반정규화를 검토하겠다. 중복 값을 둘 때는 어느 값이 기준인지, 언제 갱신하고 어긋난 값을 어떻게 복구할지까지 정하겠다.

반정규화(denormalization)는 읽기 비용을 줄이기 위해 중복이나 미리 계산한 값을 의도적으로 두는 선택이다. 판매자의 현재 닉네임을 상품에 복사한다면 변경 시 여러 행을 동기화해야 한다. 반면 거래 당시 닉네임을 거래 내역에 보존하는 것은 현재 닉네임의 복사본과 의미가 다르다. 과거 사실을 보존하는 요구인지 현재 값을 빠르게 읽으려는 요구인지부터 구분한다.

면접관이 “상품마다 판매자를 한 번씩 읽으면 되는가”라고 물으면 N+1 문제를 연결한다. 목록을 한 번 조회하고 N개 상품마다 판매자를 추가 조회하면 총 1+N번의 조회가 발생한다. 각 조회가 빨라도 반복되는 접근 비용이 커질 수 있다. 이 문제는 객체 관계 매핑 도구에만 있는 것이 아니라 Python 반복문에서 직접 SQL을 실행해도 생긴다.

해결책은 필요한 판매자를 조인하거나 판매자 번호를 모아 한 번에 읽는 것이다. 일괄 조회 결과를 사전으로 만들어 상품과 연결할 수도 있다. 같은 판매자가 반복되는 목록이라면 중복 번호를 제거할 수 있다. 다만 IN 조건의 매개변수 수 제한과 일대다 조인의 행 증가를 고려해야 한다. “쿼리는 한 번일수록 좋다” 대신 전체 비용과 결과 의미를 함께 설명한다.

사실 확인이 필요하면 SQLite 실행 계획 안내, SQLite 격리 동작 안내, Python sqlite3 안내에서 사용 중인 기능의 범위를 확인할 수 있다.

완성 코드

다음 내용을 main.py로 저장한다. 상품 240개와 예치금 두 건을 메모리 데이터베이스에 만든다. 난수와 현재 시각을 사용하지 않으며, 같은 연결에서 같은 조회를 인덱스 전후로 실행한다. 실행 계획의 표현 전체를 고정하지 않고 필요한 특징을 확인한 뒤 정해진 문장을 출력한다. 예상하지 못한 계획이 나오면 성공처럼 출력하지 않고 오류를 발생시킨다.

import sqlite3


QUERY = """
SELECT id, title, price
FROM listing
WHERE seller_id = ? AND status = ?
ORDER BY id DESC
LIMIT 3
"""
PARAMS = (2, "active")
INDEX_NAME = "idx_listing_seller_status_id"


def seed(conn):
    conn.executescript("""
        CREATE TABLE listing (
            id INTEGER PRIMARY KEY,
            seller_id INTEGER NOT NULL,
            status TEXT NOT NULL,
            title TEXT NOT NULL,
            price INTEGER NOT NULL CHECK (price >= 0)
        );
        CREATE TABLE wallet (
            owner TEXT PRIMARY KEY,
            balance INTEGER NOT NULL CHECK (balance >= 0)
        );
    """)
    products = [
        (
            number,
            number % 5,
            "active" if number % 2 == 0 else "sold",
            f"상품-{number:03d}",
            10000 + number * 10,
        )
        for number in range(1, 241)
    ]
    conn.execute("BEGIN")
    try:
        conn.executemany(
            "INSERT INTO listing VALUES (?, ?, ?, ?, ?)",
            products,
        )
        conn.executemany(
            "INSERT INTO wallet VALUES (?, ?)",
            [("buyer", 2000), ("seller", 5000)],
        )
        conn.execute("COMMIT")
    except Exception:
        conn.execute("ROLLBACK")
        raise


def inspect_plan(conn, indexed):
    details = [
        row[3]
        for row in conn.execute(
            "EXPLAIN QUERY PLAN " + QUERY, PARAMS
        )
    ]
    if indexed:
        expected = any(
            "SEARCH listing" in detail and INDEX_NAME in detail
            for detail in details
        )
        label = "listing 인덱스 탐색"
    else:
        expected = any(
            "SCAN listing" in detail
            for detail in details
        )
        label = "listing 전체 탐색"

    if not expected:
        raise RuntimeError(f"예상과 다른 접근 경로: {details}")
    if any("TEMP B-TREE" in detail for detail in details):
        raise RuntimeError(f"별도 정렬 발견: {details}")
    return label


def show_rows(rows):
    for product_id, title, price in rows:
        print(f"{product_id} | {title} | {price}")


def balances(conn):
    return dict(
        conn.execute(
            "SELECT owner, balance FROM wallet ORDER BY owner"
        ).fetchall()
    )


def show_balances(label, values):
    print(
        f"{label}: 구매자={values['buyer']}, "
        f"판매자={values['seller']}"
    )


def rollback_demo(conn):
    before = balances(conn)
    show_balances("거래 전", before)
    conn.execute("BEGIN")
    try:
        conn.execute(
            "UPDATE wallet SET balance = balance + ? "
            "WHERE owner = ?",
            (3000, "seller"),
        )
        show_balances("첫 갱신 후", balances(conn))
        conn.execute(
            "UPDATE wallet SET balance = balance - ? "
            "WHERE owner = ?",
            (3000, "buyer"),
        )
        conn.execute("COMMIT")
    except sqlite3.IntegrityError:
        conn.execute("ROLLBACK")
        print("구매자 잔액 제약 위반: 전체 롤백")
    except Exception:
        conn.execute("ROLLBACK")
        raise
    else:
        raise AssertionError("이 실험은 잔액 부족으로 실패해야 한다.")

    after = balances(conn)
    show_balances("롤백 후", after)
    print(f"원래 잔액 복원: {after == before}")


def main():
    conn = sqlite3.connect(":memory:", isolation_level=None)
    try:
        seed(conn)

        print("[인덱스 전]")
        print("계획:", inspect_plan(conn, indexed=False))
        before = conn.execute(QUERY, PARAMS).fetchall()
        show_rows(before)

        conn.execute(
            f"CREATE INDEX {INDEX_NAME} "
            "ON listing(seller_id, status, id DESC)"
        )

        print("[인덱스 후]")
        print("계획:", inspect_plan(conn, indexed=True))
        after = conn.execute(QUERY, PARAMS).fetchall()
        show_rows(after)
        print(f"조회 결과 동일: {before == after}")

        print("[트랜잭션]")
        rollback_demo(conn)
    finally:
        conn.close()


if __name__ == "__main__":
    main()

줄별 해설

1행과 QUERY 정의에서는 표준 라이브러리만 불러오고 비교할 SQL을 한 번 정의한다. 판매자와 상태를 매개변수로 전달하므로 인덱스 전후에 조회 조건이 달라지지 않는다. ORDER BY의 기준인 id가 고유하므로 결과 순서에도 동률이 없다.

seed 함수의 테이블 생성 부분은 가격과 잔액에 음수를 허용하지 않는 CHECK 제약을 둔다. 예시를 작게 유지하기 위해 판매자 정보 테이블은 만들지 않았다. 따라서 이 코드는 정규화된 서비스 스키마 전체나 외래 키 검증 실험이 아니다.

products를 만드는 부분에서는 상품 번호로 판매자와 상태를 결정한다. 판매자 2의 판매 중인 상품은 번호가 2, 12, 22처럼 증가한다. 240 이하에서 가장 큰 세 번호는 232, 222, 212다. 생성 규칙이 고정되어 결과를 손으로도 확인할 수 있다.

seed의 BEGIN부터 COMMIT까지는 초기 데이터 입력을 한 트랜잭션으로 묶는다. 중간에 오류가 발생하면 입력을 롤백하고 원래 예외를 다시 전달한다. 테이블 생성에 사용한 executescript는 초기 설정 단계에서만 호출하며, 거래 실험의 트랜잭션 안에서는 사용하지 않는다.

inspect_plan 함수는 EXPLAIN QUERY PLAN 결과의 네 번째 열인 설명 문자열을 읽는다. 인덱스 전에는 SCAN listing, 인덱스 후에는 SEARCH listing과 지정한 인덱스 이름을 함께 확인한다. 별도 정렬의 흔적인 TEMP B-TREE가 나타나면 오류로 알린다. 이 조회는 인덱스가 없을 때도 정수 기본 키의 역순으로 순회할 수 있어 별도 정렬이 필요하지 않다.

show_rows와 balances 함수는 출력과 상태 확인을 담당한다. fetchall로 결과를 모두 읽은 뒤 다음 작업으로 넘어간다. balances는 소유자 이름을 키로 하는 사전을 반환하므로 거래 전후 상태를 값으로 비교할 수 있다.

rollback_demo의 첫 UPDATE는 판매자에게 3,000을 더한다. 같은 연결에서 읽으면 아직 커밋하지 않은 변경도 보이므로 판매자 잔액이 8,000으로 출력된다. 이것은 다른 연결에서의 더티 읽기를 보여 주는 것이 아니다.

두 번째 UPDATE와 예외 처리에서는 구매자 잔액이 음수가 되어 제약 위반이 발생한다. 이 제약 위반만으로 앞의 성공한 UPDATE까지 취소되었다고 가정하지 않는다. 예외 처리에서 ROLLBACK을 실행해 두 작업을 묶은 경계 전체를 취소한다.

main의 연결 생성에서 isolation_level=None은 기존 방식의 암묵적 트랜잭션 시작을 끄고 코드의 BEGIN·COMMIT·ROLLBACK으로 경계를 관리하기 위한 설정이다. SQL의 격리 수준을 “없음”으로 낮춘다는 뜻이 아니다. Python 3.12 이상에서 이 예시는 해당 방식으로 동작하며, 연결은 finally에서 닫는다.

실행 결과

macOS 또는 Linux에서 다음 명령을 실행한다.

python3 main.py

예상 출력은 다음과 같다. 계획 행은 원문 전체가 아니라 코드가 확인한 접근 경로의 요약이다. 실행 시간과 현재 시각을 출력하지 않으므로 정상 실행의 출력은 일정하다.

[인덱스 전]
계획: listing 전체 탐색
232 | 상품-232 | 12320
222 | 상품-222 | 12220
212 | 상품-212 | 12120
[인덱스 후]
계획: listing 인덱스 탐색
232 | 상품-232 | 12320
222 | 상품-222 | 12220
212 | 상품-212 | 12120
조회 결과 동일: True
[트랜잭션]
거래 전: 구매자=2000, 판매자=5000
첫 갱신 후: 구매자=2000, 판매자=8000
구매자 잔액 제약 위반: 전체 롤백
롤백 후: 구매자=2000, 판매자=5000
원래 잔액 복원: True

인덱스 전후의 세 상품이 같다는 것은 이 입력에서 조회 결과가 보존되었다는 뜻이다. 접근 경로가 바뀌었다는 것은 조건을 활용하는 경로가 선택되었다는 뜻이다. 두 관찰을 합쳐도 실제 운영 부하에서의 지연 시간 개선율까지 계산할 수는 없다.

롤백 출력에서는 한 번 관찰된 판매자 잔액 증가가 취소되었음을 확인한다. 데이터베이스를 메모리에 만들었으므로 프로그램 종료 후 데이터는 사라진다. 파일 저장, 장애 복구, 지속성, 여러 연결의 동시 실행은 이 실험이 검증하는 범위에 포함되지 않는다.

실무에서 자주 틀리는 것

예외를 출력하고 앞의 변경을 남긴다

다음 코드는 두 번째 변경이 실패해도 첫 번째 변경을 취소하지 않는다. 이후 코드가 커밋하면 불완전한 거래가 남을 수 있다. 아래 조각은 완성 코드와 같은 명시적 트랜잭션 관리 설정을 전제로 한다.

conn.execute("BEGIN")
try:
    conn.execute(
        "UPDATE wallet SET balance = balance + 3000 "
        "WHERE owner = 'seller'"
    )
    conn.execute(
        "UPDATE wallet SET balance = balance - 3000 "
        "WHERE owner = 'buyer'"
    )
except sqlite3.IntegrityError:
    print("거래 실패")

성공하면 커밋하고 실패하면 롤백한다. 예외를 다시 전달할지는 호출자가 실패를 처리하는 방식에 맞춰 정한다.

conn.execute("BEGIN")
try:
    conn.execute(
        "UPDATE wallet SET balance = balance + ? WHERE owner = ?",
        (3000, "seller"),
    )
    conn.execute(
        "UPDATE wallet SET balance = balance - ? WHERE owner = ?",
        (3000, "buyer"),
    )
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise

상품마다 판매자를 다시 조회한다

상품 N개를 읽은 뒤 반복문에서 판매자를 읽으면 조회 횟수가 1+N이 된다. 다음 조각은 seller 테이블이 별도로 있는 서비스 코드의 예다.

products = conn.execute(
    "SELECT id, seller_id FROM listing ORDER BY id DESC LIMIT 20"
).fetchall()
for product_id, seller_id in products:
    seller = conn.execute(
        "SELECT nickname FROM seller WHERE id = ?",
        (seller_id,),
    ).fetchone()

판매자 번호가 기본 키라면 상품과 판매자의 관계는 다대일이다. 이 경우 필요한 열을 조인으로 함께 읽어 조회 반복을 줄일 수 있다. 연결된 판매자가 없는 상품도 표시할 요구가 있다면 외부 조인을 검토한다.

rows = conn.execute("""
    SELECT p.id, s.nickname
    FROM listing AS p
    JOIN seller AS s ON s.id = p.seller_id
    ORDER BY p.id DESC
    LIMIT 20
""").fetchall()

외부 조인한 테이블의 조건을 WHERE에 둔다

사진이 없는 상품도 남기고 대표 사진이 있으면 붙이려는 조회다. 하지만 오른쪽 열의 조건을 WHERE에 두면 사진이 없는 행은 NULL 비교 때문에 제거된다.

SELECT p.id, photo.path
FROM listing AS p
LEFT JOIN photo ON photo.listing_id = p.id
WHERE photo.is_main = 1;

대표 사진 조건을 결합 조건으로 옮기면 대응 사진이 없는 상품도 남는다. 상품별 대표 사진을 하나로 제한하는 규칙은 별도로 필요하다.

SELECT p.id, photo.path
FROM listing AS p
LEFT JOIN photo
  ON photo.listing_id = p.id
 AND photo.is_main = 1;

읽은 상태를 믿고 무조건 갱신한다

판매 중인지 읽은 뒤 조건 없이 예약하면, 읽기와 쓰기 사이에 다른 거래가 상태를 바꿀 수 있다. 다음 조각은 조회 결과가 있다고 가정한다.

status = conn.execute(
    "SELECT status FROM listing WHERE id = ?", (232,)
).fetchone()[0]
if status == "active":
    conn.execute(
        "UPDATE listing SET status = 'reserved' WHERE id = ?",
        (232,),
    )

현재 상태 조건을 UPDATE에 포함하고 변경한 행 수를 확인한다. 이후 거래 정보 저장까지 필요하다면 같은 트랜잭션으로 묶는다. 잠금 충돌이나 트랜잭션 실패를 처리하는 일도 별도로 남는다.

cursor = conn.execute(
    "UPDATE listing SET status = ? WHERE id = ? AND status = ?",
    ("reserved", 232, "active"),
)
if cursor.rowcount != 1:
    raise ValueError("예약할 수 없는 상품이다.")

한눈에 보기

면접 답변에 함께 넣어야 할 근거와 한계

주제	핵심 설명	확인할 근거	빠뜨리기 쉬운 한계
인덱스	조건에 맞는 탐색 범위와 정렬 비용을 줄인다.	조회 조건, 열 순서, 실행 계획	공간과 쓰기 비용이 늘며 항상 선택되지는 않는다.
실행 계획	엔진이 선택한 접근 경로를 읽는다.	SCAN·SEARCH, 인덱스 이름, 임시 정렬	접근 경로만으로 응답 시간을 증명하지 않는다.
트랜잭션	함께 성공해야 할 변경을 하나로 묶는다.	커밋 위치와 롤백 후 상태	외부 작업까지 자동으로 취소되지 않는다.
격리 수준	동시 거래의 간섭을 제한한다.	허용하는 이상 현상과 엔진의 구현	충돌 실패와 재시도가 남을 수 있다.
정규화	함수 종속에 맞춰 사실을 배치한다.	삽입·갱신·삭제 이상	테이블 수만으로 적절함을 판단하지 않는다.
조인과 N+1	필요한 정보를 의미와 비용에 맞게 결합한다.	결과 행 수와 전체 조회 횟수	일대다 결합은 행과 페이지 경계를 바꾼다.
반정규화	측정한 읽기 비용을 줄이기 위해 중복을 둔다.	병목과 갱신·복구 정책	원본과 복사본의 정합성을 관리해야 한다.

연습 문제

1. 완성 코드의 인덱스에서 price가 빠져 있다. 인덱스 사용만으로 테이블 접근이 없어졌다고 말할 수 있는가. title과 price를 뒤에 추가했을 때의 이점과 비용을 설명하라.
2. 완성 코드에서 IntegrityError 처리 부분의 ROLLBACK을 제거하면 앞의 판매자 갱신은 어떻게 되는가. “두 번째 SQL 실패”, “미커밋 상태”, “연결 종료”를 구분해 설명하라.
3. 상품 20개와 각 상품의 사진 3개를 한 번에 조인하고 LIMIT 20을 적용했다. 상품 20개가 나온다고 보장할 수 있는가. 대안을 두 가지 제시하라.
4. 거래 내역에 저장한 판매자 닉네임을 판매자가 이름을 바꿀 때마다 갱신해야 한다는 주장이 있다. 요구 사항에 따라 답이 달라지는 이유를 설명하고 면접 답변을 네 문장으로 작성하라.

정답과 해설

1. 말할 수 없다. 현재 인덱스로 조건과 정렬을 처리하더라도 title과 price를 얻기 위한 테이블 접근이 필요하다. 두 열을 인덱스 뒤에 포함하면 이 조회에 필요한 값을 인덱스에서 얻는 경로를 선택할 수 있다. 대신 인덱스가 커지고 제목이나 가격을 수정할 때도 유지 비용이 든다. 확장한 인덱스의 실행 계획과 실제 부하를 확인한 뒤 선택한다.

2. 이 사례의 CHECK 위반은 실패한 두 번째 문장을 취소하지만, 앞에서 성공한 판매자 갱신까지 자동으로 취소하지 않는다. 명시적으로 시작한 트랜잭션은 미커밋 상태로 남고 같은 연결에서는 판매자 증가가 보인다. 이후 커밋하면 그 증가만 저장될 수 있다. 미커밋 상태로 연결이 닫히면 변경은 보존되지 않지만, 연결 종료에 처리를 맡기는 대신 실패 지점에서 롤백해야 거래 경계가 분명해진다.

3. 보장할 수 없다. LIMIT는 조인 결과의 행 수를 제한하며, 상품 하나가 사진 수만큼 반복될 수 있다. 첫째, 하위 조회나 공통 테이블 식으로 상품 20개의 경계를 먼저 정하고 사진을 결합할 수 있다. 둘째, 상품 20개를 먼저 읽고 그 상품 번호의 사진을 한 번에 읽어 애플리케이션에서 묶을 수 있다. 두 번째 방식은 조회 두 번으로 해결하므로 항목마다 사진을 읽는 N+1과 구분된다.

4. “거래 당시 이름을 보존하는 요구라면 현재 이름으로 갱신하지 않겠다. 거래 시점의 이름과 현재 이름은 서로 다른 사실이기 때문이다. 예를 들어 거래 확인 화면은 저장한 당시 이름을, 판매자 프로필은 현재 이름을 보여 줄 수 있다. 반대로 현재 이름의 복사본을 조회 성능 때문에 둔 것이라면 원본 변경에 맞춘 동기화와 복구 정책을 마련하겠다.” 결론, 근거, 예시, 조건에 따른 대안이 연결된 답변이다.