Manajemen transaksi dengan mssql-python

Driver mssql-python mendukung kontrol penuh atas transaksi, termasuk commit, rollback, konfigurasi autocommit, dan level isolasi transaksi.

Dasar-dasar transaksi

Transaksi mengelompokkan urutan operasi database ke dalam satu unit kerja. Transaksi mengikuti properti ACID:

Atomisitas: Semua operasi berhasil atau semua gagal.
Konsistensi: Database tetap dalam status valid.
Isolasi: Transaksi bersamaan tidak saling mengganggu.
Daya tahan: Perubahan yang dilakukan bertahan dari kegagalan sistem.

Mode komit otomatis

Pengaturan autocommit mengontrol apakah perubahan dikomit secara otomatis.

Komit otomatis dinonaktifkan (bawaan)

Secara bawaan, autocommit=False. Anda harus secara eksplisit menerapkan perubahan.

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

Jika Anda tidak melakukan commit, driver akan membuang perubahan saat koneksi tertutup.

Autocommit diaktifkan

Saat Anda mengatur autocommit=True, driver segera melakukan setiap pernyataan:

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

Saat autocommit diaktifkan, Anda tidak dapat me-rollback beberapa statement sekaligus. Gunakan autocommit hanya jika sesuai dengan kasus penggunaan Anda.

Penerapan dan pembatalan

Melakukan komitmen

Panggil commit() untuk membuat perubahan yang tertunda menjadi permanen:

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

Rollback

Untuk membuang perubahan yang tertunda, panggil 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

Penerapan dan pengembalian tingkat kursor

Untuk kenyamanan, Anda dapat memanggil commit() dan rollback() menggunakan kursor:

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 dan rollback pada level kursor memengaruhi semua kursor pada koneksi yang sama, bukan hanya kursor yang Anda gunakan untuk memanggil commit dan rollback tersebut.

Manajer konteks

Manajer konteks koneksi mengonfirmasi transaksi saat keluar tanpa kesalahan dan membatalkannya jika terjadi pengecualian. Koneksi selalu ditutup saat keluar. Saat Anda mengatur autocommit=True, panggilan commit dan rollback tidak berpengaruh.

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

Jika terjadi pengecualian, transaksi akan dibatalkan:

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

Tingkat isolasi transaksi

Tingkat isolasi mengontrol bagaimana transaksi berinteraksi dengan transaksi bersamaan. Atur tingkat isolasi menggunakan 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
)

Tingkat isolasi yang tersedia

Konstanta	Deskripsi
`SQL_TXN_READ_UNCOMMITTED`	Dapat membaca perubahan yang belum dikomitkan dari transaksi lain (dirty read diizinkan)
`SQL_TXN_READ_COMMITTED`	Hanya membaca data yang sudah di-commit (bawaan di SQL Server)
`SQL_TXN_REPEATABLE_READ`	Menjamin pembacaan yang konsisten dalam transaksi
`SQL_TXN_SERIALIZABLE`	Isolasi tertinggi; transaksi tampaknya berjalan secara berurutan

Memilih tingkat isolasi

Skenario penggunaan	Tingkat yang disarankan
Beban kerja OLTP umum	`READ_COMMITTED` (standar)
Laporan yang memerlukan cuplikan yang konsisten	`REPEATABLE_READ` atau snapshot
Perhitungan keuangan yang membutuhkan akurasi	`SERIALIZABLE`
Beban kerja yang didominasi operasi baca dan dapat mentoleransi data usang	`READ_UNCOMMITTED`

Isolasi rekam jepret

Untuk isolasi rekam jepret, gunakan Transact-SQL (T-SQL). Isolasi snapshot menggunakan pembuatan versi baris di tempdb, yang dapat meningkatkan kebutuhan penyimpanan pada beban kerja tulis yang tinggi.

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

Transaksi bersarang dan titik penyimpanan

SQL Server mendukung savepoint untuk melakukan rollback parsial dalam suatu transaksi.

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

Menangani kebuntuan

Kebuntuan terjadi ketika dua transaksi saling menunggu kunci satu sama lain. SQL Server secara otomatis mendeteksi kebuntuan dan mengakhiri satu transaksi.

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

Praktik terbaik

Jaga agar transaksi tetap singkat untuk meminimalkan durasi penguncian dan potensi kebuntuan.
Gunakan autocommit=False untuk transaksi dengan beberapa pernyataan yang harus atomik.

Selalu tangani pengecualian dengan 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()

Gunakan pengelola konteks untuk mengelola transaksi secara otomatis. Pengelola konteks melakukan commit saat keluar tanpa error dan melakukan rollback saat terjadi eksepsi.

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

Pilih tingkat isolasi yang sesuai berdasarkan persyaratan konsistensi vs. kebutuhan performa Anda.

Gunakan petunjuk kunci untuk pola baca-modifikasi-tulis untuk mencegah pembaruan yang hilang. Saat Anda membaca nilai yang akan diperbarui dalam transaksi yang sama, gunakan petunjuk seperti WITH (UPDLOCK, ROWLOCK) pada SELECT untuk mendapatkan kunci lebih awal dan menetapkan pemesanan kunci yang konsisten, yang mengurangi risiko kebuntuan.

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

Terapkan logika coba lagi untuk kegagalan sementara seperti kebuntuan.

Contoh: Transfer dana (operasi atom)

Contoh ini menunjukkan logika transfer atom dengan petunjuk kunci untuk mencegah pembaruan yang hilang dalam skenario bersamaan:

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

Petunjuk WITH (UPDLOCK, ROWLOCK) kunci pada SELECT memastikan bahwa kunci diperoleh lebih awal. Ini mencegah transaksi lain membaca saldo yang sama secara bersamaan dan membuat skenario pembaruan yang hilang di mana kedua transaksi membaca saldo lama, melakukan pembaruan terpisah, dan hanya pembaruan terakhir yang bertahan.

Cakupan tabel sementara

Tabel sementara (#tablename) terbatas pada sesi, tetapi pembuatannya merupakan bagian dari transaksi saat ini. Jika Anda membuat tabel sementara dan transaksi digulirkan kembali, tabel sementara akan dihapus:

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

Untuk menjaga tabel sementara tetap independen dari transaksi data Anda, terapkan setelah membuatnya:

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

Pernyataan DDL yang memerlukan penerapan otomatis

Beberapa pernyataan DDL, seperti CREATE DATABASE, ALTER DATABASE, dan DROP DATABASE, tidak dapat berjalan di dalam transaksi. Atur autocommit=True sebelum menjalankannya:

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

Jika Anda menjalankan CREATE DATABASE dengan autocommit=False, Anda akan mendapatkan error: CREATE DATABASE statement not allowed within multi-statement transaction.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-07-22