Transactiebeheer met mssql-python

De mssql-python-driver ondersteunt volledige transactiecontrole, inclusief commit, rollback, autocommit-configuratie en transactieisolatieniveaus.

Basisprincipes van transacties

Een transactie groepeert een reeks databasebewerkingen in één eenheid werk. Transacties volgen de ACID-eigenschappen:

• Atomiciteit: Alle operaties slagen of mislukken allemaal.
• Consistentie: De database blijft in een geldige staat.
• Isolatie: Gelijktijdige transacties interfereren elkaar niet.
• Duurzaamheid: Toegewijde wijzigingen overleven systeemstoringen.

Modus Automatisch aanpassen

Met de instelling autocommit wordt bepaald of wijzigingen automatisch worden doorgevoerd.

Autocommit uitgeschakeld (standaard)

autocommit=FalseStandaard. Je moet expliciet veranderingen doorvoeren.

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

Als je de wijzigingen niet vastlegt, verwerpt de driver ze zodra de verbinding wordt gesloten.

Autocommit ingeschakeld

Wanneer je autocommit=True instelt, commit de driver elke instructie onmiddellijk:

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

Doorvoeren en terugdraaien

Doorvoeren

Bel commit() om lopende wijzigingen permanent te maken:

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

Terugdraaien

Roep rollback() aan om openstaande wijzigingen te verwerpen:

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 en rollback op curniveau

Voor het gemak kun je commit() en rollback() op cursors aanroepen:

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

Contextbeheerders

De contextmanager voor de verbinding bevestigt de transactie bij een foutloze afsluiting en draait deze terug als er een uitzondering optreedt. De verbinding wordt altijd gesloten bij afsluiten. Wanneer je autocommit=True instelt, hebben aanroepen van commit en rollback geen effect.

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

Als er een uitzondering optreedt, wordt de transactie teruggedraaid:

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

Isolatieniveaus voor transacties

Isolatieniveaus bepalen hoe transacties interageren met gelijktijdige transacties. Stel het isolatieniveau in met :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
)

Beschikbare isolatieniveaus

Kies een isolatieniveau

Isolatie van momentopnamen

Voor snapshot-isolatie gebruik je Transact-SQL (T-SQL). Snapshot-isolatie gebruikt rijversiebeheer in tempdb, wat de opslagvereisten kan verhogen bij schrijfintensieve workloads.

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

Geneste transacties en savepoints

SQL Server ondersteunt savepoints voor gedeeltelijke rollback binnen een transactie.

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

Impasses afhandelen

Deadlocks ontstaan wanneer twee transacties wachten op elkaars vergrendelingen. SQL Server detecteert automatisch deadlocks en beëindigt één transactie.

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

Beste praktijken

• Houd transacties kort om de duur van de vergrendeling en de kans op deadlocks te minimaliseren.

• Gebruik autocommit=False voor transacties met meerdere statements die atomair moeten zijn.

• Altijd uitzonderingen met rollback behandelen :

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

• Gebruik contextmanagers om transacties automatisch te beheren. De contextbeheerder bevestigt bij een correcte afsluiting en rolt terug als er een uitzondering optreedt.

  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
  

• Kies geschikte isolatieniveaus op basis van je consistentie-eisen versus prestatie-behoeften.

• Gebruik vergrendelingshints voor read-modify-write-patronen om verloren updates te voorkomen. Wanneer je een waarde leest die binnen dezelfde transactie wordt bijgewerkt, gebruik dan hints zoals WITH (UPDLOCK, ROWLOCK) op de SELECT om sloten vroegtijdig te verkrijgen en consistente lock-ordering te vestigen, wat het risico op deadlocks vermindert.

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

• Implementeer herkansingslogica voor tijdelijke storingen zoals deadlocks.

Voorbeeld: Overdrachtsfondsen (atomaire operatie)

Dit voorbeeld demonstreert atomaire overdrachtslogica met lock hints om verloren updates in gelijktijdige scenario's te voorkomen:

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

De lock-hints WITH (UPDLOCK, ROWLOCK) op de SELECT zorgen ervoor dat het slot vroeg wordt verkregen. Dit voorkomt dat een andere transactie hetzelfde saldo gelijktijdig leest en een verloren update-scenario creëert waarbij beide transacties het oude saldo lezen, aparte updates uitvoeren en alleen de laatste update blijft bestaan.

Bereik van tijdelijke tabellen

Tijdelijke tabellen (#tablename) zijn beperkt tot de sessie, maar hun creatie maakt deel uit van de huidige transactie. Als je een tijdelijke tabel aanmaakt en de transactie wordt teruggerold, wordt de tijdelijke tabel verwijderd:

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

Om een tijdelijke tabel onafhankelijk te houden van je gegevenstransactie, voer je na het aanmaken ervan een commit uit:

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-statements die autocommit vereisen

Sommige DDL-instructies, zoals CREATE DATABASE, ALTER DATABASE, en DROP DATABASE, kunnen niet binnen een transactie worden uitgevoerd. Stel autocommit=True in voordat je deze uitvoert:

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

Als je met CREATE DATABASEuitvoertautocommit=False, krijg je een foutmelding:CREATE DATABASE statement not allowed within multi-statement transaction.

Verwante onderwerpen

• Verbindingsbeheer
• Voer queries uit
• Foutafhandeling
• Cursorbeheer