Transaktionsmanagement mit mssql-python

Der mssql-python-Treiber unterstützt die vollständige Transaktionskontrolle, einschließlich Commit-, Rollback-, Autocommit-Konfiguration und Transaktionsisolation.

Transaktionsgrundlagen

Eine Transaktion gruppiert eine Abfolge von Datenbankoperationen zu einer einzigen Arbeitseinheit. Die Transaktionen folgen den ACID-Eigenschaften:

• Atomität: Alle Operationen sind erfolgreich oder alle scheitern.
• Konsistenz: Die Datenbank bleibt in einem gültigen Zustand.
• Isolation: Gleichzeitige Transaktionen stören sich nicht gegenseitig.
• Haltbarkeit: Engagierte Änderungen überstehen Systemausfälle.

Autocommit-Modus

Die Einstellung autocommit steuert, ob Änderungen automatisch ausgeführt werden.

Autocommit deaktiviert (Standard)

Standardmäßig ist dies autocommit=False. Du musst ausdrücklich Änderungen vornehmen.

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

Wenn du dich nicht bindest, verwirft der Treiber die Änderungen, wenn die Verbindung geschlossen wird.

Autocommit aktiviert

Wenn Sie autocommit=True festlegen, übernimmt der Treiber jede Anweisung sofort:

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 und Rollback

Commit

Rufen Sie commit() auf, um ausstehende Änderungen dauerhaft zu übernehmen:

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

Um ausstehende Änderungen zu verwerfen, rufen Sie rollback() auf:

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 und Rollback auf Cursor-Ebene

Der Einfachheit halber kannst du bei Cursorn commit() und rollback() aufrufen:

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

Kontextmanager

Der Verbindungs-Kontextmanager verbindet die Transaktion beim sauberen Abschluss und rollt sie zurück, wenn eine Ausnahme auftritt. Die Verbindung schließt sich immer beim Austreten. Wenn du setzt autocommit=True, haben Commit- und Rollback-Aufrufe keine Auswirkung.

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

Tritt eine Ausnahme ein, wird die Transaktion zurückgesetzt:

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

Transaktionsisolationsstufen

Isolationsstufen steuern, wie Transaktionen mit gleichzeitigen Transaktionen interagieren. Stellen Sie das Isolationsniveau mit set_attr()ein:

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
)

Verfügbare Isolationsstufen

Wählen Sie eine Isolationsstufe

Momentaufnahmeisolation

Zur Snapshot-Isolation verwenden Sie Transact-SQL (T-SQL). Snapshot-Isolation verwendet Zeilenversionierung in tempdb, was den Speicherbedarf bei hoher Schreiblast erhöhen kann.

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

Verschachtelte Transaktionen und Speicherpunkte

SQL Server unterstützt Speicherpunkte für teilweise Rollbacks innerhalb einer Transaktion.

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

Deadlocks beheben

Deadlocks treten auf, wenn zwei Transaktionen auf die Sperre der jeweils anderen warten. SQL Server erkennt automatisch Deadlocks und beendet eine Transaktion.

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

Bewährte Methoden

• Halten Sie die Transaktionen kurz , um die Sperrdauer und das Deadlock-Potenzial zu minimieren.

• Verwenden Sie autocommit=False für Mehraussagen-Transaktionen, die atomar sein sollten.

• Behandle Ausnahmen immer mit Rollback:

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

• Nutze Kontextmanager, um Transaktionen automatisch zu verwalten. Der Kontextmanager führt bei fehlerfreiem Verlassen ein Commit durch und führt bei einer Ausnahme ein Rollback durch.

  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
  

• Wähle passende Isolationsstufen basierend auf deinen Konsistenzanforderungen im Vergleich zu den Leistungsanforderungen.

• Verwenden Sie Lock-Hinweise für Read-Modify-Write-Muster , um verlorene Updates zu verhindern. Wenn Sie einen Wert lesen, der innerhalb derselben Transaktion aktualisiert wird, verwenden Sie Hinweise wie WITH (UPDLOCK, ROWLOCK) bei SELECT, um Schlösser frühzeitig zu erhalten und eine konsistente Lock-Reihenfolge herzustellen, was das Deadlock-Risiko verringert.

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

• Implementiere Retry-Logik für vorübergehende Fehler wie Deadlocks.

Beispiel: Geld überweisen (atomare Operation)

Dieses Beispiel demonstriert die atomare Transferlogik mit Lock-Hinweisen, um verlorene Updates in gleichzeitigen Szenarien zu verhindern:

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

Die Schlosshinweise WITH (UPDLOCK, ROWLOCK) auf der SELECT stellen sicher, dass das Schloss frühzeitig erlangt wird. Dies verhindert, dass eine andere Transaktion gleichzeitig denselben Saldo liest und ein Verloren-Update-Szenario erzeugt, bei dem beide Transaktionen den alten Saldo lesen, separate Updates durchführen und nur das letzte Update erhalten bleibt.

Temporäre Tabellenabgrenzung

Temporäre Tabellen (#tablename) sind auf die Sitzung abgegrenzt, aber ihre Erstellung ist Teil der aktuellen Transaktion. Wenn du eine temporäre Tabelle erstellst und die Transaktion zurückrollt, wird die temporäre Tabelle entfernt:

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

Um eine temporäre Tabelle unabhängig von Ihrer Datentransaktion zu halten, führen Sie nach dem Erstellen ein Commit aus:

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-Anweisungen, die Autocommit erfordern

Einige DDL-Anweisungen, wie CREATE DATABASE, ALTER DATABASE, und DROP DATABASE, können innerhalb einer Transaktion nicht ausgeführt werden. Setzen Sie autocommit=True vor der Ausführung fest:

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

Wenn Sie mit CREATE DATABASEausführenautocommit=False, erhalten Sie einen Fehler:CREATE DATABASE statement not allowed within multi-statement transaction.

Verwandte Inhalte

• Verbindungsverwaltung
• Abfragen ausführen
• Fehlerbehandlung
• Cursorverwaltung