使用 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

Caution

啟用自動提交時，你無法將多個陳述式作為一組回滾。只有在適合你使用情境時才使用自動提交。

提交與回滾

承諾

呼叫 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

Note

在游標層級執行認可與回滾時，影響的是同一連線上的所有游標，而不只是你對其呼叫這些操作的那個游標。

情境管理器

連線內容管理器會在正常結束時提交交易，並在發生例外時回滾交易。連線總是在出口時關閉。當你設定 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

根據你的一致性需求與效能需求，選擇合適的隔離等級。

使用鎖定提示來進行讀取-修改-寫入模式 ，以防止更新遺失。當你讀取同一交易中會更新的數值時，利用像 WITH (UPDLOCK, ROWLOCK) SELECT 上的提示來提前取得鎖，建立一致的鎖序，降低死鎖風險。

# 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 語句

某些 DDL 語句，如 CREATE DATABASE、 ALTER DATABASE、 DROP DATABASE，無法在交易中執行。在執行它們之前，請先設定 autocommit=True：

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

如果你用 CREATE DATABASE執行autocommit=False，會收到錯誤：CREATE DATABASE statement not allowed within multi-statement transaction.

意見反應

此頁面對您有幫助嗎？

Last updated on 2026-07-22