mssql-python을 이용한 트랜잭션 관리

mssql-python 드라이버는 커밋, 롤백, 자동 커밋 구성, 트랜잭션 격리 레벨을 포함한 완전한 트랜잭션 제어를 지원합니다.

트랜잭션 기본 사항

트랜잭션은 데이터베이스 연속 작업을 하나의 작업 단위로 그룹화합니다. 트랜잭션은 ACID 속성에 따라 진행됩니다:

원자성: 모든 작업은 성공하거나 실패합니다.
일관성: 데이터베이스는 유효한 상태를 유지합니다.
격리: 동시 거래는 서로 간섭하지 않습니다.
내구성: 전념한 변경은 시스템 장애를 견뎌냅니다.

자동 커밋 모드

이 설정은 autocommit 변경 사항이 자동으로 커밋되는지 여부를 제어합니다.

자동 커밋 비활성화 (기본값)

기본적으로autocommit=False. 변경을 명시적으로 커밋해야 합니다.

import mssql_python

conn = mssql_python.connect(connection_string)
print(conn.autocommit)  # False

cursor = conn.cursor()
cursor.execute("CREATE TABLE #TxnBasic (Name NVARCHAR(50))")
cursor.execute("INSERT INTO #TxnBasic (Name) VALUES ('Widget')")
cursor.execute("INSERT INTO #TxnBasic (Name) VALUES ('Gadget')")

# Changes are staged but not visible to other connections
conn.commit()  # Now changes are permanent

conn.close()

커밋하지 않으면 연결이 종료될 때 드라이버가 변경 사항을 버립니다.

자동 커밋 활성화

를 설정 autocommit=True하면 드라이버는 각 문장을 즉시 커밋합니다:

conn = mssql_python.connect(connection_string, autocommit=True)
# OR
conn.setautocommit(True)

cursor = conn.cursor()
cursor.execute("CREATE TABLE #AutoDemo (Name NVARCHAR(50))")
cursor.execute("INSERT INTO #AutoDemo (Name) VALUES ('Widget')")
# Immediately committed - no explicit commit needed

주의

자동 커밋이 활성화되면 여러 문장을 그룹으로 롤백할 수 없습니다. 자동 커밋은 사용 상황에 적합한 경우에만 사용하세요.

커밋 및 롤백

Commit

대기 중인 변경을 영구화하라는 요청 commit() :

cursor.execute("CREATE TABLE #CommitDemo (Name NVARCHAR(50), Price DECIMAL(10,2), CategoryID INT)")
cursor.execute("INSERT INTO #CommitDemo VALUES ('A',10,1),('B',20,1)")
cursor.execute("UPDATE #CommitDemo SET Price = Price * 1.1 WHERE CategoryID = 1")

conn.commit()  # The update is now permanent

롤백

대기 중인 변경 사항을 버리려면 다음을 호출하세요 rollback():

try:
    cursor.execute("CREATE TABLE #RollDemo (Name NVARCHAR(50), Price DECIMAL(10,2), CategoryID INT)")
    cursor.execute("INSERT INTO #RollDemo VALUES ('A',10,1),('B',20,1)")
    cursor.execute("UPDATE #RollDemo SET Price = Price * 1.1 WHERE CategoryID = 1")
    
    # Verify the update
    cursor.execute("SELECT AVG(Price) FROM #RollDemo WHERE CategoryID = 1")
    avg_price = cursor.fetchval()
    
    if avg_price > 100:
        conn.rollback()  # Price too high, undo both updates
        print("Rolled back: average price would exceed limit")
    else:
        conn.commit()
except Exception as e:
    conn.rollback()  # Undo on error
    raise

커서 수준 커밋 및 롤백

편의를 위해 커서에서 commit() 및 rollback()을 호출할 수 있습니다:

cursor = conn.cursor()
cursor.execute("CREATE TABLE #CursorDemo (Name NVARCHAR(50))")
cursor.execute("INSERT INTO #CursorDemo (Name) VALUES ('Widget')")
cursor.commit()  # Delegates to connection

