Gestione delle transazioni con mssql-python

Il driver mssql-python supporta il controllo completo delle transazioni, inclusi commit, rollback, configurazione autocommit e livelli di isolamento delle transazioni.

Nozioni di base sulle transazioni

Una transazione raggruppa una sequenza di operazioni nel database in un'unica unità di lavoro. Le transazioni seguono le proprietà ACID:

• Atomicità: Tutte le operazioni hanno successo o falliscono tutte.
• Coerenza: Il database rimane in uno stato valido.
• Isolamento: le transazioni concorrenti non interferiscono tra loro.
• Durabilità: I cambiamenti decisi sopravvivono ai guasti del sistema.

Modalità autocommit

L'impostazione autocommit controlla se le modifiche vengono salvate automaticamente.

Autocommit disabilitato (impostazione predefinita)

Per impostazione predefinita, autocommit=False. Devi eseguire esplicitamente il commit delle modifiche.

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

Se non esegui il commit, il driver scarta le modifiche quando la connessione si chiude.

Autocommit abilitato

Quando si imposta autocommit=True, il driver esegue immediatamente il commit di ogni istruzione:

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

Eseguire il commit e il rollback

Commettere

Chiamata commit() per rendere permanenti i cambiamenti in sospeso:

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

Ripristino

Per scartare le modifiche in sospeso, chiama 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 e rollback a livello di cursore

Per comodità, puoi chiamare commit() e rollback() sui cursori:

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

Gestori di contesto

Il gestore di contesto della connessione conferma la transazione in caso di uscita corretta e l'annulla se si verifica un'eccezione. La connessione si chiude sempre all'uscita. Quando imposti autocommit=True, le chiamate di commit e rollback non hanno effetto.

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

Se si verifica un'eccezione, la transazione viene annullata indietro:

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

Livelli di isolamento delle transazioni

I livelli di isolamento controllano come le transazioni interagiscono con le transazioni concorrenti. Imposta il livello di isolamento usando 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
)

Livelli di isolamento disponibili

Scegli un livello di isolamento

Isolamento dello snapshot

Per l'isolamento degli snapshot, usa Transact-SQL (T-SQL). L'isolamento snapshot utilizza il controllo delle versioni delle righe in tempdb, il che può aumentare i requisiti di archiviazione con carichi di lavoro con scritture intensive.

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

Transazioni annidate e punti di salvataggio

SQL Server supporta i punti di salvataggio per il parziale rollback all'interno di una transazione.

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

Gestire i deadlock

I deadlock si verificano quando due transazioni attendono i blocchi l'uno dell'altro. SQL Server rileva automaticamente i blocchi e termina una transazione.

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

Procedure consigliate

• Mantieni le transazioni brevi per minimizzare la durata del blocco e il potenziale deadlock.

• Usa autocommit=False per transazioni multi-istruzione che dovrebbero essere atomiche.

• Gestisci sempre le eccezioni con il 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()
  

• Usa i gestori contestuali per gestire automaticamente le transazioni. Il gestore di contesto esegue il commit quando termina senza errori ed esegue il rollback in caso di eccezione.

  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
  

• Scegli livelli di isolamento appropriati in base ai tuoi requisiti di coerenza rispetto alle esigenze di prestazione.

• Usa suggerimenti di blocco per pattern di lettura-modifica-scrittura per evitare aggiornamenti persi. Quando leggi un valore che verrà aggiornato all'interno della stessa transazione, usa hint come WITH (UPDLOCK, ROWLOCK) nella SELECT per acquisire i lock in anticipo e stabilire un ordine coerente di acquisizione dei lock, riducendo il rischio di deadlock.

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

• Implementa la logica di ritentazione per fallimenti transitori come i deadlock.

Esempio: Trasferimento di fondi (operazione atomica)

Questo esempio dimostra la logica di trasferimento atomico con suggerimenti di blocco per prevenire aggiornamenti persi in scenari concorrenti:

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

Gli indizi WITH (UPDLOCK, ROWLOCK) di blocco sul modulo SELECT assicurano che il blocco venga acquisito in anticipo. Questo impedisce a un'altra transazione di leggere contemporaneamente lo stesso saldo, creando uno scenario di aggiornamento perso, in cui entrambe le transazioni leggono il vecchio saldo, effettuano aggiornamenti separati e solo l'ultimo aggiornamento persiste.

Ambito della tabella temporanea

Le tabelle temporanee (#tablename) sono inserite nella sessione, ma la loro creazione fa parte della transazione corrente. Se crei una tabella temporanea e la transazione viene annullata indietro, la tabella temporanea viene eliminata:

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

Per mantenere una tabella temporanea indipendente dalla tua transazione dati, effettua un commit dopo averla creata:

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

Istruzioni DDL che richiedono l'autocommit

Alcune istruzioni DDL, come CREATE DATABASE, ALTER DATABASE, e DROP DATABASE, non possono essere eseguite all'interno di una transazione. Impostate autocommit=True prima di eseguirle:

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

Se esegui CREATE DATABASE con autocommit=False, ottieni un errore: CREATE DATABASE statement not allowed within multi-statement transaction.

Contenuti correlati

• Gestione delle connessioni
• Esecuzione delle query
• Gestione degli errori
• Gestione del cursore