SQLAlchemy 2.0 #2 엔진과 트랜잭션: 커넥션 풀, commit의 두 가지 패턴

프로그래밍 언어 파이썬 SQLAlchemy 데이터베이스
5 분 소요

1편에서 엔진을 만들고 text()로 쿼리를 실행했습니다. 이번 편은 그 밑에서 일어나는 일입니다. 커넥션 풀이 어떻게 돌아가는지, 트랜잭션은 언제 시작되고 언제 커밋되는지를 정확히 알아야 ORM에 올라가서도 흔들리지 않습니다. 실무에서 겪는 “커넥션 고갈”, “커밋했는데 반영이 안 됨"의 답이 전부 이 층에 있습니다.

커넥션 풀: 엔진이 진짜 하는 일 #

DB 접속은 비쌉니다. TCP 연결, 인증, 세션 초기화까지 밀리초 단위의 비용이 들고, DB 서버가 받을 수 있는 동시 접속 수에도 한계가 있습니다. 그래서 엔진은 접속을 매번 새로 만들지 않고 커넥션 풀에 보관해 두고 재사용합니다.

main.py 
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql+psycopg2://user:pw@localhost/mydb",
    pool_size=5,        # 풀에 유지하는 커넥션 수 (기본 5)
    max_overflow=10,    # 풀이 바닥났을 때 추가로 여는 수 (기본 10)
    pool_timeout=30,    # 빌릴 커넥션이 없을 때 기다리는 초 (기본 30)
    pool_pre_ping=True, # 빌려주기 전에 살아 있는지 확인
)

동작 방식은 도서관 대출과 같은 구조입니다. engine.connect()는 풀에서 커넥션을 빌리고, with 블록이 끝나면 닫는 것이 아니라 풀에 반납합니다. 알아둘 값은 셋입니다.

  • pool_size + max_overflow가 실질 상한입니다. 기본값이면 프로세스당 최대 15개까지 동시에 열 수 있고, 그 이상 요청되면 pool_timeout만큼 기다리다 TimeoutError가 납니다. “커넥션 고갈” 에러의 정체는 대부분 반납되지 않은 커넥션(with 없이 빌리고 close를 안 함)이 풀을 잠식한 것입니다.
  • 프로세스 수를 곱해야 합니다. gunicorn 워커 4개면 풀도 4개, DB가 감당해야 할 접속은 최대 60개가 됩니다. DB 쪽 max_connections와 함께 계산해야 합니다.
  • pool_pre_ping은 오래 방치되어 서버 쪽에서 끊긴 커넥션을 걸러 줍니다. “MySQL server has gone away” 류의 에러를 만나면 이 옵션부터 확인합니다.

SQLite 파일 DB는 접속 비용이 거의 없어서 풀 설정을 신경 쓸 일이 없습니다. 풀이 문제가 되는 것은 네트워크 너머의 PostgreSQL, MySQL부터입니다.

트랜잭션: 자동 커밋은 없습니다 #

2.0의 대원칙은 트랜잭션은 항상 명시적입니다. DBAPI 드라이버는 원래 자동 커밋이 아니고, SQLAlchemy도 이를 숨기지 않습니다. 커밋을 안 하면 블록이 끝날 때 롤백됩니다.

main.py 
from sqlalchemy import text

# 이 INSERT는 사라집니다. 커밋하지 않았기 때문입니다.
with engine.connect() as conn:
    conn.execute(text("INSERT INTO memo (body) VALUES ('임시')"))
# 블록 종료 → 롤백

커밋하는 방법은 두 패턴입니다.

main.py 
# 패턴 1: commit-as-you-go — 원하는 지점에서 직접 커밋
with engine.connect() as conn:
    conn.execute(text("INSERT INTO memo (body) VALUES ('첫 번째')"))
    conn.commit()  # 여기까지 확정
    conn.execute(text("INSERT INTO memo (body) VALUES ('두 번째')"))
    conn.commit()  # 두 번째 트랜잭션 확정

# 패턴 2: begin-once — 블록 전체가 하나의 트랜잭션
with engine.begin() as conn:
    conn.execute(text("INSERT INTO memo (body) VALUES ('셋')"))
    conn.execute(text("INSERT INTO memo (body) VALUES ('넷')"))