cursor.execute("DELETE FROM #CursorDemo WHERE Name = 'Widget'")
cursor.rollback()  # Delegates to connection

비고

커서 레벨 커밋과 롤백은 호출한 커서뿐만 아니라 같은 연결 내 모든 커서에 영향을 미칩니다.

컨텍스트 관리자

연결 컨텍스트 매니저는 클린 종료 시 트랜잭션을 커밋하고, 예외가 발생하면 롤백합니다. 연결은 종료 시 항상 닫힙니다. 를 설정 autocommit=True하면 커밋과 롤백 호출은 아무런 영향이 없습니다.

with mssql_python.connect(connection_string) as conn:
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE #CtxDemo (Name NVARCHAR(50))")
    cursor.execute("INSERT INTO #CtxDemo (Name) VALUES ('Widget')")
    cursor.execute("INSERT INTO #CtxDemo (Name) VALUES ('Gadget')")
# Transaction is committed and connection is closed on exit

예외가 발생하면 트랜잭션이 롤백됩니다:

try:
    with mssql_python.connect(connection_string) as conn:
        cursor = conn.cursor()
        cursor.execute("CREATE TABLE #TxnDemo (Name NVARCHAR(50))")
        cursor.execute("INSERT INTO #TxnDemo (Name) VALUES ('Widget')")
        raise ValueError("Something went wrong")
except ValueError:
    pass
# Transaction is rolled back and connection is closed on exit

트랜잭션 격리 수준

격리 레벨은 트랜잭션이 동시 트랜잭션과 어떻게 상호작용하는지를 제어합니다. 격리 레벨을 다음과 같이 set_attr()설정하세요:

import mssql_python

conn = mssql_python.connect(connection_string)

# Set isolation level
conn.set_attr(
    mssql_python.SQL_ATTR_TXN_ISOLATION,
    mssql_python.SQL_TXN_SERIALIZABLE
)

사용 가능한 격리 수준

상수	설명
`SQL_TXN_READ_UNCOMMITTED`	다른 트랜잭션의 커밋되지 않은 변경 사항을 읽을 수 있습니다(더티 리드 허용)
`SQL_TXN_READ_COMMITTED`	커밋된 데이터만 읽습니다(SQL Server 기본값)
`SQL_TXN_REPEATABLE_READ`	트랜잭션 내에서 일관된 읽기를 보장
`SQL_TXN_SERIALIZABLE`	최고 격리; 거래는 순차적으로 실행되는 것으로 보입니다

격리 수준을 선택하세요

사용 사례	권장 수준
일반 OLTP 작업 부하	`READ_COMMITTED`(기본값)
일관된 스냅샷이 필요한 보고서	`REPEATABLE_READ` 또는 스냅샷
정확성이 요구되는 재무 계산	`SERIALIZABLE`
읽기 중심 워크로드가 오래된 데이터를 허용하는 경우	`READ_UNCOMMITTED`

스냅샷 격리

스냅샷 격리를 위해서는 Transact-SQL(T-SQL)를 사용하세요. 스냅샷 격리는 tempdb에서 행 버전 관리를 사용하므로, 쓰기 작업 부하가 높은 경우 저장 공간 요구량이 증가할 수 있습니다.

# Enable snapshot isolation on the database (one-time setup, requires autocommit)
conn.commit()
conn.autocommit = True
cursor.execute("ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON")

# Set isolation level while still in autocommit, then start the transaction
cursor.execute("SET TRANSACTION ISOLATION LEVEL SNAPSHOT")
conn.autocommit = False

cursor.execute("SELECT TOP 5 Name, ListPrice FROM Production.Product")
rows = cursor.fetchall()
for row in rows:
    print(row.Name, row.ListPrice)
conn.commit()

중첩된 트랜잭션과 저장 지점

