In modern database development, tools like DataGrip make transaction handling easier by providing graphical interfaces to commit, rollback, or set savepoints. DataGrip 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. COMMIT ROLLBACK SAVEPOINT Oracle MySQL PostgreSQL Key Transaction Concepts 1. COMMIT 1. COMMIT • Definition: Finalizes a transaction, making all changes permanent in the database. Definition • Use Case: Use COMMIT when all operations in a transaction are successful, ensuring the database reflects the changes. Use Case 2. ROLLBACK 2. ROLLBACK • Definition: Reverts all changes made during a transaction, restoring the database to its previous state. Definition • Use Case: Use ROLLBACK to handle errors or undo a failed transaction. Use Case 3. SAVEPOINT 3. SAVEPOINT • Definition: Sets a named checkpoint within a transaction, allowing partial rollbacks to that point without undoing the entire transaction. Definition • Use Case: Use SAVEPOINT to manage complex transactions with multiple steps, rolling back selectively when needed. Use Case 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 Common Steps for Transactions in Python Start a Transaction: Automatically starts with the connection unless autocommit is enabled.
Execute SQL Statements: Perform the necessary database operations.
Commit or Rollback: Use commit() to finalize changes or rollback() to revert them.
Use Savepoints: For finer control, define and rollback to savepoints if supported. Start a Transaction: Automatically starts with the connection unless autocommit is enabled. Start a Transaction Execute SQL Statements: Perform the necessary database operations. Execute SQL Statements Commit or Rollback: Use commit() to finalize changes or rollback() to revert them. Commit or Rollback Use Savepoints: For finer control, define and rollback to savepoints if supported. Use Savepoints Example: Python Transactions With Oracle Databases Setup Connection Setup Connection import cx_Oracle

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

# Connect to Oracle Database
connection = cx_Oracle.connect("user/password@localhost/XEPDB1")
cursor = connection.cursor() Using COMMIT and ROLLBACK 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}") 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 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}") 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!👋 LinkedIn

Oracle

SQL Recursive Queries - How to Understand Common Table Expressions (CTEs)

Do You Really Need a “Second Brain”?

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

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

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

A Quick Guide to Listing All Measures in Power BI via DAX Studio

4 Data Analytics Certifications That Boost Your Career

Common MS Excel Questions to Help you Excel in a Data Analyst Job Interview

Data Scientist Careers at Amazon: What You'll Earn, Learn, and Work On

How Much Can You Make as a Data Scientist?

A Quick Guide to Listing All Measures in Power BI via DAX Studio

4 Data Analytics Certifications That Boost Your Career

Common MS Excel Questions to Help you Excel in a Data Analyst Job Interview

Data Scientist Careers at Amazon: What You'll Earn, Learn, and Work On

How Much Can You Make as a Data Scientist?

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps