Transactions and error handling are essential concepts in database systems to ensure data integrity and reliability.

They help ensure that:

Data remains consistent
Operations are completed successfully
Errors do not corrupt the database

These concepts are critical in banking systems, e-commerce platforms, and enterprise applications.

What is a Transaction?

A Transaction is a group of one or more database operations executed as a single unit of work.

A transaction must follow ACID properties:

Atomicity
Consistency
Isolation
Durability

If any part of a transaction fails, the entire transaction is rolled back.

ACID Properties

Atomicity
All operations succeed or none do.

Consistency
Database remains valid before and after transaction.

Isolation
Transactions do not interfere with each other.

Durability
Committed data is permanently saved.

Example of Transaction Scenario

Bank Transfer:

Step 1: Deduct money from Account A
Step 2: Add money to Account B

If step 2 fails, step 1 must be reversed.

This is handled using transactions.

Using Transactions in Python (PostgreSQL Example)

import psycopg2try:
    connection = psycopg2.connect(
        host="localhost",
        database="your_database",
        user="your_username",
        password="your_password"
    )    cursor = connection.cursor()    # Start transaction
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1;")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2;")    connection.commit()
    print("Transaction successful!")except Exception as e:
    connection.rollback()
    print("Transaction failed:", e)finally:
    cursor.close()
    connection.close()

If any error occurs, rollback() ensures no partial updates happen.

Commit vs Rollback

commit()
Saves all changes permanently.

rollback()
Reverts all changes made during the transaction.

Never forget to commit when operations succeed.

Error Handling in Python

Use try-except blocks to handle errors safely.

Example:

try:
    cursor.execute("SELECT * FROM users;")
    data = cursor.fetchall()
    print(data)except Exception as e:
    print("Error occurred:", e)

This prevents application crashes.

Using Context Manager (Recommended)

import psycopg2with psycopg2.connect(
    host="localhost",
    database="your_database",
    user="your_username",
    password="your_password"
) as connection:    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM users;")
        print(cursor.fetchall())

Benefits:

Automatic commit on success
Automatic rollback on failure
Cleaner code

Common Transaction Use Cases

Bank transfers
Order placement systems
Inventory updates
Payment processing
Multi-step database operations

Best Practices

Always use transactions for critical operations
Use rollback in error handling
Keep transactions short
Avoid long-running transactions
Handle exceptions properly
Log errors for debugging

Common Mistakes

Not using transactions in financial operations
Forgetting rollback
Ignoring exceptions
Leaving connections open
Long transactions causing database locks

Why Transactions Matter

Without transactions:

Data can become inconsistent
Partial updates may corrupt records
Financial systems can break
User trust can be lost

Transactions protect data integrity.

Key Takeaway

Transactions ensure that multiple database operations either complete fully or not at all.

Error handling with try-except and rollback protects your system from data corruption and ensures reliable, secure database applications.