REPEATABLE READ와 autocommit 때문에 MySQL SELECT가 옛 값을 돌려주는 이유

MySQL에서 분명히 데이터를 UPDATE하고 COMMIT까지 했는데, 다른 커넥션에서 SELECT하면 변경 전 값이 계속 나오는 경우가 있습니다. 캐시도 없고, 쿼리도 틀린 게 없는데 말입니다. 서버를 재시작하면 또 잘 보입니다.

원인은 MySQL의 트랜잭션 격리 수준과 PyMySQL의 기본 설정이 만드는 조합에 있습니다. 이 글의 모든 동작은 MySQL 8.4.11에서 두 세션을 직접 띄워 확인한 것입니다.

어떤 상황에서 발생하는가

먼저 전제를 하나 못박아 둡니다. 이 현상은 커넥션 하나를 서버 시작부터 종료까지 붙잡아두는 구조에서 나타납니다. 모듈 전역에 커넥션을 하나 만들어 두고 계속 재사용하는 코드가 대표적입니다. SQLAlchemy 같은 커넥션 풀은 커넥션을 반납할 때 ROLLBACK을 날려 트랜잭션을 끊어주므로, 풀을 쓰고 있다면 이 증상이 잘 재현되지 않습니다.

관리자 페이지에서 주문 상태를 PENDING에서 SHIPPING으로 바꾸고 COMMIT까지 했는데, API 서버 쪽 SELECT는 몇 번을 다시 던져도 계속 PENDING을 돌려줍니다. 서버를 재시작해서 커넥션을 새로 만들어야 비로소 SHIPPING이 보입니다.

두 세션으로 그대로 재현됩니다.

-- 세션 A: autocommit을 끄고 조회
SET autocommit = 0;
SELECT status FROM orders WHERE order_id = 1;   -- PENDING

-- 세션 B: 다른 커넥션에서 변경하고 커밋
UPDATE orders SET status = 'SHIPPING' WHERE order_id = 1;
COMMIT;

-- 세션 A: 같은 쿼리를 다시 던져도
SELECT status FROM orders WHERE order_id = 1;   -- PENDING
SELECT status FROM orders WHERE order_id = 1;   -- PENDING

-- 세션 A: 자기 트랜잭션을 끊은 뒤에야
COMMIT;
SELECT status FROM orders WHERE order_id = 1;   -- SHIPPING

세션 A는 SELECT만 던졌습니다. 그런데도 세션 A를 가둔 것은 세션 A 자신의 트랜잭션입니다.

MySQL의 트랜잭션 격리 수준

SQL 표준은 4가지 격리 수준을 정의하고 있고, MySQL InnoDB의 기본값은 REPEATABLE READ입니다. SELECT @@transaction_isolation을 던지면 REPEATABLE-READ가 나옵니다.

두 번째 열은 SQL 표준 기준입니다. InnoDB의 실제 동작은 표준보다 강해서, REPEATABLE READ에서 일반 SELECT는 MVCC 스냅샷을 읽으므로 Phantom Read가 보이지 않습니다. SELECT ... FOR UPDATE 같은 잠금 읽기나 DML은 갭 잠금으로 막습니다.

이 글에서 중요한 것은 세 번째 열입니다.

스냅샷은 언제 고정되는가

여기가 제일 틀리기 쉬운 대목입니다. REPEATABLE READ의 스냅샷은 START TRANSACTION 시점이 아니라 트랜잭션 안에서 첫 번째 일관된 읽기가 실행되는 순간 만들어집니다. MySQL 레퍼런스도 “all consistent reads within the same transaction read the snapshot established by the first such read in that transaction”이라고 적고 있습니다.

차이는 실측으로 갈립니다. 트랜잭션만 열어두고 아직 아무것도 읽지 않았다면, 그 사이에 들어온 커밋은 그대로 보입니다.

-- 세션 A
SET autocommit = 0;
START TRANSACTION;      -- 트랜잭션은 열렸지만 스냅샷은 아직 없음

-- 세션 B: UPDATE ... 'SHIPPING' 후 COMMIT

-- 세션 A: 이 트랜잭션의 첫 읽기
SELECT status FROM orders WHERE order_id = 1;   -- SHIPPING

트랜잭션을 여는 시점에 스냅샷을 잡고 싶다면 별도 구문이 있습니다. 같은 시나리오에서 결과가 뒤집힙니다.

-- 세션 A
START TRANSACTION WITH CONSISTENT SNAPSHOT;   -- 이 순간 스냅샷 고정

-- 세션 B: UPDATE ... 'SHIPPING' 후 COMMIT

