Handling Transactions

What are Transactions?

In database management, a transaction is a logical unit of work that ensures database consistency. It represents a series of operations that must either succeed or fail together, maintaining data integrity.

ACID Properties

Transactions adhere to the following ACID properties:

• Atomicity: All operations within a transaction must complete successfully, or the entire transaction is rolled back.
• Consistency: The transaction must maintain database consistency, ensuring that data meets all business rules.
• Isolation: Each transaction appears to execute independently, isolated from other concurrent transactions.
• Durability: Once a transaction commits, its changes become permanent and cannot be lost.

Steps in Handling Transactions

• Begin Transaction: Start a new transaction using BEGIN or START TRANSACTION.
• Execute Operations: Perform the necessary database operations within the transaction.
• Commit or Rollback: When all operations complete successfully, use COMMIT to make the changes permanent. If any error occurs, use ROLLBACK to undo the entire transaction.

Python Example:

Ensuring Data Integrity with MySQL Transactions

import mysql.connector

# Open a database connection
connection = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

# Start a transaction
cursor = connection.cursor()
cursor.execute("BEGIN")

# Execute operations within the transaction
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 1")
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 2")

try:
    # Commit the transaction
    connection.commit()
except Exception as e:
    # Rollback the transaction if an error occurs
    connection.rollback()
finally:
    # Close the cursor and connection
    cursor.close()
    connection.close()