[DB-006][기초] 트랜잭션과 잠금으로 데이터 안전하게 바꾸기 > IT 기술 공유

• 목록

[이번 수업]

트랜잭션, 격리 수준, 잠금이 동시 변경을 지키는 방법을 배웁니다.

[선수지식]

DB-001·002·005와 Python 3의 기본 `sqlite3`를 알면 됩니다.

[학습목표]

1. COMMIT과 ROLLBACK의 차이를 설명한다.
2. 주요 격리 수준의 차이를 설명한다.
3. 두 연결의 읽기·쓰기 충돌을 로컬에서 관찰한다.

[핵심개념]

트랜잭션은 함께 성공하거나 함께 취소할 SQL 묶음입니다. 계좌 A 차감과 B 증가는 둘 다 끝난 뒤 `COMMIT`하고 하나라도 실패하면 `ROLLBACK`해야 합니다. 이를 원자성이라고 합니다.

격리 수준은 동시 트랜잭션이 서로의 변경을 보는 범위입니다. 대표 수준은 Read Uncommitted, Read Committed, Repeatable Read, Serializable입니다. Read Committed는 미커밋 값을 숨기지만 다음 조회가 새 커밋을 볼 수 있습니다. Repeatable Read는 읽기를 더 안정적으로 유지하고, Serializable은 결과가 하나씩 실행한 순서와 같도록 제한합니다. 실제 보장은 제품마다 달라 충돌 시 전체 재시도가 필요할 수 있습니다.

잠금은 변경 데이터의 임시 사용권입니다. 충돌 쓰기를 기다리거나 실패시켜 분실 갱신을 막지만 오래 잡으면 대기·교착이 늘어납니다. SQLite는 여러 읽기와 쓰기 하나를 허용하며 `BEGIN IMMEDIATE`는 쓰기 권한을 먼저 확보합니다.

[따라하기]

다음을 `tx_lab.py`로 저장합니다. 같은 파일을 연결 A와 B가 따로 엽니다.

```python
from pathlib import Path
import sqlite3

DB = "tx_lab.db"
Path(DB).unlink(missing_ok=True)

with sqlite3.connect(DB) as setup:
    setup.execute("CREATE TABLE account ("
                  "id INTEGER PRIMARY KEY, "
                  "balance INTEGER NOT NULL CHECK(balance >= 0))")
    setup.executemany(
        "INSERT INTO account VALUES (?, ?)",
        [(1, 100), (2, 50)],
    )
setup.close()

a = sqlite3.connect(DB, timeout=0.1, isolation_level=None)
b = sqlite3.connect(DB, timeout=0.1, isolation_level=None)

def balances(connection):
    rows = connection.execute(
        "SELECT balance FROM account ORDER BY id"
    )
    return [row[0] for row in rows]

a.execute("BEGIN IMMEDIATE")
a.execute("UPDATE account SET balance = balance - ? WHERE id = ?", (30, 1))
a.execute("UPDATE account SET balance = balance + ? WHERE id = ?", (30, 2))
print("A 내부:", balances(a))
print("B 읽기:", balances(b))

try:
    b.execute("UPDATE account SET balance = balance + ? WHERE id = ?", (1, 1))
except sqlite3.OperationalError as error:
    print("B 쓰기:", error)

a.rollback()
print("롤백 뒤:", balances(b))
a.close()
b.close()
```

macOS/Linux는 `python3 tx_lab.py`, Windows PowerShell은 `python tx_lab.py`로 실행합니다. 예상은 A `[70, 80]`, B `[100, 50]`, 쓰기 `database is locked`, 롤백 뒤 `[100, 50]` 순입니다.

[흔한 실수]

- 변경 사이에 COMMIT합니다.
- 예외 후 ROLLBACK 없이 연결을 재사용합니다.
- 격리 수준만 높이면 경쟁 조건이 끝난다고 생각합니다.
- 트랜잭션 안에서 외부 API나 긴 계산을 실행합니다.

[보안 주의]

SQL 값은 예제처럼 매개변수로 전달하세요. 잔액·재고 규칙은 트랜잭션과 CHECK·UNIQUE·조건부 UPDATE로 함께 지킵니다. 잠금은 짧게 유지하고 교착·직렬화 실패는 제한 횟수만 전체 트랜잭션을 재시도합니다. 결제·주문 재시도에는 멱등성을 두며 로그에는 민감값과 전체 매개변수를 남기지 않습니다.

[직접 해볼 과제]

1. `a.rollback()`을 `a.commit()`으로 바꾸고 B가 `[70, 80]`을 읽는지 확인하세요.
2. 차감액을 130으로 바꾸어 CHECK 위반을 잡고 ROLLBACK 뒤 잔액을 `assert`로 검사하세요.

[확인문제]

1. 두 UPDATE를 한 트랜잭션으로 묶어야 하는 이유는 무엇인가요?
2. A가 바꾼 값을 COMMIT 전에 B가 보지 못하는 것은 어떤 개념과 관련 있나요?
3. 잠금을 오래 유지하면 처리량과 교착 위험은 어떻게 변하나요?

[다음 학습]

DB-007에서는 PostgreSQL을 설치하고 역할·데이터베이스·기본 운영 명령을 익힙니다.

[공식 참고 자료]

- SQLite 트랜잭션 문서: https://www.sqlite.org/lang_transaction.html
- PostgreSQL 트랜잭션 격리 문서: https://www.postgresql.org/docs/current/transaction-iso.html
- Python sqlite3 트랜잭션 제어: https://docs.python.org/3/library/sqlite3.html#transaction-control