-- 세션 A
SELECT status FROM orders WHERE order_id = 1;   -- PENDING

SELECT status FROM orders WHERE order_id = 1;              -- PENDING
SELECT status FROM orders WHERE order_id = 1 FOR SHARE;    -- SHIPPING
SELECT status FROM orders WHERE order_id = 1 FOR UPDATE;   -- SHIPPING
SELECT status FROM orders WHERE order_id = 1;              -- PENDING

autocommit=False가 만드는 끝나지 않는 트랜잭션

PyMySQL의 connect()는 autocommit 파라미터의 기본값이 False입니다. 소스의 시그니처가 autocommit=False이고, 독스트링도 “Autocommit mode. None means use server default. (default: False)“라고 적고 있습니다. 서버 쪽 @@autocommit은 기본이 1이므로, 이 값을 끄는 주체는 서버가 아니라 드라이버입니다.

import pymysql

conn = pymysql.connect(
    host='db.example.com',
    user='api_user',
    password='...',
    database='myapp',
)
# autocommit을 명시하지 않았으므로 conn.autocommit(False)와 같은 상태

autocommit=False인 상태에서 첫 번째 SQL 문이 실행되면 MySQL은 암묵적으로 트랜잭션을 시작하고, 이 트랜잭션은 COMMIT 또는 ROLLBACK을 호출하기 전까지 끝나지 않습니다. 여기에 REPEATABLE READ가 결합되면 첫 SELECT가 트랜잭션을 열면서 동시에 스냅샷을 고정시킵니다.

의심스러우면 서버 쪽에서 바로 확인할 수 있습니다. 평범한 SELECT 한 번을 던진 뒤 다른 커넥션에서 조회하면 트랜잭션이 잡혀 있습니다.

SELECT trx_state, trx_isolation_level FROM information_schema.innodb_trx;

RUNNING    REPEATABLE READ

이 행은 해당 커넥션이 COMMIT이나 ROLLBACK을 부를 때까지 사라지지 않습니다.

autocommit=True로 해결하기

가장 깔끔한 해결법은 읽기 전용 커넥션에 autocommit=True를 설정하는 것입니다.

conn = pymysql.connect(
    host='db.example.com',
    user='api_user',
    password='...',
    database='myapp',
    autocommit=True,
)

이러면 각 SQL 문이 독립적인 트랜잭션으로 실행됩니다. SELECT 하나가 곧 하나의 트랜잭션이고, 실행이 끝나면 자동으로 커밋됩니다. 매 SELECT마다 새 스냅샷을 읽으므로 다른 커넥션의 COMMIT이 즉시 반영됩니다.

autocommit을 켤 수 없을 때

같은 커넥션으로 읽기와 쓰기를 모두 한다면 무조건 autocommit을 켜는 것이 답은 아닙니다. 쓰기 쪽에서 여러 문장을 하나의 단위로 묶어야 한다면 autocommit은 그 묶음을 깨뜨립니다.

조회 전에 명시적으로 COMMIT하기

conn.commit()

with conn.cursor() as cursor:
    cursor.execute("SELECT * FROM orders WHERE order_id = %s", (order_id,))
    row = cursor.fetchone()

기존 트랜잭션을 끝내면 다음 SELECT가 새 트랜잭션을 열고 새 스냅샷을 잡습니다. ROLLBACK도 같은 효과를 냅니다. 쓰기 작업이 섞여 있어서 autocommit을 쓸 수 없는 경우에 적합합니다.

세션 격리 수준을 READ COMMITTED로 낮추기

격리 수준을 READ COMMITTED로 낮추면 autocommit=False여도 매 SELECT마다 새 스냅샷을 읽습니다. 다만 실행 시점이 중요합니다.

conn = pymysql.connect(...)

with conn.cursor() as cursor:
    cursor.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")

이미 트랜잭션이 열려 있는 커넥션이라면 conn.rollback()이나 conn.commit()으로 먼저 끊어야 합니다. 이 방법은 동일 트랜잭션 안의 반복 읽기 일관성을 포기하는 것이므로, 보고서처럼 여러 쿼리가 같은 시점을 봐야 하는 작업에는 쓰면 안 됩니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- 통과
SELECT @@transaction_isolation;                           -- READ-COMMITTED
SELECT status FROM orders WHERE order_id = 1;             -- PENDING

상황별 선택

읽기 전용 커넥션과 쓰기 커넥션을 아예 분리하면 이 표를 고민할 일이 없어집니다. 읽기 쪽은 autocommit=True, 쓰기 쪽은 autocommit=False로 두면 각자의 목적에 맞는 트랜잭션 경계를 갖습니다.

