Gestão de transações com mssql-python

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

Fundamentos das transações

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

• Atomicidade: Todas as operações têm sucesso ou todas falham.
• Consistência: A base de dados mantém-se num estado válido.
• Isolamento: Transações concorrentes não interferem entre si.
• Durabilidade: As alterações confirmadas persistem apesar de falhas do sistema.

Modo de confirmação automática

A definição autocommit controla se as alterações são guardadas automaticamente.

Confirmação automática desativada por predefinição

Por padrão, autocommit=False. Tem de submeter 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 não fizeres commit, o controlador descarta as alterações quando a conexão é encerrada.

Autocommit ativado

Quando defines autocommit=True, o controlador 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

Commit e rollback

Compromisso

Chame commit() para tornar definitivas 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 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 ao nível do cursor

Por conveniência, 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

Gestores de contexto

O gestor de contexto da ligação confirma a transação ao sair sem erros e reverte-a se ocorrer uma exceção. A conexão fecha-se sempre ao sair. Quando defines 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ções

Os níveis de isolamento controlam como as transações interagem com as 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 de Snapshot

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

# 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 restauro

O SQL Server suporta pontos de gravação para reversão parcial dentro de 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")

Lidar com bloqueios

Os deadlocks ocorrem quando duas transações aguardam os bloqueios um do outro. O SQL Server deteta automaticamente bloqueios e termina 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")

Melhores práticas

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

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

• Trate sempre 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 gestores de contexto para gerir transações automaticamente. O gestor de contexto confirma na saída sem erros e efetua uma reversão 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 requisitos de consistência versus as necessidades de desempenho.

• Utilize sugestões de bloqueio para padrões de leitura-modificação-escrita para evitar a perda de atualizações. Ao ler um valor que será atualizado na mesma transação, utilize sugestões como WITH (UPDLOCK, ROWLOCK) na instrução SELECT para adquirir bloqueios antecipadamente e estabelecer uma ordem consistente de bloqueio, o que reduz 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 um mecanismo de repetição para falhas transitórias, como interbloqueios.

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 concorrentes:

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

Os hints de bloqueio WITH (UPDLOCK, ROWLOCK) no SELECT garantem que o bloqueio é adquirido mais cedo. Isto impede que outra transação leia o mesmo saldo em simultâneo e crie um cenário de atualização perdida em que ambas as transações leem o saldo antigo, realizam atualizações separadas e apenas a última atualização persiste.

Âmbito da tabela temporária

As tabelas temporárias (#tablename) estão limitadas à sessão, mas a sua criação faz parte da transação atual. Se criares uma tabela temporária e a transação for revertida, a tabela temporária é eliminada:

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 sua transação de dados, faça commit depois de a criar:

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

Declarações DDL que exigem o autocommit

Algumas instruções DDL, como CREATE DATABASE, ALTER DATABASE, e DROP DATABASE, não podem ser executadas dentro de uma transação. Definir autocommit=True antes de os executar:

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

Se executar CREATE DATABASE com autocommit=False, obtém o erro: CREATE DATABASE statement not allowed within multi-statement transaction.

Conteúdo relacionado

• Gestão de ligações
• Executar consultas
• Tratamento de erros
• Gestão do cursor