Gestion des transactions avec mssql-python

Le pilote mssql-python prend en charge le contrôle complet des transactions, y compris les niveaux de commit, rollback, configuration autocommit et isolation des transactions.

Informations de base sur les transactions

Une transaction regroupe une séquence d’opérations de base de données en une seule unité de travail. Les transactions suivent les propriétés ACID :

• Atomicité : Toutes les opérations réussissent ou toutes échouent.
• Cohérence : La base de données reste en état valide.
• Isolation : Les transactions concurrentes ne s’interfèrent pas entre elles.
• Durabilité : Les changements engagés survivent aux pannes système.

Mode de validation automatique

Le autocommit paramètre détermine si les modifications sont automatiquement engagées.

Commit automatique désactivé (par défaut)

Par défaut, autocommit=False. Vous devez explicitement engager des modifications.

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

Si vous ne validez pas, le pilote annule les modifications lorsque la connexion est fermée.

Autocommit activé

Lorsque vous définissez autocommit=True, le pilote envoie chaque instruction immédiatement :

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

Validation et annulation

Validations

Appel commit() pour rendre permanents les changements en attente :

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

Retour arrière

Pour annuler les modifications en attente, appelez 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

Validation et annulation au niveau du curseur

Pour plus de commodité, vous pouvez appeler commit() et rollback() sur les curseurs :

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

Gestionnaires de contexte

Le gestionnaire de contexte de connexion envoie la transaction à la sortie propre et la revient en arrière si une exception survient. La connexion se coupe toujours à la sortie. Lorsque vous définissez autocommit=True, les appels d’engagement et de retour en arrière n’ont aucun effet.

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

En cas d’exception, la transaction est annulée :

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

Niveaux d’isolation des transactions

Les niveaux d’isolation contrôlent la manière dont les transactions interagissent avec les transactions concurrentes. Définissez le niveau d’isolation en utilisant 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
)

Niveaux d’isolement disponibles

Choisissez un niveau d’isolement

Isolation des instantanés

Pour l’isolation des instantanés, utilisez Transact-SQL (T-SQL). L’isolation des instantanés utilise le versionnement par lignes dans tempdb, ce qui peut augmenter les besoins de stockage sous des charges d’écriture lourdes.

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

Transactions imbriquées et points de sauvegarde

SQL Server prend en charge les points de sauvegarde pour un retour partiel en arrière au sein d’une transaction.

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

Gérer les interblocages

Les blocages surviennent lorsque deux transactions attendent les verrous de l’autre. SQL Server détecte automatiquement les blocages et termine une transaction.

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

Bonnes pratiques

• Gardez les transactions courtes pour minimiser la durée du verrouillage et le potentiel de blocage.

• Utilisez autocommit=False pour les transactions à plusieurs instructions devant être atomiques.

• Gérez toujours les exceptions avec un retour en arrière :

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

• Utilisez des gestionnaires de contexte pour gérer automatiquement les transactions. Le gestionnaire de contexte valide en cas de sortie normale et annule en cas d’exception.

  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
  

• Choisissez des niveaux d’isolation appropriés en fonction de vos exigences de cohérence par rapport aux besoins de performance.

• Utilisez des indications de verrouillage pour les scénarios de lecture-modification-écriture afin d’éviter la perte de mises à jour. Lorsque vous lisez une valeur qui sera mise à jour dans la même transaction, utilisez des indices comme WITH (UPDLOCK, ROWLOCK) sur le SELECT pour acquérir les verrous tôt et établir un ordre cohérent des verrous, ce qui réduit le risque de blocage.

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

• Mettez en place une logique de retentative pour les échecs transitoires comme les blocages.

Exemple : Transfert de fonds (opération atomique)

Cet exemple démontre la logique de transfert atomique avec des indices de verrouillage pour éviter les mises à jour perdues dans des scénarios concurrents :

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

Les indications WITH (UPDLOCK, ROWLOCK) de verrouillage sur l’instruction SELECT garantissent que le verrou est acquis dès le début. Cela empêche une autre transaction de lire simultanément le même solde et de créer un scénario de mise à jour perdue où les deux transactions lisent l’ancien solde, effectuent des mises à jour séparées, et seule la dernière mise à jour persiste.

Portée de la table temporaire

Les tables temporaires (#tablename) ont une portée limitée à la session, mais leur création s’inscrit dans la transaction en cours. Si vous créez une table temporaire et que la transaction est annulée, la table temporaire est supprimée :

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

Pour garder une table temporaire indépendante de votre transaction de données, validez après l’avoir créée :

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

Instructions DDL nécessitant une validation automatique

Certaines instructions DDL, telles que CREATE DATABASE, ALTER DATABASE, et DROP DATABASE, ne peuvent pas s’exécuter à l’intérieur d’une transaction. Définissez autocommit=True avant de les exécuter :

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

Si vous exécutez CREATE DATABASE avec autocommit=False, vous obtenez une erreur : CREATE DATABASE statement not allowed within multi-statement transaction.

Contenu connexe

• Gestion des connexions
• Exécution des requêtes
• Gestion des erreurs
• Gestion du curseur