Gestión de transacciones con mssql-python

El controlador mssql-python admite un control completo de las transacciones, incluido commit, rollback, la configuración de autocommit y los niveles de aislamiento de las transacciones.

Conceptos básicos de transacciones

Una transacción agrupa una secuencia de operaciones de base de datos en una única unidad de trabajo. Las transacciones siguen las propiedades ACID:

• Atomicidad: Todas las operaciones tienen éxito o fracasan.
• Consistencia: La base de datos permanece en un estado válido.
• Aislamiento: Las transacciones concurrentes no interfieren entre sí.
• Durabilidad: Los cambios comprometidos sobreviven a fallos del sistema.

Modo de confirmación automática

La configuración autocommit controla si los cambios se confirman automáticamente.

Autocommit desactivado (por defecto)

De forma predeterminada, autocommit=False. Debes confirmar los cambios explícitamente.

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 no confirmas los cambios, el controlador los descarta cuando se cierra la conexión.

Autocommit activado

Cuando se establece autocommit=True, el controlador confirma cada sentencia inmediatamente:

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

Confirmación y reversión

Confirmar

Llamada commit() para hacer permanentes los cambios pendientes:

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

Reversión

Para descartar los cambios pendientes, llama a 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

Confirmación y reversión por cursor

Para mayor comodidad, puedes llamar a commit() y rollback() sobre los cursores:

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

Administradores de contexto

El gestor de contexto de la conexión confirma la transacción al finalizar correctamente y la revierte si se produce una excepción. La conexión siempre se cierra al salir. Cuando configuras autocommit=True, las llamadas de commit y rollback no tienen efecto.

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

Si ocurre una excepción, la transacción se revierte:

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

Niveles de aislamiento de transacciones

Los niveles de aislamiento controlan cómo las transacciones interactúan con las transacciones concurrentes. Establezca el nivel de aislamiento 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
)

Niveles de aislamiento disponibles

Elige un nivel de aislamiento

Aislamiento de instantáneas

Para aislamiento de instantáneas, utiliza Transact-SQL (T-SQL). El aislamiento de instantáneas utiliza versionado de filas en tempdb, lo que puede aumentar los requisitos de almacenamiento bajo cargas de trabajo de escritura pesadas.

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

Transacciones anidadas y puntos de guardado

SQL Server admite puntos de guardado para realizar una reversión parcial dentro de una transacción.

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

Controlar interbloqueos

Los bloqueos ocurren cuando dos transacciones esperan a que se bloqueen mutuamente. SQL Server detecta automáticamente bloqueos y termina una transacción.

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

procedimientos recomendados

• Mantén las transacciones cortas para minimizar el tiempo de bloqueo y el riesgo de interbloqueo.

• Utiliza autocommit=False para transacciones con varias instrucciones que deberían ser atómicas.

• Siempre gestiona las excepciones con 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()
  

• Utiliza gestores de contexto para gestionar las transacciones automáticamente. El gestor de contexto confirma los cambios si finaliza correctamente y los revierte si se produce una excepción.

  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
  

• Elige niveles de aislamiento adecuados según tus requisitos de consistencia frente a tus necesidades de rendimiento.

• Utiliza consejos de bloqueo para patrones de lectura, modificación y escritura para evitar la pérdida de actualizaciones. Cuando leas un valor que se va a actualizar dentro de la misma transacción, usa sugerencias como WITH (UPDLOCK, ROWLOCK) en la instrucción SELECT para adquirir los bloqueos de forma anticipada y establecer un orden coherente de adquisición de bloqueos, lo que reduce el riesgo de interbloqueo.

  # 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 lógica de reintentos para fallos transitorios como los bloqueos.

Ejemplo: Transferir fondos (operación atómica)

Este ejemplo demuestra lógica de transferencia atómica con pistas de bloqueo para evitar actualizaciones perdidas en escenarios concurrentes:

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

Las indicaciones WITH (UPDLOCK, ROWLOCK) de bloqueo en la instrucción SELECT garantizan que el bloqueo se adquiera de forma temprana. Esto evita que otra transacción lea el mismo saldo simultáneamente y se genere un escenario de actualización perdida donde ambas transacciones lean el saldo antiguo, realicen actualizaciones separadas y solo la última actualización persista.

Alcance de la tabla temporal

Las tablas temporales (#tablename) están limitadas a la sesión, pero su creación se realiza dentro de la transacción actual. Si creas una tabla temporal y la transacción se revierte, la tabla temporal se elimina:

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

Para mantener una tabla temporal independiente de tu transacción de datos, haz commit después de crearla:

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

Sentencias DDL que requieren el autocommit

Algunas sentencias DDL, como CREATE DATABASE, ALTER DATABASE, y DROP DATABASE, no pueden ejecutarse dentro de una transacción. Configura autocommit=True antes de ejecutarlos:

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

Si ejecutas CREATE DATABASE con autocommit=False, obtienes un error: CREATE DATABASE statement not allowed within multi-statement transaction.

Contenido relacionado

• Administración de conexiones
• Ejecutar consultas
• Control de errores
• Gestión del cursor