SQL Transactions - How to Implement COMMIT, ROLLBACK, and Savepoints With Python

In modern database development, tools like DataGrip make transaction handling easier by providing graphical interfaces to commit, rollback, or set savepoints.

In this article, we’ll define COMMIT, ROLLBACK, and SAVEPOINT in SQL and demonstrate how to implement these transaction controls when working with Oracle, MySQL, or PostgreSQL in Python.

Key Transaction Concepts

1. COMMIT

• Definition: Finalizes a transaction, making all changes permanent in the database.

• Use Case: Use COMMIT when all operations in a transaction are successful, ensuring the database reflects the changes.

2. ROLLBACK

• Definition: Reverts all changes made during a transaction, restoring the database to its previous state.

• Use Case: Use ROLLBACK to handle errors or undo a failed transaction.

3. SAVEPOINT

• Definition: Sets a named checkpoint within a transaction, allowing partial rollbacks to that point without undoing the entire transaction.

• Use Case: Use SAVEPOINT to manage complex transactions with multiple steps, rolling back selectively when needed.

Python and Database Transactions

When working with databases programmatically, Python’s database libraries (e.g., cx_Oracle, mysql-connector-python, psycopg2) provide methods to control transactions explicitly.

Common Steps for Transactions in Python

1. Start a Transaction: Automatically starts with the connection unless autocommit is enabled.
2. Execute SQL Statements: Perform the necessary database operations.
3. Commit or Rollback: Use commit() to finalize changes or rollback() to revert them.
4. Use Savepoints: For finer control, define and rollback to savepoints if supported.

Example: Python Transactions With Oracle Databases

Setup Connection

import cx_Oracle

# Connect to Oracle Database
connection = cx_Oracle.connect("user/password@localhost/XEPDB1")
cursor = connection.cursor()

Using COMMIT and ROLLBACK

try:
    # Start Transaction
    cursor.execute("UPDATE Accounts SET Balance = Balance - 100 WHERE Name = 'Alice'")
    cursor.execute("UPDATE Accounts SET Balance = Balance + 100 WHERE Name = 'Bob'")
    
    # Commit the transaction
    connection.commit()
    print("Transaction committed successfully!")
except Exception as e:
    # Rollback in case of error
    connection.rollback()
    print(f"Transaction failed. Rolled back changes. Error: {e}")

Using SAVEPOINT

try:
    # Start Transaction
    cursor.execute("UPDATE Accounts SET Balance = Balance - 200 WHERE Name = 'Alice'")
    connection.commit()
    
    # Savepoint
    cursor.execute("SAVEPOINT Savepoint_After_Alice")

    # Add 200 to Bob (intentional error to demonstrate rollback)
    cursor.execute("UPDATE Accounts SET Balance = Balance + 200 WHERE Name = 'Unknown'")

    # Commit if successful
    connection.commit()
except Exception as e:
    # Rollback to savepoint
    cursor.execute("ROLLBACK TO Savepoint_After_Alice")
    connection.commit()
    print(f"Rolled back to savepoint. Error: {e}")

Summary

Mastering these concepts allows you to manage database transactions efficiently in real-world applications!

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement. If you find this information helpful, I invite you to follow me or connect with me on LinkedIn. Happy exploring!👋