Gerenciamento de transações com mssql-python

O driver mssql-python suporta controle total de transações, incluindo commit, rollback, configuração autocommit e níveis de isolamento de transações.

Noções básicas de transação

Uma transação agrupa uma sequência de operações de banco de dados em uma única unidade de trabalho. As transações seguem as propriedades ACID:

• Atomicidade: Todas as operações têm sucesso ou todas falham.
• Consistência: O banco de dados permanece em estado válido.
• Isolamento: Transações concorrentes não interferem umas com as outras.
• Durabilidade: Alterações confirmadas persistem mesmo após falhas do sistema.

Modo de confirmação automática

A configuração autocommit define se as alterações são confirmadas automaticamente.

Confirmação automática desativada (padrão)

Por padrão, autocommit=False. Você deve confirmar explicitamente as alterações.

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 você não se comprometer, o driver descarta as mudanças quando a conexão fecha.

Autocommit ativado

Quando você define autocommit=True, o driver confirma cada instrução imediatamente:

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

Confirmação e reversão

Confirmar

Chame commit() para tornar permanentes as alterações pendentes:

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

Reversão

Para descartar as alterações pendentes, chame 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 no nível do cursor

Por conveniência, você pode chamar commit() e rollback() em 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

Gerenciadores de contexto

O gerenciador de contexto da conexão efetiva a transação ao ser encerrado sem erros e reverte a transação se ocorrer uma exceção. A conexão sempre fecha na saída. Quando você define autocommit=True, as chamadas de commit e rollback não têm efeito.

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 ocorrer uma exceção, a transação é revertida:

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

Níveis de isolamento de transação

Níveis de isolamento controlam como as transações interagem com transações concorrentes. Defina o nível de 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
)

Níveis de isolamento disponíveis

Escolha um nível de isolamento

Isolamento por instantâneo

Para isolamento de snapshots, use Transact-SQL (T-SQL). O isolamento de snapshots utiliza versionamento de linhas em tempdb, o que pode aumentar os requisitos de armazenamento sob cargas de trabalho pesadas de escrita.

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

Transações aninhadas e pontos de salvamento

O SQL Server suporta pontos de salvamento para reversão parcial em uma transação.

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

Gerenciar deadlocks

Deadlocks ocorrem quando duas transações aguardam os bloqueios uma da outra. O SQL Server detecta automaticamente bloqueios e encerra uma transação.

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

Práticas recomendadas

• Mantenha as transações curtas para minimizar a duração do bloqueio e o potencial de deadlock.

• Use autocommit=False para transações com múltiplas instruções que devem ser atômicas.

• Sempre trate exceções com reversão:

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

• Use gerentes de contexto para gerenciar transações automaticamente. O gerenciador de contexto confirma no encerramento sem erros e faz rollback em caso de exceção.

  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
  

• Escolha níveis de isolamento adequados com base nos seus requisitos de consistência versus necessidades de desempenho.

• Use instruções de bloqueio para padrões de leitura, modificação e escrita para evitar atualizações perdidas. Ao ler um valor que será atualizado na mesma transação, use diretivas como WITH (UPDLOCK, ROWLOCK) no SELECT para adquirir bloqueios antecipadamente e estabelecer uma ordem consistente de bloqueios, reduzindo o risco de impasse.

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

• Implemente lógica de nova tentativa para falhas transitórias, como deadlocks.

Exemplo: Transferir fundos (operação atômica)

Este exemplo demonstra lógica de transferência atômica com dicas de bloqueio para evitar atualizações perdidas em cenários simultâneos:

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

As instruções de bloqueio WITH (UPDLOCK, ROWLOCK) na instrução SELECT garantem que o bloqueio seja obtido antecipadamente. Isso impede que outra transação leia o mesmo saldo simultaneamente e crie um cenário de atualização perdida, onde ambas as transações leem o saldo antigo, realizam atualizações separadas e apenas a última atualização persiste.

Escopo da tabela temporária

Tabelas temporárias (#tablename) têm escopo da sessão, mas sua criação faz parte da transação atual. Se você criar uma tabela temporária e a transação sofrer rollback, a tabela temporária será removida:

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 manter uma tabela temporária independente da transação de dados, execute um commit após criá-la:

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

Instruções DDL que exigem autocommit

Algumas instruções DDL, como CREATE DATABASE, ALTER DATABASE, e DROP DATABASE, não podem rodar dentro de uma transação. Defina autocommit=True antes de executá-los:

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

Se você rodar CREATE DATABASE com autocommit=False, recebe um erro: CREATE DATABASE statement not allowed within multi-statement transaction.

Conteúdo relacionado

• Gerenciamento de conexões
• Executar consultas
• Tratamento de erros
• Gerenciamento do cursor