# 블록이 정상 종료하면 커밋, 예외가 나면 롤백

실무 기본값은 engine.begin()을 쓰는 패턴 2입니다. “전부 성공하거나 전부 취소"라는 트랜잭션의 목적에 정확히 부합하고, 커밋을 잊을 수가 없습니다. 패턴 1은 긴 배치 작업에서 중간 저장 지점을 만들 때처럼, 한 커넥션에서 트랜잭션을 여러 번 끊어야 할 때 씁니다.

테이블 정의: MetaData와 Table #

Core에서 테이블은 Table 객체로 표현하고, 테이블들의 목록은 MetaData가 들고 있습니다.

main.py 
from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey

metadata = MetaData()

user_table = Table(
    "user_account",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(30), nullable=False),
    Column("email", String(100), nullable=False, unique=True),
)

address_table = Table(
    "address",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("user_id", ForeignKey("user_account.id"), nullable=False),
    Column("email_address", String(100), nullable=False),
)

metadata.create_all(engine)  # 없는 테이블만 CREATE TABLE

create_all()은 학습과 프로토타입에는 편하지만, 이미 있는 테이블을 변경해 주지는 않습니다. 컬럼 추가 같은 스키마 변경은 7편에서 다룰 Alembic의 일입니다.

Core로 CRUD: SQL을 파이썬 표현식으로 #

text()와 달리, Core 표현식은 파이썬 객체로 SQL을 조립합니다. 오타가 문자열 안에 숨지 않고, 파라미터 바인딩이 자동입니다.

main.py 
from sqlalchemy import insert, select, update, delete

# INSERT
with engine.begin() as conn:
    conn.execute(
        insert(user_table),
        [
            {"name": "김파이썬", "email": "kim@example.com"},
            {"name": "이코어", "email": "lee@example.com"},
        ],
    )

# SELECT
with engine.connect() as conn:
    stmt = select(user_table).where(user_table.c.name == "김파이썬")
    for row in conn.execute(stmt):
        print(row.id, row.name, row.email)

# UPDATE와 DELETE
with engine.begin() as conn:
    conn.execute(
        update(user_table)
        .where(user_table.c.email == "lee@example.com")
        .values(name="이수정")
    )
    conn.execute(delete(user_table).where(user_table.c.id == 99))
  • 컬럼은 테이블.c.컬럼명으로 접근합니다. user_table.c.name == "김파이썬"은 비교 결과(불리언)가 아니라 SQL 조건 객체를 만듭니다. 파이썬 연산자를 오버로드한 것입니다.
  • insert()에 딕셔너리 리스트를 넘기면 여러 행을 한 번에 넣는 executemany가 됩니다. 대량 입력의 기본형입니다.
  • echo=True를 켜 두면 각 표현식이 어떤 SQL로 컴파일되는지 보입니다. where() 조건이 전부 바인딩 파라미터(? 또는 %(name)s)로 처리되는 것을 확인할 수 있습니다.

이 select() 문법은 다음 편부터 ORM에서도 그대로 씁니다. 바뀌는 것은 user_table.c.name이 User.name이 되는 것뿐입니다. 2.0에서 Core를 먼저 배우는 보람이 여기서 나옵니다.

정리 #

  • 엔진의 실체는 커넥션 풀입니다. pool_size + max_overflow가 프로세스당 동시 접속 상한이고, 워커 수를 곱해 DB의 max_connections와 맞춰야 합니다.
  • 커넥션 고갈의 주범은 반납되지 않는 커넥션입니다. 커넥션은 항상 with로 빌립니다.
  • 자동 커밋은 없습니다. 기본은 engine.begin()으로 블록 전체를 한 트랜잭션으로 묶고, 중간 커밋이 필요할 때만 connect() + commit()을 씁니다.
  • 테이블은 Table과 MetaData로 정의하고, insert, select, update, delete 표현식으로 조작합니다. 조건은 파이썬 연산자로 쓰지만 전부 바인딩 파라미터로 컴파일됩니다.
  • 다음 편에서는 같은 테이블을 ORM 클래스로 선언하는 법, Mapped와 mapped_column을 다룹니다.
X