Transaktionshantering med mssql-python

mssql-python-drivrutinen stödjer full transaktionskontroll, inklusive commit, rollback, autocommit-konfiguration och transaktionsisoleringsnivåer.

Grunderna för transaktioner

En transaktion grupperar en sekvens av databasoperationer till en enda arbetsenhet. Transaktioner följer ACID:s egenskaper:

Atomicitet: Alla operationer lyckas eller misslyckas.
Konsistens: Databasen förblir i ett giltigt tillstånd.
Isolering: Samtidiga transaktioner stör inte varandra.
Hållbarhet: Engagerade förändringar överlever systemfel.

Autocommit-läge

Inställningen autocommit styr om ändringar görs automatiskt.

Automatisk incheckning inaktiverat (som standard)

Som standard . autocommit=False Du måste uttryckligen genomföra förändringar.

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

Om du inte genomför en commit kasserar drivrutinen ändringarna när anslutningen stängs.

Autocommit aktiverad

När du ställer in autocommit=True bekräftar drivrutinen varje instruktion omedelbart:

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

Caution

När autocommit är aktiverat kan du inte rulla tillbaka flera satser som en grupp. Använd autocommit endast när det passar ditt användningsfall.

Spara och återgå

Commit

Anropa commit() för att göra väntande ändringar permanenta:

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

Tillbakagång

För att förkasta väntande ändringar, anropa 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

Incheckning och återställning på markörnivå

För enkelhetens skull kan du anropa commit() och rollback() på markörer:

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

Note

Commit och rollback på kursornivå påverkar alla kursorer på samma anslutning, inte bara den kursor som du anropar dem på.

Kontexthanterare

Kontexthanteraren för anslutningen slutför transaktionen vid normal avslutning och rullar tillbaka den om ett undantag inträffar. Anslutningen stängs alltid vid utgång. När du sätter autocommit=True, har commit- och rollback-anropen ingen effekt.

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

Om ett undantag inträffar rullas transaktionen tillbaka igen:

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

Transaktionsisoleringsnivåer

Isoleringsnivåer styr hur transaktioner interagerar med samtidiga transaktioner. Ställ in isoleringsnivån med :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
)

Tillgängliga isoleringsnivåer

Konstant	Beskrivning
`SQL_TXN_READ_UNCOMMITTED`	Kan läsa icke-committade ändringar från andra transaktioner (smutsiga läsningar tillåts)
`SQL_TXN_READ_COMMITTED`	Läser endast committed data (standard i SQL Server)
`SQL_TXN_REPEATABLE_READ`	Garanterar konsekventa läsningar inom en transaktion
`SQL_TXN_SERIALIZABLE`	Högsta isolering; Transaktioner verkar köras sekventiellt

Välj en isoleringsnivå

Användningsfall	Rekommenderad nivå
Allmänna OLTP-arbetsbelastningar	`READ_COMMITTED` (standardinställning)
Rapporter som behöver konsekventa ögonblicksbilder	`REPEATABLE_READ` eller snapshot
Finansiella beräkningar som kräver noggrannhet	`SERIALIZABLE`
Lästunga arbetsbelastningar som tolererar föråldrad data	`READ_UNCOMMITTED`

Isolering av ögonblicksbilder

För snapshot-isolering, använd Transact-SQL (T-SQL). Snapshot-isolering använder radversionering i tempdb, vilket kan öka lagringsbehovet under tunga skrivbelastningar.

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

Nästlade transaktioner och sparpunkter

SQL Server stöder sparpunkter för partiell återställning inom en transaktion.

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

Hantera dödlägen

Deadlocks uppstår när två transaktioner väntar på varandras lås. SQL Server upptäcker automatiskt deadlocks och avslutar en transaktion.

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

Metodtips

Håll transaktionerna korta för att minimera låstid och risk för deadlock.
Använd autocommit=False för multi-statement-transaktioner som ska vara atomära.

Hantera alltid undantag med 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()

Använd kontexthanterare för att hantera transaktioner automatiskt. Kontexthanteraren bekräftar transaktionen vid ett normalt avslut och rullar tillbaka vid undantag.

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

Välj lämpliga isoleringsnivåer baserat på dina konsekvenskrav kontra prestandabehov.

Använd låstips för läs-modifiera-skriv-mönster för att förhindra förlorade uppdateringar. När du läser ett värde som ska uppdateras inom samma transaktion, använd ledtrådar som WITH (UPDLOCK, ROWLOCK) på SELECT för att skaffa lås tidigt och etablera konsekvent låsordning, vilket minskar risken för 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})

Implementera återförsökslogik för tillfälliga misslyckanden som deadlocks.

Exempel: Överföringsmedel (atomoperation)

Detta exempel demonstrerar atomär överföringslogik med låstips för att förhindra förlorade uppdateringar i samtidiga scenarier:

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

Låsledtrådarna WITH (UPDLOCK, ROWLOCK) på SELECT säkerställer att låset skaffas tidigt. Detta förhindrar att en annan transaktion läser samma saldo samtidigt och skapar ett förlorat uppdateringsscenario där båda transaktionerna läser det gamla saldot, utför separata uppdateringar och endast den senaste uppdateringen kvarstår.

Omfång för temporära tabeller

Tillfälliga tabeller (#tablename) är begränsade till sessionen, men deras skapande är en del av den aktuella transaktionen. Om du skapar en tillfällig tabell och transaktionen rullas tillbaka tas tillfälliga tabellen bort:

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

För att göra en tillfällig tabell oberoende av din datatransaktion, gör en commit när du har skapat den:

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

DDL-satser som kräver autocommit

Vissa DDL-satser, såsom CREATE DATABASE, ALTER DATABASE, och DROP DATABASE, kan inte köras i en transaktion. Ställ in autocommit=True innan du kör dem:

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

Om du kör CREATE DATABASE med autocommit=False, får du ett felmeddelande: CREATE DATABASE statement not allowed within multi-statement transaction.

Feedback

Var den här sidan till hjälp?

Last updated on 2026-07-22