SQL Server는 트랜잭션 내 부분 롤백을 위한 저장점을 지원합니다.

cursor = conn.cursor()

cursor.execute("BEGIN TRANSACTION")
cursor.execute("CREATE TABLE #SaveDemo (Name NVARCHAR(50))")
cursor.execute("INSERT INTO #SaveDemo (Name) VALUES ('Widget')")

cursor.execute("SAVE TRANSACTION SaveDemoPoint")
cursor.execute("INSERT INTO #SaveDemo (Name) VALUES ('Gadget')")

# Roll back to savepoint, keeping first insert
cursor.execute("ROLLBACK TRANSACTION SaveDemoPoint")

cursor.execute("COMMIT TRANSACTION")

교착 상태 처리

교착 상태는 두 트랜잭션이 서로의 잠금을 기다리는 동안 발생합니다. SQL Server는 교착 상태를 자동으로 감지하고 한 트랜잭션을 종료합니다.

import time

def execute_with_retry(conn, cursor, sql, params=None, max_retries=3):
    """Execute SQL with deadlock retry logic."""
    for attempt in range(max_retries):
        try:
            cursor.execute(sql, params)
            return
        except mssql_python.OperationalError as e:
            if "1205" in str(e):  # Deadlock error number
                if attempt < max_retries - 1:
                    conn.rollback()  # Clear the failed transaction
                    time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
                    continue
            raise
    raise Exception(f"Failed after {max_retries} attempts")

모범 사례

거래 시간을 짧게 하여 잠금 기간과 교착 상태를 최소화하세요.
원자여야 할 다중 문장 트랜잭션에는 autocommit=False를 사용하세요.

예외는 항상 롤백으로 처리하세요:

conn = None
try:
     conn = mssql_python.connect(connection_string)
     cursor = conn.cursor()
     cursor.execute("CREATE TABLE #RollbackPattern (ID INT, Name NVARCHAR(50))")
     cursor.execute("INSERT INTO #RollbackPattern (ID, Name) VALUES (1, 'Widget')")
     cursor.execute("UPDATE #RollbackPattern SET Name = 'Updated Widget' WHERE ID = 1")
     conn.commit()
except Exception:
     if conn is not None:
         conn.rollback()
     raise
finally:
     if conn is not None:
         conn.close()

컨텍스트 매니저를 사용 해 거래를 자동으로 관리하세요. 컨텍스트 매니저는 클린 종료 시 커밋하고 예외 시 롤백합니다.

with mssql_python.connect(connection_string) as conn:
     cursor = conn.cursor()
     cursor.execute("CREATE TABLE #ContextManagerDemo (ID INT, Name NVARCHAR(50))")
     cursor.execute("INSERT INTO #ContextManagerDemo (ID, Name) VALUES (1, 'Widget')")
     cursor.execute("UPDATE #ContextManagerDemo SET Name = 'Committed Widget' WHERE ID = 1")
# Committed automatically on exit

일관성 요구사항과 성능 요구사항을 바탕으로 적절한 격리 수준을 선택하세요.

읽기-수정-쓰기 패턴에 락 힌트를 사용하여 업데이트 손실을 방지하세요. 같은 거래 내에서 업데이트될 값을 읽을 때, SELECT와 같은 WITH (UPDLOCK, ROWLOCK) 힌트를 활용해 조기에 잠금장치를 획득하고 일관된 잠금 순서를 확립하여 교착 상태를 줄이세요.

# Good: Acquire lock during read to prevent lost update pattern
cursor.execute("""
     SELECT Balance FROM Accounts 
     WITH (UPDLOCK, ROWLOCK) 
     WHERE ID = %(id)s
""", {"id": account_id})
balance = cursor.fetchval()

if balance >= amount:
     cursor.execute("""
         UPDATE Accounts SET Balance = Balance - %(amount)s 
         WHERE ID = %(id)s
     """, {"amount": amount, "id": account_id})

