Database transactions ensure that multiple operations are executed safely and reliably. A transaction is a sequence of operations performed as a single unit of work, following the ACID (Atomicity, Consistency, Isolation, Durability) properties. In Python, database transactions are managed using libraries like SQLite, MySQL, PostgreSQL, and SQLAlchemy.
In this guide, we will explore:
- The importance of transactions
- How to use transactions in different databases
- Best practices for managing transactions efficiently
1. What is a Database Transaction?
A database transaction consists of one or more SQL operations that must either complete fully or fail together. Transactions prevent partial updates that could leave the database in an inconsistent state.
ACID Properties
- Atomicity: Ensures that all operations in a transaction succeed or none do.
- Consistency: Keeps the database in a valid state before and after the transaction.
- Isolation: Transactions are executed independently to avoid conflicts.
- Durability: Ensures data is permanently saved even after a system crash.
2. Using Transactions in Python
2.1 Transactions in SQLite
SQLite automatically wraps each statement in a transaction, but you can manage them manually.
Example: Using Transactions in SQLite
import sqlite3
# Connect to database
conn = sqlite3.connect("example.db")
cursor = conn.cursor()
try:
# Start transaction
conn.execute("BEGIN TRANSACTION;")
# Execute multiple queries
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30);")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25);")
# Commit transaction
conn.commit()
print("Transaction committed successfully!")
except Exception as e:
conn.rollback() # Rollback if an error occurs
print(f"Transaction failed: {e}")
finally:
conn.close()
Best Practice: Always use commit() to finalize transactions and rollback() to revert if an error occurs.
2.2 Transactions in MySQL
MySQL supports transactions for InnoDB tables.
Example: Using Transactions in MySQL
import mysql.connector
# Connect to MySQL
conn = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydb"
)
cursor = conn.cursor()
try:
conn.start_transaction() # Start transaction
# Perform multiple operations
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;")
conn.commit() # Commit the transaction
print("Transaction successful!")
except mysql.connector.Error as e:
conn.rollback() # Rollback on error
print(f"Transaction failed: {e}")
finally:
cursor.close()
conn.close()
Best Practice: Use start_transaction() instead of BEGIN TRANSACTION; in MySQL for better control.
2.3 Transactions in PostgreSQL
PostgreSQL provides robust transaction management with commit() and rollback().
Example: Using Transactions in PostgreSQL
import psycopg2
# Connect to PostgreSQL
conn = psycopg2.connect("dbname=mydb user=postgres password=mypassword")
cursor = conn.cursor()
try:
cursor.execute("BEGIN;") # Start transaction
# Execute operations
cursor.execute("INSERT INTO orders (customer_id, total) VALUES (1, 200);")
cursor.execute("UPDATE inventory SET stock = stock - 1 WHERE product_id = 10;")
conn.commit() # Commit transaction
print("Transaction successful!")
except Exception as e:
conn.rollback() # Rollback on failure
print(f"Transaction failed: {e}")
finally:
cursor.close()
conn.close()
Tip: PostgreSQL also supports SAVEPOINTS, which allow partial rollbacks within a transaction.
2.4 Transactions with SQLAlchemy
SQLAlchemy simplifies transaction management across different databases.
Example: Using Transactions in SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# Database connection
engine = create_engine("sqlite:///example.db")
Session = sessionmaker(bind=engine)
session = Session()
try:
# Perform operations
user1 = {"name": "Alice", "age": 30}
user2 = {"name": "Bob", "age": 25}
session.execute("INSERT INTO users (name, age) VALUES (:name, :age);", user1)
session.execute("INSERT INTO users (name, age) VALUES (:name, :age);", user2)
session.commit() # Commit transaction
print("Transaction committed!")
except Exception as e:
session.rollback() # Rollback on error
print(f"Transaction failed: {e}")
finally:
session.close()
Best Practice: Use session.commit() for transaction completion and session.rollback() on failure.
3. Advanced Transaction Techniques
3.1 Using Savepoints
Savepoints allow rolling back parts of a transaction instead of the entire process.
Example: Savepoints in PostgreSQL
cursor.execute("SAVEPOINT sp1;")
cursor.execute("INSERT INTO payments (user_id, amount) VALUES (1, 500);")
# Rollback to savepoint if needed
cursor.execute("ROLLBACK TO SAVEPOINT sp1;")
conn.commit()
When to Use Savepoints: In complex transactions where partial rollback is necessary.
3.2 Using Autocommit Mode
By default, most databases require commit() to save changes, but autocommit mode allows immediate execution.
Example: Enabling Autocommit in MySQL
conn.autocommit = True
cursor.execute("UPDATE users SET age = 40 WHERE name = 'Alice';")
Best Practice: Use autocommit only for non-critical updates to avoid accidental data corruption.
3.3 Handling Deadlocks
Deadlocks occur when two transactions wait for each other, causing a cycle.
Solution: Use NOWAIT or LOCK TIMEOUT
cursor.execute("SELECT * FROM orders FOR UPDATE NOWAIT;")
Avoid deadlocks by:
- Locking rows in a consistent order.
- Keeping transactions short.