Database Transaction — Explained with Examples
A database transaction is a unit of work that groups multiple operations into a single all-or-nothing execution with ACID guarantees.
Transaction is a sequence of database operations executed as a single logical unit. Transactions provide the ACID guarantees — Atomicity, Consistency, Isolation, Durability — ensuring data integrity even during failures or concurrent access.
Why Transactions Matter
Consider a money transfer: debit account A, credit account B. If the system crashes after debiting A but before crediting B, money disappears. A transaction ensures both happen or neither happens. Without transactions, data corruption is inevitable in any multi-step operation.
Real-World Analogy
A wedding ceremony: the officiant says “I now pronounce you married” — all legal changes happen at once (name change, tax status, inheritance rights). There’s no partial state. If the groom faints mid-ceremony, nothing changes — you’re not “half-married.” That’s atomicity.
Example: Transaction in Python with SQLite
import sqlite3
conn = sqlite3.connect('bank.db')
cursor = conn.cursor()
# Create tables
cursor.execute("CREATE TABLE IF NOT EXISTS accounts (id INT, balance INT)")
cursor.execute("INSERT INTO accounts VALUES (1, 1000)")
cursor.execute("INSERT INTO accounts VALUES (2, 500)")
conn.commit()
def transfer(from_id, to_id, amount):
try:
conn.execute("BEGIN TRANSACTION")
# Deduct from sender
cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?",
(amount, from_id))
# Check balance didn't go negative
cursor.execute("SELECT balance FROM accounts WHERE id = ?", (from_id,))
if cursor.fetchone()[0] < 0:
raise ValueError("Insufficient funds")
# Credit to receiver
cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?",
(amount, to_id))
conn.commit() # All changes persist atomically
print(f"Transferred ${amount} from {from_id} to {to_id}")
except Exception as e:
conn.rollback() # All changes reverted
print(f"Transfer failed: {e}")
transfer(1, 2, 200) # Success
transfer(1, 2, 9999) # Rollback — insufficient fundsRelated Terms
Built by the developers of DodaTech
Doda Browser, DodaZIP & Durga Antivirus Pro