Správa transakcí pomocí mssql-python

Ovladač mssql-python podporuje plnou kontrolu transakcí, včetně úrovní commit, rollback, konfigurace automatického commitu a izolace transakcí.

Základy transakcí

Transakce seskupuje sekvenci databázových operací do jedné jednotky práce. Transakce se řídí vlastnostmi ACID:

Atomicita: Všechny operace uspějí, nebo všechny selžou.
Konzistence: Databáze zůstává v platném stavu.
Izolace: Současné transakce si navzájem nepřekážejí.
Odolnost: Závazné změny přežijí selhání systému.

Režim automatického dokončování

Nastavení autocommit určuje, zda jsou změny automaticky potvrzeny.

Autocommit deaktivován (výchozí)

Ve výchozím nastavení autocommit=False. Musíte explicitně provádět změny.

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()

Pokud nepotvrdíte transakci, ovladač po uzavření připojení zahodí změny.

Autocommit povolen

Když nastavíte autocommit=True, ovladač okamžitě commituje každý příkaz:

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

Když je autocommit povolen, nemůžete provést rollback více příkazů jako celku. Používejte autocommit jen tehdy, když je to vhodné pro váš případ použití.

Zapsání a zrušení změn

Závazek

Zavolejte commit(), aby se čekající změny natrvalo uložily:

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

Vrácení zpět

Pro zrušení čekajících změn volejte 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

Potvrzení a vrácení změn na úrovni kurzoru

Pro usnadnění můžete na kurzorech volat commit() a 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

Potvrzení a vrácení změn prováděné na úrovni kurzoru ovlivňují všechny kurzory ve stejném připojení, nejen kurzor, na kterém je voláte.

Kontextoví manažeři

Správce kontextu připojení potvrdí transakci při korektním ukončení a v případě výjimky ji zruší. Spojení se vždy při ukončení uzavře. Když nastavíte autocommit=True, volání commit a rollback nemají žádný vliv.

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

Pokud dojde k výjimce, transakce je vrácena zpět:

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

Úrovně izolace transakcí

Úrovně izolace určují, jak transakce interagují s probíhajícími transakcemi. Nastavte úroveň izolace pomocí 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
)

Dostupné úrovně izolace

Konstanta	Popis
`SQL_TXN_READ_UNCOMMITTED`	Může číst nepotvrzené změny z jiných transakcí (je povoleno špinavé čtení)
`SQL_TXN_READ_COMMITTED`	Čte pouze potvrzená data (výchozí nastavení v SQL Serveru)
`SQL_TXN_REPEATABLE_READ`	Zaručuje konzistentní čtení v rámci transakce
`SQL_TXN_SERIALIZABLE`	Nejvyšší izolace; Transakce se zdají probíhat postupně

Vyberte úroveň izolace

Případ použití	Doporučená úroveň
Obecné pracovní zátěže OLTP	`READ_COMMITTED` (výchozí)
Zprávy, které potřebují konzistentní snímky	`REPEATABLE_READ` nebo snímek
Finanční výpočty vyžadující přesnost	`SERIALIZABLE`
Pracovní zátěže s velkým množstvím čtení tolerují zastaralá data	`READ_UNCOMMITTED`

Izolace snímků

Pro izolaci snímků použijte Transact-SQL (T-SQL). Izolace snímku využívá verzování řádků v tempdb, což může zvýšit nároky na úložiště při vysokém zatížení zápisem.

# 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()

Vnořené transakce a uložené body

SQL Server podporuje body uložení pro částečné vrácení změn v rámci transakce.

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")

Zpracování vzájemných zablokování

Patové situace nastávají, když dvě transakce čekají na vzájemné zámky. SQL Server automaticky detekuje zablokování a ukončuje jednu transakci.

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")

Osvědčené postupy

Udržujte transakce krátké , abyste minimalizovali dobu uzamčení a možnost zablokování.
Použijte autocommit=False pro transakce s více příkazy, které mají být atomické.

Vždy řešte výjimky s rollbackem:

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()

Používejte správce kontextu pro automatickou správu transakcí. Správce kontextu při bezchybném ukončení potvrdí transakci a při výjimce ji vrátí zpět.

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

Vyberte vhodné úrovně izolace podle požadavků na konzistenci versus výkonnost.

Používejte indikátory zámků pro sekvence čtení–úprava–zápis, abyste zabránili ztraceným aktualizacím. Když čtete hodnotu, která se bude aktualizovat v rámci jedné transakce, použijte nápovědy jako WITH (UPDLOCK, ROWLOCK) na SELECT pro získání zámků dříve a vytvoření konzistentního pořadí zámků, což snižuje riziko zablokování.

# 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})

Implementujte logiku opakovaného pokusu pro přechodné selhání, jako jsou zablokování.

Příklad: Převod prostředků (atomová operace)

Tento příklad demonstruje atomickou přenosovou logiku s náznaky zámku, aby se zabránilo ztrátě aktualizací v souběžných scénářích:

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

Indikace uzamčení WITH (UPDLOCK, ROWLOCK) u příkazu SELECT zajišťují, aby byl zámek získán brzy. To zabraňuje tomu, aby jiná transakce současně přečetla stejný zůstatek a vytvořila scénář ztracené aktualizace, kdy obě transakce čtou starý zůstatek, provedou samostatné aktualizace a pouze poslední aktualizace zůstane.

Měření temp tabulky

Dočasné tabulky (#tablename) jsou vázány na relaci, ale jejich vytvoření je součástí aktuální transakce. Pokud vytvoříte dočasnou tabulku a dojde k vrácení transakce, dočasná tabulka se zruší:

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")

Chcete-li zachovat dočasnou tabulku nezávislou na datové transakci, potvrďte ji po jejím vytvoření:

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

Příkazy DDL, které vyžadují automatické potvrzení

Některé DDL příkazy, jako CREATE DATABASE, ALTER DATABASE, a DROP DATABASE, nemohou běžet uvnitř transakce. Nastavte autocommit=True před jejich spuštěním:

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

Pokud spustíte CREATE DATABASE s autocommit=False, zobrazí se chyba: CREATE DATABASE statement not allowed within multi-statement transaction.

Váš názor

Byla tato stránka užitečná?

Last updated on 2026-07-22