使用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() 以使待处理更改永久生效：

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语句

某些DDL语句，如CREATE DATABASE、和ALTER DATABASEDROP DATABASE，不能在事务中运行。在执行它们之前，设置 autocommit=True：

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

如果使用 autocommit=False 运行 CREATE DATABASE，会报错：CREATE DATABASE statement not allowed within multi-statement transaction.

反馈

此页面是否有帮助？

Last updated on 2026-07-22