ORM을 쓰면 괜찮은가

raw 커넥션 대신 ORM을 쓰면 피할 수 있을지는 어느 ORM이냐에 따라 갈립니다.

Django는 기본이 autocommit 모드입니다. 공식 문서가 “Django’s default behavior is to run in autocommit mode. Each query is immediately committed to the database, unless a transaction is active”라고 못박고 있어서, 스냅샷 고정 문제가 생기지 않습니다. 트랜잭션은 transaction.atomic() 블록 안에서만 열립니다.

SQLAlchemy 2.0은 autobegin 방식입니다. 쿼리를 실행하면 자동으로 트랜잭션이 시작되고, session.commit() 또는 session.rollback()으로 끝내야 합니다. 레거시였던 Session(autocommit=True) 옵션은 2.0에서 제거되었습니다. 대신 커넥션 풀이 커넥션을 반납할 때 ROLLBACK을 날리므로, 웹 프레임워크에서 request 단위로 세션을 관리하면(FastAPI의 Depends(get_db), Flask의 @app.teardown_appcontext) request마다 트랜잭션이 끊겨 대체로 안전합니다. 위험한 쪽은 배치 스크립트나 Celery worker처럼 세션을 오래 열어두는 코드입니다. 읽기 전용 작업이라면 create_engine(..., isolation_level="AUTOCOMMIT")이나 connection.execution_options(isolation_level="AUTOCOMMIT")으로 DBAPI 레벨 autocommit을 쓸 수 있습니다.

다른 드라이버의 기본값

이 함정이 PyMySQL만의 것은 아닙니다.

세 드라이버가 모두 False인 데는 이유가 있습니다. PEP 249는 .commit() 항목에서 “if the database supports an auto-commit feature, this must be initially off”라고 규정합니다. 권장이 아니라 요구사항입니다.

그렇다면 psycopg2는 왜 같은 autocommit=False인데 증상이 없을까요. PostgreSQL의 기본 격리 수준이 READ COMMITTED이기 때문입니다. READ COMMITTED는 매 SELECT마다 새 스냅샷을 읽으므로, 트랜잭션이 열려 있어도 다른 커넥션의 COMMIT이 보입니다. 결국 이 문제는 MySQL의 기본 격리 수준과 PEP 249의 기본값이라는 두 기본값이 만나야 발생합니다.

표의 “없음”은 기본값을 그대로 뒀을 때의 이야기입니다. PostgreSQL에서도 격리 수준을 REPEATABLE READ로 올리면 같은 일이 벌어집니다. psycopg2 문서가 다는 경고도 같은 취지입니다. 단순한 SELECT 하나도 트랜잭션을 시작하므로, 오래 도는 프로그램에서 아무 조치를 안 하면 세션이 “idle in transaction” 상태로 남아 잠금을 쥐고 테이블을 부풀린다는 것입니다.

마치며

이 문제가 까다로운 이유는 증상과 원인이 서로 다른 커넥션에 있기 때문입니다. 값이 안 바뀌는 쪽은 읽는 커넥션인데, 사람은 쓰는 쪽을 먼저 의심합니다. 쓰기 세션에서 아무리 COMMIT을 확인해도 답이 안 나옵니다.

점검 순서는 단순합니다. 증상이 나오는 커넥션이 오래 살아 있는지, 그 커넥션의 드라이버 autocommit이 무엇인지, information_schema.innodb_trx에 그 커넥션의 트랜잭션이 잡혀 있는지를 보면 됩니다. 세 가지가 모두 걸리면 원인은 확정입니다.

기억할 것은 하나입니다. MySQL에서 SELECT도 트랜잭션을 엽니다. autocommit=False면 첫 SELECT가 트랜잭션을 열면서 스냅샷을 고정하고, 명시적으로 끊기 전까지 그 커넥션은 그 시점의 데이터베이스를 계속 보게 됩니다.

함께 보면 좋은 글

• 트랜잭션 격리 수준과 락 : 같은 격리 수준을 PostgreSQL은 어떻게 구현하는지
• PostgreSQL MVCC와 튜플 가시성 : 스냅샷이 어떤 행을 보여줄지 결정하는 원리
• VACUUM과 bloat의 정체 : 끝나지 않는 트랜잭션이 정리 작업을 어떻게 막는지

참고자료

• MySQL 8.4 Reference: Consistent Nonlocking Reads
• MySQL 8.4 Reference: Transaction Isolation Levels
• MySQL 8.4 Reference: SET TRANSACTION
• PyMySQL Documentation: Connection
• PEP 249: Python Database API Specification v2.0
• Django Documentation: Database transactions