교착 상태 같은 일시적 실패에 대해 재시도 로직을 구현하세요.

예시: 자금 이체 (원자 조작)

이 예시는 동시 상황에서 업데이트 손실을 방지하기 위한 락 힌트를 가진 원자 전송 논리를 보여줍니다:

def transfer_funds(conn, from_account, to_account, amount):
    """Transfer funds atomically between accounts."""
    cursor = conn.cursor()
    
    try:
        # Read balance with lock hint to prevent lost updates
        cursor.execute(
            "SELECT Balance FROM Accounts WITH (UPDLOCK, ROWLOCK) WHERE AccountID = %(account_id)s",
            {"account_id": from_account}
        )
        balance = cursor.fetchval()
        
        if balance is None:
            raise ValueError(f"Source account {from_account} not found")
        
        if balance < amount:
            raise ValueError("Insufficient funds")
        
        # Debit source account
        cursor.execute(
            "UPDATE Accounts SET Balance = Balance - %(amount)s WHERE AccountID = %(account_id)s",
            {"amount": amount, "account_id": from_account}
        )
        
        # Credit destination account
        cursor.execute(
            "UPDATE Accounts SET Balance = Balance + %(amount)s WHERE AccountID = %(account_id)s",
            {"amount": amount, "account_id": to_account}
        )
        
        if cursor.rowcount != 1:
            raise ValueError(f"Destination account {to_account} not found")
        
        conn.commit()
        print(f"Transferred ${amount} from {from_account} to {to_account}")
        
    except Exception:
        conn.rollback()
        raise

SELECT 문의 잠금 힌트 WITH (UPDLOCK, ROWLOCK) 는 잠금이 초기에 획득되도록 보장합니다. 이렇게 하면 다른 트랜잭션이 같은 잔액을 동시에 읽는 것을 방지하고, 두 트랜잭션이 기존 잔액을 읽고 별도의 업데이트를 수행하며 마지막 업데이트만 유지되는 손실 업데이트 상황이 발생합니다.

임시 테이블 스코핑

임시 테이블(#tablename)은 세션에 범위가 지정되어 있지만, 생성은 현재 트랜잭션의 일부입니다. 임시 테이블을 만들고 트랜잭션이 롤백하면, 임시 테이블은 폐기됩니다:

conn = mssql_python.connect(connection_string)  # autocommit=False
cursor = conn.cursor()

cursor.execute("CREATE TABLE #Staging (ID INT, Name NVARCHAR(50))")
cursor.execute("INSERT INTO #Staging VALUES (1, 'test')")

# Rollback removes the temp table entirely
conn.rollback()

# This fails: Invalid object name '#Staging'
try:
    cursor.execute("SELECT * FROM #Staging")
except mssql_python.ProgrammingError:
    print("Temp table was dropped by rollback")

임시 테이블을 데이터 트랜잭션과 독립적으로 유지하려면 테이블을 생성한 후 커밋하세요:

cursor.execute("CREATE TABLE #Staging (ID INT, Name NVARCHAR(50))")
conn.commit()  # Temp table persists regardless of later rollbacks

# Now data operations can roll back without losing the table
cursor.execute("INSERT INTO #Staging VALUES (1, 'test')")
conn.rollback()  # Data is gone, but #Staging still exists

자동 커밋이 필요한 DDL 문장

CREATE DATABASE, ALTER DATABASE, DROP DATABASE와 같은 일부 DDL 문은 트랜잭션 내부에서 실행할 수 없습니다. 실행하기 전에 autocommit=True을(를) 설정하세요:

conn.autocommit = True
cursor.execute("CREATE DATABASE TestDB")
conn.autocommit = False  # Return to transactional mode

로 실행 CREATE DATABASEautocommit=False하면 오류가 발생합니다: CREATE DATABASE statement not allowed within multi-statement transaction.

피드백

이 페이지가 도움이 되었나요?

Last updated on 2026-07-22