Tranzakciókezelés mssql-python segítségével

Az mssql-python illesztőprogram teljes tranzakcióvezérlést támogat, beleértve a commit, rollback, automatikus commit konfigurációt és tranzakciós izolációs szinteket.

A tranzakció alapjai

Egy tranzakció adatbázis-műveletek sorozatát egyetlen munkaegységbe csoportosítja. A tranzakciók a ACID tulajdonságokat követik:

Atomiság: Minden művelet sikeres vagy kudarcot vall.
Konzisztencia: Az adatbázis érvényes állapotban marad.
Izoláció: Az egyidejű tranzakciók nem zavarják egymást.
Tartósság: Elkötelezett változtatások túlélik a rendszerhibákat.

Automatikus véglegesítési mód

A autocommit beállítás szabályozza, hogy a változtatások automatikusan elköteleződnek-e.

Autocommit letiltva (alapértelmezett)

Alapértelmezés szerint. autocommit=False A módosításokat külön véglegesítened kell.

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

Ha nem véglegesíted a módosításokat, az illesztőprogram elveti őket a kapcsolat lezárásakor.

Automatikus véglegesítés engedélyezve

Amikor beállítja a(z) autocommit=True értéket, az illesztőprogram minden utasítást azonnal véglegesít:

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

Ha az automatikus commit engedélyezett, nem lehet több utasítást csoportként visszavonni. Az automatikus véglegesítést csak akkor használd, ha az megfelel a felhasználási esetednek.

Véglegesítés és visszaállítás

Elköteleződés

Hívja meg a(z) commit() elemet a függőben lévő módosítások véglegesítéséhez:

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

Visszagörgetés

A függőben lévő módosítások elvetéséhez hívja meg a(z) rollback() függvényt:

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

Kurzorszintű véglegesítés és visszagörgetés

A kényelem kedvéért meghívhatod a commit() és rollback() elemeket a kurzorokon:

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

Megjegyzés:

A kurzorszintű véglegesítés és visszagörgetés ugyanazon a kapcsolaton lévő összes kurzort érinti, nem csak azt a kurzort, amelyen meghívja.

Környezetkezelők

A kapcsolat kontextuskezelője hibamentes befejezéskor véglegesíti a tranzakciót, kivétel fellépése esetén pedig visszagörgeti. A kapcsolat mindig kijáratnál bezárul. Ha beállítja a(z) autocommit=True értéket, a commit és a rollback hívásoknak nincs hatásuk.

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

Ha kivétel történik, a tranzakciót visszafordítják:

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

Tranzakciós izolációs szintek

Az izolációs szintek szabályozzák, hogyan lépnek kölcsönhatásba a tranzakciók az egyidejű tranzakciókkal. Állítsd be az izolációs szintet a következőként: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
)

Elérhető izolációs szintek

Konstans	Leírás
`SQL_TXN_READ_UNCOMMITTED`	El tudja olvasni a kötelezetlen változtatásokat más tranzakciókból (piszkos olvasás engedélyezett)
`SQL_TXN_READ_COMMITTED`	Csak a kötelezett adatokat olvassa (alapértelmezett az SQL Server-ben)
`SQL_TXN_REPEATABLE_READ`	Biztosítja a tranzakción belüli konzisztens olvasást
`SQL_TXN_SERIALIZABLE`	Legmagasabb elszigeteltség; A tranzakciók úgy tűnik, hogy egymás után futnak

Válassz izolációs szintet

Felhasználási eset	Ajánlott szint
Általános OLTP munkaterhelések	`READ_COMMITTED` (alapértelmezett)
Jelentések, amelyekhez következetes pillanatképeket kell	`REPEATABLE_READ` vagy pillanatkép
A pontosságot igénylő pénzügyi számítások	`SERIALIZABLE`
Olvasásintenzív munkaterhelések, amelyek tolerálják az elavult adatokat	`READ_UNCOMMITTED`

Pillanatképek elkülönítése

Snapshot izoláláshoz használd a Transact-SQL (T-SQL) funkciót. A pillanatkép-izoláció sorverzió-kezelést használ tempdb, ami nagy írási terhelés esetén növelheti a tárolási igényeket.

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

Egymásba ágyazott tranzakciók és mentési pontok

Az SQL Server támogatja a mentési pontokat a tranzakción belüli részleges visszafordításhoz.

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

Holtpontok kezelése

A holtpont akkor fordul elő, amikor két tranzakció várja egymás zárját. Az SQL Server automatikusan észleli a holtpontokat, és egy tranzakciót megszüntet.

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

Bevált gyakorlatok

Tartsd a tranzakciókat röviden , hogy minimalizáld a zárolás időtartamát és a holtpont lehetőségét.
Használja az autocommit=False beállítást olyan több utasításból álló tranzakciókhoz, amelyeknek atomikusnak kell lenniük.

Mindig kezeld a kivételeket visszagörgetéssel:

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

Használj kontextuskezelőket a tranzakciók automatikusára történő kezelésére. A kontextuskezelő tiszta kilépéskor véglegesíti a tranzakciót, kivétel esetén pedig visszagörgeti.

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álassz megfelelő izolációs szinteket a következetesség követelményei és a teljesítményigények alapján.

Használj zárolási tippeket olvasás-módosítás-írás mintákhoz, hogy elkerüld a frissítések elvesztését. Ha olyan értéket olvasol be, amelyet ugyanabban a tranzakcióban frissíteni fogsz, használj a SELECT utasításban olyan hintákat, mint a WITH (UPDLOCK, ROWLOCK), hogy már korán megszerezd a zárakat, és egységes zársorrendet alakíts ki, ami csökkenti a deadlock kockázatát.

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

Alkalmazz újrapróbálási logikát átmeneti hibáknál, például holtpontoknál.

Példa: Pénz átutalása (atomi művelet)

Ez a példa atomátviteli logikát mutat be zárási tippekkel, hogy elkerüljék a frissítések elvesztését párhuzamos helyzetekben:

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

A SELECT zár jelzései WITH (UPDLOCK, ROWLOCK) biztosítják, hogy a zár korán megszerezhető. Ez megakadályozza, hogy egy másik tranzakció egyszerre olvassa ugyanazt az egyenleget, és elveszett frissítési forgatókönyvet hozzon létre, ahol mindkét tranzakció olvassa a régi egyenleget, külön frissítéseket hajt végre, és csak az utolsó frissítés marad fenn.

Ideiglenes táblázat meghatározása

Az ideiglenes táblák (#tablename) a munkamenet hatókörébe tartoznak, de létrehozásuk az aktuális tranzakció része. Ha létrehozol egy ideiglenes táblát, és a tranzakciót visszagörgetik, akkor az ideiglenes tábla törlődik:

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

Ahhoz, hogy az ideiglenes tábla független maradjon az adattranzakciódtól, a létrehozása után hajts végre commitot:

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-utasítások, amelyek automatikus véglegesítést igényelnek

Néhány DDL-utasítás, például CREATE DATABASE, ALTER DATABASE és DROP DATABASE, nem futtatható tranzakción belül. Állítsd autocommit=True be a végrehajtás előtt:

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

Ha a(z) CREATE DATABASE elemet a(z) autocommit=False használatával futtatod, hibaüzenetet kapsz: CREATE DATABASE statement not allowed within multi-statement transaction.

Visszajelzés

Hasznosnak találta ezt az oldalt?

Last updated on 2026-07-22