Performing Transactions

In database management, a transaction is a sequence of operations performed as a single unit of work. A transaction is committed only if all operations are successful; otherwise, it is rolled back. This ensures the database remains consistent and avoids partial updates.

ACID Properties of Transactions

• Atomicity: Ensures that all operations within a transaction are completed; otherwise, none are applied.
• Consistency: Ensures that a transaction brings the database from one valid state to another.
• Isolation: Ensures that transactions are executed independently without interference.
• Durability: Ensures that once a transaction is committed, it will remain so, even in the case of a system failure.

Example

Let's consider an example where we are transferring money between two accounts. The transaction will involve updating two rows in the database. If any step fails, we will roll back the transaction to maintain consistency.

import mysql.connector

# Establishing a connection to the MySQL database
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpassword',
    database='bank_db'
)
cursor = connection.cursor()

# Creating a sample table for the transaction
cursor.execute("""
CREATE TABLE IF NOT EXISTS accounts (
    account_id INT PRIMARY KEY,
    holder_name VARCHAR(100),
    balance DECIMAL(10, 2)
)
""")
connection.commit()

# Inserting initial data
cursor.execute("INSERT INTO accounts (account_id, holder_name, balance) VALUES (1, 'Alice', 1000.00)")
cursor.execute("INSERT INTO accounts (account_id, holder_name, balance) VALUES (2, 'Bob', 500.00)")
connection.commit()

try:
    # Start the transaction
    print("Starting Transaction...")

    # Deducting amount from Alice's account
    cursor.execute("UPDATE accounts SET balance = balance - 200 WHERE account_id = 1")

    # Adding amount to Bob's account
    cursor.execute("UPDATE accounts SET balance = balance + 200 WHERE account_id = 2")

    # Committing the transaction
    connection.commit()
    print("Transaction Committed Successfully")

except mysql.connector.Error as error:
    # Rolling back in case of error
    connection.rollback()
    print("Transaction Failed. Rolled Back.")

finally:
    # Fetching the updated data for verification
    cursor.execute("SELECT * FROM accounts")
    accounts = cursor.fetchall()

    for account in accounts:
        print(account)

    cursor.close()
    connection.close()
            

Data in the 'accounts' Table Before Transaction

Data in the 'accounts' Table After Transaction

Explanation

• In this example, we first deducted 200 from Alice's account and added it to Bob's account.
• If any of the UPDATE operations failed, the except block would roll back the changes, ensuring no partial updates occur.
• The commit() method is used to save all the changes, while the rollback() method reverts them.

Using transactions is essential for maintaining data integrity, especially in scenarios where multiple related changes need to occur together.