데이터베이스 - 정규화·인덱스·트랜잭션
이 장에서 배우는 것
앞 장에서 요청이 서버에 도착하는 과정을 살펴보았다. 서버가 요청을 처리하려면 사용자의 학습 기록을 읽거나 바꿔야 한다. 이때 필요한 것은 값을 저장하는 공간만이 아니다. 같은 과목의 이름이 서로 다르게 남지 않게 하고, 필요한 기록을 적은 비용으로 찾으며, 여러 변경이 도중에 끊겨도 정해 둔 규칙을 지켜야 한다. 데이터베이스는 이 세 문제를 함께 다루는 과목이다.
이 장에서는 전공 과목별 학습 계획을 저장하는 작은 프로그램을 만든다. 관계형 모델로 기록의 모양을 정하고, 정규화로 중복을 줄이고, 인덱스로 조회 경로를 바꾼다. 마지막에는 두 변경 가운데 하나가 실패했을 때 앞선 변경까지 취소되는지 확인한다. 목표는 데이터베이스 제품의 기능을 외우는 것이 아니라, 저장 구조와 조회 비용과 변경의 경계를 설명할 수 있게 되는 것이다.
- 표 사이의 관계를 기본 키와 외래 키로 표현한다.
- 중복된 사실을 분리하는 정규화의 이유를 설명한다.
- 인덱스 유무에 따라 실행 계획이 어떻게 달라지는지 확인한다.
- 트랜잭션과 ACID를 구분하고 롤백을 코드로 검증한다.
문제 상황
다른 전공을 공부하다 개발 일을 시작한 지윤은 동료들과 함께 전공 과목별 학습 현황을 기록한다. 처음에는 한 표에 학습자 이름, 이전 전공, 과목 이름, 과목 설명, 학습 상태를 모두 적었다. 데이터베이스 과목을 공부하는 사람이 늘자 같은 과목 설명이 여러 행에 반복되었다. 과목 이름을 고칠 때 일부 행만 수정되어 같은 과목이 서로 다른 이름으로 보이기도 했다.
조회에도 문제가 생겼다. 화면에서는 “데이터베이스를 아직 시작하지 않은 사람”만 필요하지만, 기록이 쌓일수록 조건에 맞는 행을 찾는 일이 늘어났다. 목록을 만든 뒤에는 학습 상태를 바꾸고 다음 과목을 등록하는 기능도 추가되었다. 두 변경 중 하나만 반영되면 실제 계획과 저장된 기록이 어긋날 수 있었다.
이 상황은 세 가지 질문으로 나뉜다. 하나의 사실을 어디에 저장할 것인가, 필요한 행을 어떤 경로로 찾을 것인가, 함께 성공해야 하는 변경을 어디까지 묶을 것인가다. 전공 수업에서는 각각 데이터 모델링과 정규화, 질의 처리와 인덱스, 트랜잭션 관리라는 주제로 다룬다. 실무에서는 기능 하나를 만드는 동안 이 질문들이 함께 나타난다.
관계형 모델과 정규화: 사실을 어디에 둘 것인가
관계형 모델(relational model)은 데이터를 속성이 정해진 관계로 표현하는 방식이다. 이 장에서는 관계를 표로 생각해도 된다. 행은 하나의 기록이고 열은 그 기록의 속성이다. 전공 수업에서는 행을 튜플, 열을 애트리뷰트라고도 부른다. 다만 표에 보이는 행 순서 자체에는 의미가 없다. 결과 순서가 필요하면 조회문에서 따로 지정해야 한다.
데이터를 조회하거나 바꾸는 언어가 SQL이다. Python의 sqlite3 모듈을 사용하면 별도 서버 없이 SQLite 데이터베이스에 SQL을 실행할 수 있다. 이 장에서는 실행할 때마다 새로 만들어지고 종료할 때 사라지는 메모리 데이터베이스를 사용한다. 파일 관리 없이 구조와 동작에 집중하기 위한 선택이다.
학습 기록은 학습자, 과목, 학습 현황으로 나눈다. 학습자 한 명은 여러 과목을 공부할 수 있고, 과목 하나도 여러 학습자에게 연결된다. 이를 다대다 관계라고 한다. 학습 현황 표가 두 대상을 연결하면서, 그 조합에 속하는 상태를 저장한다.
| 표 | 저장하는 사실 | 행을 구별하는 값 |
|---|---|---|
| learners | 학습자 이름과 이전 전공 | learner_id |
| courses | 과목 이름 | course_id |
| progress | 학습자별 과목 상태 | learner_id와 course_id의 조합 |
행을 유일하게 구별하도록 선택한 열 또는 열의 조합을 기본 키(primary key)라고 한다. 이름은 동명이인이 있을 수 있으므로 학습자의 기본 키로 쓰지 않는다. 학습 현황에서는 학습자 번호와 과목 번호를 함께 기본 키로 삼는다. 이렇게 여러 열을 묶은 키를 복합 키라고 한다. 같은 사람이 같은 과목에 두 번 등록되는 것을 이 조합으로 막는다.
다른 표의 행을 가리키는 값에는 외래 키(foreign key)를 설정한다. 학습 현황의 과목 번호는 실제 과목 표에 있어야 한다. 이처럼 참조하는 대상이 존재하도록 지키는 규칙을 참조 무결성이라고 한다. SQLite에서는 연결마다 외래 키 검사를 켜야 하므로 완성 코드에 설정을 명시한다.
중복을 줄이도록 표의 구조를 정리하는 일을 정규화(normalization)라고 한다. 핵심은 표를 많이 만드는 것이 아니라, 어떤 값이 어떤 값을 결정하는지 확인하는 것이다. 과목 번호가 정해지면 과목 이름도 정해진다. 전공 수업에서는 이런 관계를 함수 종속이라고 부른다. 반면 학습 상태는 과목 번호만으로 정해지지 않는다. 학습자 번호까지 알아야 한다.
학습 현황에 과목 이름까지 넣으면 과목 이름이 복합 키의 일부인 과목 번호에만 달라붙는다. 이를 부분 함수 종속이라고 한다. 이 중복 때문에 이름 변경 시 여러 행을 수정해야 하는 갱신 이상이 생긴다. 아직 아무도 등록하지 않은 과목을 넣기 어려운 삽입 이상, 마지막 학습 기록을 지웠더니 과목 정보까지 사라지는 삭제 이상도 나타날 수 있다.
전공 수업에서 말하는 제1정규형은 한 칸에 여러 값을 묶어 넣지 않도록 하는 출발점이다. 예를 들어 과목 번호를 “DB, OS”라는 문자열 하나로 저장하지 않는다. 제2정규형은 복합 키의 일부에만 종속되는 일반 속성을 분리한다. 제3정규형은 키가 아닌 속성을 거쳐 다른 속성이 정해지는 간접적인 종속도 정리한다. 여기서는 과목 이름을 과목 표로 옮기는 결정이 정규화의 효과를 보여 준다.
분리한 표를 다시 연결해서 읽는 연산을 조인(join)이라고 한다. 완성 코드의 첫 조회는 학습 현황과 과목 표를 연결한다. 과목 이름을 여러 행에 복사하지 않아도 “데이터베이스: planned”라는 결과를 얻는다. 저장할 때 중복을 줄이는 선택과 읽을 때 연결하는 선택은 함께 설계해야 한다.
인덱스와 조회 비용: 무엇을 덜 읽을 것인가
인덱스(index)는 조건에 맞는 데이터의 위치를 찾도록 돕는 별도 구조다. 책의 찾아보기처럼 탐색의 출발점을 제공하지만, 내용이 바뀔 때 함께 관리해야 한다. 완성 코드에서는 학습자 200명이 과목 네 개에 등록한 800행을 만든다. 이 가운데 과목 번호가 DB이고 상태가 planned인 학습자는 200명이다.
인덱스가 없으면 데이터베이스는 학습 현황을 훑으며 조건을 검사하는 경로를 선택할 수 있다. 이를 전체 스캔이라고 한다. 조건에 맞는 인덱스가 있으면 값의 범위를 찾아 들어가는 인덱스 탐색을 사용할 수 있다. 전공 수업에서는 이러한 접근 경로를 포함한 수행 방법을 실행 계획이라고 부른다.
기본 키도 조회에 도움을 줄 수 있지만 열 순서를 봐야 한다. 학습 현황의 기본 키는 학습자 번호, 과목 번호 순서다. 특정 학습자의 과목을 찾는 데는 어울리지만, 전체 학습자 중 특정 과목과 상태를 찾는 조건을 그대로 지원하는 구조는 아니다. 실험에서는 과목 번호, 상태 순서의 별도 인덱스를 추가한다.
여러 열을 묶은 인덱스를 복합 인덱스라고 한다. 보통 앞쪽 열부터 조건이 주어지는 조회에 활용하기 좋다. 이 말이 뒤쪽 열만 쓰면 어떤 데이터베이스에서도 활용할 수 없다는 뜻은 아니다. 실제 선택은 데이터 분포, 통계와 구현에 따라 달라진다. 따라서 규칙을 외우는 데서 멈추지 말고 실행 계획을 확인해야 한다.
| 관찰 대상 | 인덱스 없음 | 조건에 맞는 인덱스 있음 |
|---|---|---|
| 후보를 찾는 경로 | 표를 훑는 경로 | 인덱스의 값 범위 탐색 |
| 이 실험의 결과 건수 | 200명 | 200명 |
| 추가 저장 공간 | 별도 인덱스 없음 | 인덱스 공간 필요 |
| 값 변경 시 할 일 | 표의 기록 갱신 | 표와 관련 인덱스 갱신 |
반환한 행 수와 검사한 후보 수는 다르다. 800행을 확인해서 200행을 반환할 수도 있고, 인덱스에서 해당 범위를 찾은 뒤 필요한 행을 읽을 수도 있다. 조회 비용은 반환 건수 하나로 설명되지 않는다. 저장 장치에서 읽는 페이지 수, 추가로 표에 접근하는 횟수, 정렬 같은 작업도 영향을 준다. 페이지는 데이터베이스가 데이터를 묶어 관리하는 단위다.
많은 관계형 데이터베이스는 균형을 유지하는 트리 계열 구조로 인덱스를 구현한다. 트리는 값을 찾는 경로가 한쪽으로 길게 늘어지지 않도록 관리된다. 다만 인덱스가 있다고 매번 빨라지는 것은 아니다. 대부분의 행을 읽는 조회라면 표를 순서대로 읽는 편이 나을 수도 있다. 쓰기가 많은 표에서는 인덱스 유지 비용도 고려해야 한다.
이 장은 실행 시간을 재지 않는다. 작은 메모리 데이터에서는 실행 순서와 주변 작업이 시간 측정에 크게 섞이기 때문이다. 대신 EXPLAIN QUERY PLAN으로 조회 방법을 확인한다. 이 명령은 조회 계획을 보여 주며, 실제로 읽은 페이지 수를 측정해 주는 명령은 아니다. 프로그램은 버전마다 달라질 수 있는 상세 문장 전체 대신 SCAN과 SEARCH라는 핵심 표시를 출력한다.
트랜잭션과 ACID: 어디까지 함께 바꿀 것인가
트랜잭션(transaction)은 함께 성공하거나 함께 취소할 작업의 묶음이다. 학습 계획을 바꾸는 요청 하나가 여러 SQL 문을 실행하더라도, 업무상 하나의 변경이라면 같은 트랜잭션에 담을 수 있다. BEGIN으로 시작하고 COMMIT으로 확정하며 ROLLBACK으로 취소한다. 확정하기 전의 변경도 같은 연결에서는 읽을 수 있다는 점을 구별해야 한다.
완성 코드에서는 데이터베이스 과목을 done으로 바꾼 뒤, 운영체제 과목에 허용하지 않은 상태를 넣는다. 표에는 planned와 done만 허용하는 CHECK 제약 조건이 있다. 제약 조건은 데이터가 지켜야 할 규칙을 데이터베이스에 선언한 것이다. 두 번째 변경이 실패하면 Python에서 오류를 받아 전체 트랜잭션을 명시적으로 되돌린다.
트랜잭션의 성질을 묶어 부르는 말이 ACID다. 원자성은 묶음이 일부만 반영된 상태로 끝나지 않게 하는 성질이다. 일관성은 선언된 제약과 올바른 업무 규칙을 지키는 상태 사이에서 변경이 이루어지는 성질이다. 격리성은 동시에 실행되는 트랜잭션이 서로에게 어떤 변경을 보여 주는지 통제하는 성질이다. 지속성은 확정된 변경을 장애 이후에도 보존하는 성질이다.
일관성은 데이터베이스가 업무 의도를 모두 알아서 판단한다는 뜻이 아니다. “학습 상태는 두 값 중 하나다”는 제약으로 표현했지만, “지금 이 사람이 과목을 완료했다고 처리해도 되는가”는 프로그램이 검증해야 할 수 있다. 격리성도 모든 환경에서 같은 동작을 뜻하지 않는다. 어떤 중간 상태를 볼 수 있는지는 데이터베이스와 설정에 따라 확인해야 한다.
이 실험에서 직접 관찰하는 범위는 제약 위반과 명시적 롤백, 정상 커밋이다. 연결 하나만 사용하므로 동시 실행의 격리성을 검증하지 않는다. 또한 메모리 데이터베이스를 사용하므로 종료 후 기록 보존이나 장애 후 복구를 실험하지 않는다. 지속성을 확인하려면 파일 저장과 복구 동작을 별도로 살펴봐야 한다.
완성 코드
다음 내용을 main.py에 저장한다. Python 3.12 이상에서 표준 라이브러리만 사용한다. 임의의 값이나 현재 시간을 사용하지 않으므로 실행할 때마다 같은 데이터를 만든다. isolation_level=None으로 연결해 SQL 문으로 트랜잭션의 시작과 끝을 직접 제어한다.
import sqlite3
QUERY = """
SELECT learner_id
FROM progress
WHERE course_id = ? AND status = ?
"""
def plan_kind(conn):
rows = conn.execute(
"EXPLAIN QUERY PLAN " + QUERY,
("DB", "planned"),
).fetchall()
details = [row[3] for row in rows]
if any("SEARCH" in detail for detail in details):
return "SEARCH"
if any("SCAN" in detail for detail in details):
return "SCAN"
raise RuntimeError("예상하지 못한 실행 계획")
def matching_ids(conn):
rows = conn.execute(
QUERY, ("DB", "planned")
).fetchall()
return sorted(row[0] for row in rows)
def done_count(conn):
return conn.execute(
"SELECT COUNT(*) FROM progress WHERE status = ?",
("done",),
).fetchone()[0]
def main():
conn = sqlite3.connect(":memory:", isolation_level=None)
try:
conn.execute("PRAGMA foreign_keys = ON")
conn.executescript("""
CREATE TABLE learners (
learner_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
previous_major TEXT NOT NULL
);
CREATE TABLE courses (
course_id TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL
);
CREATE TABLE progress (
learner_id INTEGER NOT NULL,
course_id TEXT NOT NULL,
status TEXT NOT NULL
CHECK (status IN ('planned', 'done')),
PRIMARY KEY (learner_id, course_id),
FOREIGN KEY (learner_id)
REFERENCES learners (learner_id),
FOREIGN KEY (course_id)
REFERENCES courses (course_id)
);
""")
courses = [
("DB", "데이터베이스"),
("OS", "운영체제"),
("NET", "네트워크"),
("LANG", "프로그래밍 언어와 컴파일러"),
]
learners = [
(number, f"학습자{number:03d}", "다른 전공")
for number in range(1, 201)
]
progress = [
(number, course_id, "planned")
for number in range(1, 201)
for course_id, _ in courses
]
conn.execute("BEGIN")
try:
conn.executemany(
"INSERT INTO learners VALUES (?, ?, ?)",
learners,
)
conn.executemany(
"INSERT INTO courses VALUES (?, ?)",
courses,
)
conn.executemany(
"INSERT INTO progress VALUES (?, ?, ?)",
progress,
)
conn.execute("COMMIT")
except Exception:
conn.execute("ROLLBACK")
raise
total = conn.execute(
"SELECT COUNT(*) FROM progress"
).fetchone()[0]
print(f"학습 현황: {total}행")
course_name, status = conn.execute("""
SELECT c.name, p.status
FROM progress AS p
JOIN courses AS c ON c.course_id = p.course_id
WHERE p.learner_id = ? AND p.course_id = ?
""", (1, "DB")).fetchone()
print(f"과목 연결: {course_name}: {status}")
before_plan = plan_kind(conn)
before_ids = matching_ids(conn)
conn.execute("""
CREATE INDEX idx_progress_course_status
ON progress (course_id, status)
""")
after_plan = plan_kind(conn)
after_ids = matching_ids(conn)
print(f"인덱스 전 계획: {before_plan}")
print(f"인덱스 후 계획: {after_plan}")
print(f"조회 결과: {len(after_ids)}명")
print(f"결과 동일: {before_ids == after_ids}")
try:
conn.execute(
"DELETE FROM courses WHERE course_id = ?",
("DB",),
)
except sqlite3.IntegrityError:
print("참조 중인 과목 삭제: 거부")
else:
raise AssertionError("외래 키 검사가 필요하다")
print(f"변경 전 완료: {done_count(conn)}건")
conn.execute("BEGIN")
try:
conn.execute("""
UPDATE progress SET status = ?
WHERE learner_id = ? AND course_id = ?
""", ("done", 1, "DB"))
print(f"트랜잭션 안 완료: {done_count(conn)}건")
conn.execute("""
UPDATE progress SET status = ?
WHERE learner_id = ? AND course_id = ?
""", ("paused", 1, "OS"))
conn.execute("COMMIT")
except sqlite3.IntegrityError:
conn.execute("ROLLBACK")
print("허용하지 않은 상태: 거부 후 롤백")
except Exception:
conn.execute("ROLLBACK")
raise
print(f"롤백 후 완료: {done_count(conn)}건")
conn.execute("BEGIN")
try:
conn.execute("""
UPDATE progress SET status = ?
WHERE learner_id = ? AND course_id = ?
""", ("done", 1, "DB"))
conn.execute("COMMIT")
except Exception:
conn.execute("ROLLBACK")
raise
print(f"커밋 후 완료: {done_count(conn)}건")
finally:
conn.close()
if __name__ == "__main__":
main()
줄별 해설
import sqlite3는 Python에 포함된 데이터베이스 모듈을 가져온다. QUERY에는 두 번 비교할 조회문을 한 번만 적는다. 물음표는 실행할 때 값을 전달할 자리다. 조건값을 문자열로 이어 붙이지 않고 별도 인자로 넘기는 방식을 매개변수 바인딩이라고 한다.
plan_kind()는 조회문 앞에 실행 계획 명령을 붙인다. 여기서 이어 붙이는 대상은 프로그램에 고정된 SQL이며 사용자 입력이 아니다. 결과 행의 네 번째 값에서 계획 설명을 얻고 탐색 여부를 분류한다. 상세 계획의 문구는 SQLite 버전에 따라 달라질 수 있으므로 전체 문장을 예상 출력에 고정하지 않는다.
matching_ids()는 같은 조회문을 실제로 실행한다. fetchall()은 결과 행을 모두 가져온다. 결과 순서는 SQL에서 지정하지 않았으므로 Python의 sorted()로 번호를 정렬한 뒤 비교한다. 인덱스가 결과를 바꾸지 않고 찾는 방법만 바꾸었는지 확인하기 위한 처리다.
done_count()의 COUNT(*)는 조건에 맞는 행 수를 센다. fetchone()으로 결과 행 하나를 받고 그 첫 번째 값을 꺼낸다. 단순히 UPDATE가 오류 없이 끝났다고 말하는 대신, 트랜잭션 안과 롤백 뒤의 실제 값을 관찰한다.
연결 직후의 PRAGMA foreign_keys = ON은 참조 검사를 켠다. 이어지는 executescript()는 고정된 여러 생성문을 실행한다. 이 메서드는 트랜잭션 처리에 주의할 부분이 있으므로, 여기서는 실험용 트랜잭션을 시작하기 전에만 사용한다. 변경을 묶는 구간에서는 execute()를 사용한다.
표 정의의 NOT NULL은 값이 비어 있음을 나타내는 NULL을 허용하지 않는다. CHECK는 허용 상태를 제한하고, 복합 기본 키는 학습자와 과목의 조합이 중복되지 않게 한다. 외래 키는 연결되는 학습자와 과목이 존재하는지 검사한다. 각 제약은 서로 다른 오류를 막는다.
두 개의 목록 만드는 식은 학습자 200명과 학습 현황 800개를 준비한다. executemany()는 같은 삽입문에 값 묶음을 반복해서 전달한다. 초기 데이터도 한 트랜잭션에 넣어, 준비 과정이 실패하면 일부만 남지 않게 한다. 예상하지 못한 오류는 롤백한 뒤 다시 발생시켜 숨기지 않는다.
조인 조회의 AS p와 AS c는 표에 짧은 별칭을 붙인다. ON 뒤에는 두 표를 연결하는 조건이 있다. 학습 현황의 과목 번호와 과목 표의 과목 번호가 같은 행을 연결하므로, 과목 이름을 복사해 두지 않고도 읽을 수 있다.
인덱스를 만들기 전후에는 동일한 조건으로 계획과 결과를 얻는다. 인덱스 이름은 관리할 때 구별하기 위한 이름이고, 괄호 안의 두 열과 순서가 조회 구조를 결정한다. 결과 목록 전체를 비교하므로 건수만 우연히 같은 경우와도 구별된다.
과목 삭제는 외래 키 검사를 확인하는 별도 실험이다. 등록 기록이 남아 있는 DB 과목을 지우면 IntegrityError가 발생한다. 이 이름은 제약을 어겨 데이터의 규칙을 지키지 못했다는 오류를 뜻한다. 이 삭제문은 명시적 트랜잭션 밖에서 실행되며, 실패한 문장의 삭제는 반영되지 않는다.
롤백 실험에서는 첫 UPDATE 뒤에 완료 건수가 1이 된다. 같은 연결에서 아직 확정하지 않은 변경을 읽은 결과다. 두 번째 UPDATE가 CHECK 조건을 어기면 예외 처리로 이동하고, 프로그램이 ROLLBACK을 실행한다. 이때 첫 UPDATE도 취소되어 완료 건수가 0으로 돌아간다. 끝으로 올바른 변경만 다시 실행해 커밋하면 1이 남는다.
finally는 중간에 오류가 나도 연결을 닫는다. 마지막 조건문은 이 파일을 직접 실행했을 때 main()을 호출한다. 메모리 데이터베이스이므로 연결을 닫으면 이번 실행에서 만든 데이터도 사라진다.
실행 결과
main.py가 있는 디렉터리에서 다음 명령을 실행한다.
python3 main.py예상 출력은 다음과 같다. 실행 계획은 이 예제의 단순한 조회에서 선택되는 경로를 보여 준다. 일반적인 서비스 데이터에서도 같은 경로가 선택된다고 확대해서 해석하지 않는다.
학습 현황: 800행
과목 연결: 데이터베이스: planned
인덱스 전 계획: SCAN
인덱스 후 계획: SEARCH
조회 결과: 200명
결과 동일: True
참조 중인 과목 삭제: 거부
변경 전 완료: 0건
트랜잭션 안 완료: 1건
허용하지 않은 상태: 거부 후 롤백
롤백 후 완료: 0건
커밋 후 완료: 1건눈여겨볼 부분은 세 곳이다. SCAN에서 SEARCH로 바뀌어도 결과는 같다. 참조 중인 과목 삭제는 거부된다. 트랜잭션 안에서 보였던 완료 기록은 롤백 후 사라지고, 별도로 커밋한 기록만 남는다. 저장 구조, 조회 경로, 변경 경계를 각각 출력으로 확인한 셈이다.
실무에서 자주 틀리는 것
값을 SQL 문자열에 직접 넣는다
다음은 사용자가 입력한 과목 번호를 그대로 이어 붙이는 잘못된 방식이다. 입력에 따옴표나 SQL 조각이 있으면 문장의 의미가 달라질 수 있다. 입력값이 SQL 구조로 해석되도록 만드는 공격을 SQL 주입이라고 한다.
course_id = "DB"
sql = "SELECT name FROM courses WHERE course_id = '" + course_id + "'"
row = conn.execute(sql).fetchone()값은 바인딩으로 전달한다. 값이 하나인 튜플에는 쉼표가 필요하다. 물음표는 값 자리이며 표 이름이나 열 이름을 대신하는 용도로 쓰지 않는다.
course_id = "DB"
row = conn.execute(
"SELECT name FROM courses WHERE course_id = ?",
(course_id,),
).fetchone()오류가 나면 앞선 변경도 자동으로 취소됐다고 생각한다
다음 코드는 완성 코드와 같은 수동 트랜잭션 제어 환경을 전제로 한다. 오류 메시지만 남기면 앞서 성공한 변경이 열린 트랜잭션에 남을 수 있다. 문장 하나의 실패와 트랜잭션 전체의 취소는 구별해야 한다.
conn.execute("BEGIN")
try:
conn.execute(
"UPDATE progress SET status = ? WHERE learner_id = ?",
("done", 2),
)
conn.execute(
"UPDATE progress SET status = ? WHERE learner_id = ?",
("paused", 2),
)
conn.execute("COMMIT")
except sqlite3.IntegrityError:
print("변경 실패")함께 취소해야 하는 구간은 예외 처리에서 명시적으로 되돌린다. 다음 코드는 오류를 호출한 쪽에도 전달하므로, 상위 기능이 실패를 성공으로 오해하지 않는다.
conn.execute("BEGIN")
try:
conn.execute(
"UPDATE progress SET status = ? WHERE learner_id = ?",
("done", 2),
)
conn.execute(
"UPDATE progress SET status = ? WHERE learner_id = ?",
("paused", 2),
)
conn.execute("COMMIT")
except Exception:
conn.execute("ROLLBACK")
raise빠르게 읽으려고 같은 사실을 먼저 복사한다
다음 코드는 학습 현황에 과목 이름을 복사하는 열을 추가한다. 초기에는 같은 값으로 채워도 이후 과목 이름을 바꾸는 모든 경로에서 두 위치를 함께 관리해야 한다. 성능 문제가 확인되기 전에 이런 중복부터 넣으면 갱신 규칙만 늘어날 수 있다.
conn.execute("ALTER TABLE progress ADD COLUMN course_name TEXT")
conn.execute("""
UPDATE progress
SET course_name = (
SELECT name FROM courses
WHERE courses.course_id = progress.course_id
)
""")처음에는 원래 위치의 이름을 조인해서 읽는다. 실제 비용을 측정한 뒤 의도적으로 중복을 두는 설계를 비정규화라고 한다. 비정규화를 선택할 때는 복사본을 언제, 어떻게 맞출지도 함께 정해야 한다.
rows = conn.execute("""
SELECT p.learner_id, c.name, p.status
FROM progress AS p
JOIN courses AS c ON c.course_id = p.course_id
WHERE p.course_id = ?
ORDER BY p.learner_id
""", ("DB",)).fetchall()인덱스만 있으면 화면 순서도 정해진다고 생각한다
인덱스가 어떤 순서로 저장된다는 사실과 조회 결과의 순서를 보장하는 것은 다르다. 아래 조회에서 관찰한 순서를 화면의 약속으로 삼으면 실행 계획이 바뀔 때 문제가 생길 수 있다.
rows = conn.execute(
"SELECT learner_id FROM progress WHERE course_id = ?",
("DB",),
).fetchall()오름차순이 요구사항이라면 ORDER BY를 명시한다. 정렬 비용은 계획으로 살펴보되, 결과에 필요한 의미를 먼저 SQL에 표현한다.
rows = conn.execute("""
SELECT learner_id FROM progress
WHERE course_id = ?
ORDER BY learner_id
""", ("DB",)).fetchall()한눈에 보기
| 핵심 | 실무에서 쓰는 곳 | 면접에서 묻는 방식 | 설명의 기준 |
|---|---|---|---|
| 관계형 모델과 키 | 학습자와 과목 연결 | 기본 키와 외래 키는 어떻게 다른가 | 행 식별과 참조 규칙을 구별한다 |
| 정규화 | 과목 이름의 중복 제거 | 표를 나누면 무엇을 막는가 | 갱신·삽입·삭제 이상으로 설명한다 |
| 인덱스 | 과목별 학습자 조회 | 인덱스를 많이 만들면 좋은가 | 조회 이득과 쓰기·공간 비용을 비교한다 |
| 트랜잭션과 ACID | 여러 변경의 일괄 처리 | 두 번째 변경이 실패하면 어떻게 하는가 | 문장 실패와 전체 롤백을 구별한다 |
면접에서는 용어의 정의 뒤에 이 실험의 장면을 붙이면 설명이 구체적이 된다. “원자성은 모두 성공하거나 모두 취소되는 성질이다”에서 끝내지 않고, “첫 UPDATE가 성공했어도 두 번째 변경 실패 후 명시적으로 롤백해 완료 건수가 0으로 돌아갔다”까지 말하는 식이다. 다만 오류가 발생하면 데이터베이스가 언제나 전체 트랜잭션을 자동 취소한다고 말하지 않는다.
서재에서 더 깊게 볼 책으로는 Database System Concepts와 Database Management Systems가 있다. 전자는 관계형 모델, 질의 처리, 트랜잭션을 한 과목의 흐름으로 연결해 읽기 좋다. 후자는 저장 구조와 인덱스, 실행 비용을 더 살펴볼 때 함께 볼 수 있다. 처음부터 순서대로 읽기보다 이 장에서 남은 질문에 해당하는 부분을 찾는 방식으로 활용한다.
사실과 API 동작을 더 확인하려면 Python sqlite3 문서, SQLite 실행 계획 안내, SQLite 외래 키 안내를 참고한다. 특히 트랜잭션 제어 방식과 계획 표시 형식은 사용하는 환경에 맞춰 확인한다. 다음 장에서는 이처럼 작성한 Python과 SQL 같은 언어가 해석되고 실행되는 과정을 살펴본다.
연습 문제
- 인덱스 전후 조회 결과를 출력할 때 가장 작은 학습자 번호와 가장 큰 학습자 번호도 표시하라. 결과 건수가 같다는 확인에 번호 목록 전체 비교를 추가한 이유를 설명하라.
- 완성 코드의 인덱스 비교 뒤에 학습자 1의 DB 기록을 한 번 더 삽입해 보라. 어떤 제약이 막는지 설명하고, 기존 행 수가 달라지는지도 확인하라.
- 롤백 실험에서 허용하지 않은 상태 paused를 done으로 바꾸라. “롤백 후 완료”라는 출력 문구도 실제 동작에 맞게 고치고, 최종 완료 건수를 예상하라.
- 과목 이름을 “데이터베이스 기초”로 변경하고 조인으로 다시 읽어라. 학습 현황 200행의 과목 이름을 따로 고칠 필요가 없는 이유를 설명하라.
정답과 해설
첫 번째 문제는 정렬된 목록의 양 끝을 읽으면 된다. 이 실험의 결과는 비어 있지 않으므로 다음 코드에서 첫 값은 1, 마지막 값은 200이다. 일반적인 입력에서는 빈 목록인 경우도 처리해야 한다. 건수만 비교하면 서로 다른 200명이 조회되어도 같다고 판단할 수 있으므로 목록 전체를 비교한다.
print(f"번호 범위: {after_ids[0]}~{after_ids[-1]}")
print(f"목록 동일: {before_ids == after_ids}")두 번째 문제는 학습자 번호와 과목 번호의 복합 기본 키가 삽입을 막는다. 이름이나 상태가 같아서가 아니라 동일한 키 조합이 이미 있기 때문이다. 다음 코드를 인덱스 비교 뒤에 넣으면 “중복 등록: 거부”와 800이 출력된다. 이 위치는 명시적인 트랜잭션 밖이므로 실패한 삽입문에 별도의 ROLLBACK을 실행하지 않는다.
try:
conn.execute(
"INSERT INTO progress VALUES (?, ?, ?)",
(1, "DB", "planned"),
)
except sqlite3.IntegrityError:
print("중복 등록: 거부")
print(conn.execute(
"SELECT COUNT(*) FROM progress"
).fetchone()[0])세 번째 문제에서는 두 UPDATE가 모두 성공하므로 COMMIT까지 도달한다. 따라서 두 과목이 완료되어 2건이 된다. 이후 DB 과목을 다시 done으로 바꿔도 이미 완료된 행의 값이 같게 유지되므로 최종 완료 건수 역시 2건이다. 해당 출력은 다음처럼 바꾼다. 실패와 롤백을 알리는 문장은 출력되지 않는다.
print(f"첫 변경 묶음 후 완료: {done_count(conn)}건")네 번째 문제는 과목 표 한 행만 수정하면 된다. 아래 조회는 학습자 1에게 연결된 새 과목 이름을 반환한다. 학습 현황은 변하지 않는 과목 번호 DB를 참조하고, 이름이라는 사실은 과목 표 한 곳에서 읽기 때문에 학습자별 복사본을 맞출 일이 없다.
conn.execute(
"UPDATE courses SET name = ? WHERE course_id = ?",
("데이터베이스 기초", "DB"),
)
row = conn.execute("""
SELECT c.name
FROM progress AS p
JOIN courses AS c ON c.course_id = p.course_id
WHERE p.learner_id = ? AND p.course_id = ?
""", (1, "DB")).fetchone()
print(row[0])