+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Part 477 of 541

๐Ÿ“˜ Transactions: ACID Properties

Master transactions: acid properties in Python with practical examples, best practices, and real-world applications ๐Ÿš€

๐Ÿš€Intermediate
25 min read

Prerequisites

  • Basic understanding of programming concepts ๐Ÿ“
  • Python installation (3.8+) ๐Ÿ
  • VS Code or preferred IDE ๐Ÿ’ป

What you'll learn

  • Understand the concept fundamentals ๐ŸŽฏ
  • Apply the concept in real projects ๐Ÿ—๏ธ
  • Debug common issues ๐Ÿ›
  • Write clean, Pythonic code โœจ

๐ŸŽฏ Introduction

Welcome to this exciting tutorial on database transactions and ACID properties! ๐ŸŽ‰ In this guide, weโ€™ll explore how databases ensure your data stays consistent and reliable, even when things go wrong.

Youโ€™ll discover how transactions can transform your Python database applications from fragile to rock-solid. Whether youโ€™re building banking systems ๐Ÿฆ, e-commerce platforms ๐Ÿ›’, or any application where data integrity matters, understanding ACID properties is essential for writing robust, production-ready code.

By the end of this tutorial, youโ€™ll feel confident implementing transactions in your own projects! Letโ€™s dive in! ๐ŸŠโ€โ™‚๏ธ

๐Ÿ“š Understanding Transactions and ACID

๐Ÿค” What is a Transaction?

A transaction is like a protective bubble around your database operations ๐Ÿซง. Think of it as an โ€œall-or-nothingโ€ package deal - either everything inside succeeds together, or nothing happens at all.

In Python database terms, a transaction groups multiple database operations into a single unit of work. This means you can:

  • โœจ Execute multiple operations atomically
  • ๐Ÿš€ Maintain data consistency
  • ๐Ÿ›ก๏ธ Protect against partial updates

๐Ÿ’ก What Does ACID Stand For?

ACID is the superhero team of database properties! Each letter represents a crucial guarantee:

  1. Atomicity โš›๏ธ: All or nothing - no half-baked operations
  2. Consistency โœ…: Data follows all the rules
  3. Isolation ๐Ÿ๏ธ: Transactions donโ€™t step on each otherโ€™s toes
  4. Durability ๐Ÿ’ช: Once committed, data survives even system crashes

Real-world example: Imagine transferring money between bank accounts ๐Ÿ’ธ. With ACID properties, youโ€™ll never lose money in transit or accidentally create money out of thin air!

๐Ÿ”ง Basic Syntax and Usage

๐Ÿ“ Simple Transaction Example

Letโ€™s start with a friendly example using SQLite:

import sqlite3

# ๐Ÿ‘‹ Hello, Transactions!
connection = sqlite3.connect('bank.db')
cursor = connection.cursor()

# ๐ŸŽจ Create our accounts table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS accounts (
        id INTEGER PRIMARY KEY,
        name TEXT,
        balance DECIMAL(10, 2)
    )
''')

# ๐Ÿ’ฐ Start a transaction (implicit in SQLite)
try:
    # ๐Ÿฆ Transfer money between accounts
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
    
    # โœ… Commit if everything worked
    connection.commit()
    print("๐Ÿ’ธ Transfer successful!")
    
except Exception as e:
    # โŒ Rollback if anything failed
    connection.rollback()
    print(f"๐Ÿ˜ฑ Transfer failed: {e}")

๐Ÿ’ก Explanation: Notice how we wrap our operations in a try-except block! If any operation fails, we rollback the entire transaction to maintain consistency.

๐ŸŽฏ Common Transaction Patterns

Here are patterns youโ€™ll use daily:

# ๐Ÿ—๏ธ Pattern 1: Context Manager (Recommended!)
from contextlib import closing

with closing(sqlite3.connect('store.db')) as conn:
    with conn:  # ๐ŸŽฏ This creates a transaction!
        cursor = conn.cursor()
        cursor.execute("INSERT INTO orders (customer, total) VALUES (?, ?)", 
                      ("Alice", 99.99))
        cursor.execute("UPDATE inventory SET quantity = quantity - 1 WHERE id = ?", 
                      (42,))
        # ๐ŸŽ‰ Automatically commits on success, rollbacks on exception!

# ๐ŸŽจ Pattern 2: Explicit Transaction Control
import psycopg2

conn = psycopg2.connect("dbname=shop user=python")
cursor = conn.cursor()

# ๐Ÿ”„ Begin explicit transaction
conn.autocommit = False
try:
    cursor.execute("BEGIN")
    # ... your operations here ...
    cursor.execute("COMMIT")
except:
    cursor.execute("ROLLBACK")
    raise

# ๐Ÿ›ก๏ธ Pattern 3: Savepoints for Partial Rollback
cursor.execute("SAVEPOINT my_savepoint")
try:
    # ๐ŸŽฏ Risky operation
    cursor.execute("DELETE FROM temp_data")
except:
    cursor.execute("ROLLBACK TO SAVEPOINT my_savepoint")

๐Ÿ’ก Practical Examples

๐Ÿ›’ Example 1: E-Commerce Order Processing

Letโ€™s build something real - a complete order processing system:

import sqlite3
from decimal import Decimal
from datetime import datetime

class OrderProcessor:
    def __init__(self, db_path='shop.db'):
        self.conn = sqlite3.connect(db_path)
        self.setup_tables()
    
    def setup_tables(self):
        # ๐Ÿ—๏ธ Create our schema
        with self.conn:
            self.conn.executescript('''
                CREATE TABLE IF NOT EXISTS products (
                    id INTEGER PRIMARY KEY,
                    name TEXT,
                    price DECIMAL(10, 2),
                    stock INTEGER,
                    emoji TEXT
                );
                
                CREATE TABLE IF NOT EXISTS orders (
                    id INTEGER PRIMARY KEY,
                    customer_name TEXT,
                    order_date TIMESTAMP,
                    total DECIMAL(10, 2),
                    status TEXT
                );
                
                CREATE TABLE IF NOT EXISTS order_items (
                    order_id INTEGER,
                    product_id INTEGER,
                    quantity INTEGER,
                    price DECIMAL(10, 2)
                );
            ''')
    
    def process_order(self, customer_name, cart_items):
        # ๐Ÿ›’ Process a complete order with ACID guarantees!
        try:
            with self.conn:
                cursor = self.conn.cursor()
                
                # ๐Ÿ“ Create the order
                cursor.execute("""
                    INSERT INTO orders (customer_name, order_date, total, status)
                    VALUES (?, ?, 0, 'processing')
                """, (customer_name, datetime.now()))
                
                order_id = cursor.lastrowid
                total = Decimal('0.00')
                
                # ๐Ÿ›๏ธ Process each item
                for item in cart_items:
                    product_id = item['product_id']
                    quantity = item['quantity']
                    
                    # ๐Ÿ” Check stock (Consistency!)
                    cursor.execute(
                        "SELECT name, price, stock, emoji FROM products WHERE id = ?",
                        (product_id,)
                    )
                    product = cursor.fetchone()
                    
                    if not product:
                        raise ValueError(f"โŒ Product {product_id} not found!")
                    
                    name, price, stock, emoji = product
                    
                    if stock < quantity:
                        raise ValueError(
                            f"๐Ÿ˜ฑ Not enough {emoji} {name} in stock! "
                            f"(wanted {quantity}, have {stock})"
                        )
                    
                    # ๐Ÿ“ฆ Update inventory (Atomicity!)
                    cursor.execute(
                        "UPDATE products SET stock = stock - ? WHERE id = ?",
                        (quantity, product_id)
                    )
                    
                    # ๐Ÿ’ฐ Add to order
                    cursor.execute("""
                        INSERT INTO order_items (order_id, product_id, quantity, price)
                        VALUES (?, ?, ?, ?)
                    """, (order_id, product_id, quantity, price))
                    
                    total += Decimal(str(price)) * quantity
                    print(f"โœ… Added {quantity}x {emoji} {name} to order")
                
                # ๐Ÿ’ธ Update order total
                cursor.execute(
                    "UPDATE orders SET total = ?, status = 'completed' WHERE id = ?",
                    (str(total), order_id)
                )
                
                print(f"๐ŸŽ‰ Order #{order_id} completed! Total: ${total}")
                return order_id
                
        except Exception as e:
            print(f"๐Ÿ’ฅ Order failed: {e}")
            # Transaction automatically rolled back!
            raise

# ๐ŸŽฎ Let's use it!
processor = OrderProcessor()

# ๐Ÿช Add some products
with processor.conn:
    cursor = processor.conn.cursor()
    cursor.executemany(
        "INSERT OR REPLACE INTO products (id, name, price, stock, emoji) VALUES (?, ?, ?, ?, ?)",
        [
            (1, "Python Book", 29.99, 10, "๐Ÿ“˜"),
            (2, "Coffee Mug", 12.99, 5, "โ˜•"),
            (3, "Mechanical Keyboard", 89.99, 3, "โŒจ๏ธ")
        ]
    )

# ๐Ÿ›’ Place an order
cart = [
    {'product_id': 1, 'quantity': 2},
    {'product_id': 2, 'quantity': 1}
]

processor.process_order("Alice", cart)

๐ŸŽฏ Try it yourself: Add a payment processing step and handle refunds with proper transaction management!

๐ŸŽฎ Example 2: Game Leaderboard with ACID

Letโ€™s make a multiplayer game leaderboard:

import sqlite3
from threading import Thread
import time
import random

class GameLeaderboard:
    def __init__(self):
        self.conn = sqlite3.connect('game.db', check_same_thread=False)
        self.conn.execute("PRAGMA journal_mode=WAL")  # ๐Ÿš€ Better concurrency
        self.setup()
    
    def setup(self):
        with self.conn:
            self.conn.execute('''
                CREATE TABLE IF NOT EXISTS players (
                    id INTEGER PRIMARY KEY,
                    username TEXT UNIQUE,
                    score INTEGER DEFAULT 0,
                    level INTEGER DEFAULT 1,
                    achievements TEXT DEFAULT '[]',
                    last_played TIMESTAMP
                )
            ''')
    
    def award_points(self, username, points, achievement=None):
        # ๐Ÿ† Award points with ACID guarantees
        for attempt in range(3):  # ๐Ÿ”„ Retry logic for isolation conflicts
            try:
                with self.conn:
                    cursor = self.conn.cursor()
                    
                    # ๐Ÿ”’ Lock the player row (Isolation!)
                    cursor.execute(
                        "SELECT score, level FROM players WHERE username = ?",
                        (username,)
                    )
                    result = cursor.fetchone()
                    
                    if not result:
                        # ๐Ÿ†• New player
                        cursor.execute(
                            "INSERT INTO players (username, score) VALUES (?, ?)",
                            (username, points)
                        )
                        print(f"๐ŸŽฎ Welcome {username}! Starting score: {points}")
                    else:
                        old_score, old_level = result
                        new_score = old_score + points
                        
                        # ๐ŸŽŠ Level up check
                        new_level = old_level
                        while new_score >= new_level * 1000:
                            new_level += 1
                            print(f"๐ŸŽ‰ {username} leveled up to {new_level}!")
                        
                        # ๐Ÿ“ˆ Update score (Atomicity!)
                        cursor.execute("""
                            UPDATE players 
                            SET score = ?, level = ?, last_played = datetime('now')
                            WHERE username = ?
                        """, (new_score, new_level, username))
                        
                        # ๐Ÿ… Add achievement if provided
                        if achievement:
                            cursor.execute("""
                                UPDATE players 
                                SET achievements = json_insert(
                                    achievements, '$[#]', ?
                                )
                                WHERE username = ?
                            """, (achievement, username))
                            print(f"๐Ÿ… {username} earned: {achievement}")
                
                return True
                
            except sqlite3.OperationalError as e:
                if "database is locked" in str(e):
                    # ๐Ÿ˜… Someone else is updating, wait and retry
                    time.sleep(0.1 * (attempt + 1))
                else:
                    raise
        
        return False
    
    def get_leaderboard(self, limit=10):
        # ๐Ÿ“Š Get top players (Read consistency!)
        cursor = self.conn.cursor()
        cursor.execute("""
            SELECT username, score, level 
            FROM players 
            ORDER BY score DESC 
            LIMIT ?
        """, (limit,))
        
        print("\n๐Ÿ† LEADERBOARD ๐Ÿ†")
        for i, (username, score, level) in enumerate(cursor.fetchall(), 1):
            emoji = "๐Ÿฅ‡" if i == 1 else "๐Ÿฅˆ" if i == 2 else "๐Ÿฅ‰" if i == 3 else "๐ŸŽฏ"
            print(f"{emoji} {i}. {username} - Score: {score} (Level {level})")

# ๐ŸŽฎ Simulate concurrent players!
leaderboard = GameLeaderboard()

def simulate_player(name):
    # ๐Ÿ‘พ Simulate a player earning points
    for _ in range(5):
        points = random.randint(10, 100)
        leaderboard.award_points(name, points)
        time.sleep(random.uniform(0.1, 0.3))

# ๐Ÿš€ Launch multiple players
players = ["Alice", "Bob", "Charlie", "Diana", "Eve"]
threads = []

for player in players:
    thread = Thread(target=simulate_player, args=(player,))
    threads.append(thread)
    thread.start()

# โณ Wait for all players
for thread in threads:
    thread.join()

# ๐Ÿ† Show final leaderboard
leaderboard.get_leaderboard()

๐Ÿš€ Advanced Concepts

๐Ÿง™โ€โ™‚๏ธ Isolation Levels: Fine-Tuning Transaction Behavior

When youโ€™re ready to level up, master isolation levels:

import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE

# ๐ŸŽฏ Different isolation levels for different needs
class IsolationDemo:
    def __init__(self):
        self.conn = psycopg2.connect("dbname=test user=python")
    
    def read_uncommitted_example(self):
        # ๐Ÿ‘ป Dirty reads allowed (PostgreSQL doesn't support this)
        self.conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_READ_UNCOMMITTED)
        print("โš ๏ธ Can see uncommitted changes from other transactions")
    
    def read_committed_example(self):
        # ๐Ÿ›ก๏ธ Default level - no dirty reads
        self.conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
        print("โœ… Only see committed data")
    
    def repeatable_read_example(self):
        # ๐Ÿ”’ Same query returns same results
        self.conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ)
        print("๐ŸŽฏ Consistent reads within transaction")
    
    def serializable_example(self):
        # ๐Ÿฐ Maximum isolation - transactions run as if serial
        self.conn.set_isolation_level(ISOLATION_LEVEL_SERIALIZABLE)
        print("๐Ÿ’ช Full ACID compliance, may retry on conflicts")

๐Ÿ—๏ธ Two-Phase Commit: Distributed Transactions

For the brave developers working with multiple databases:

import psycopg2

class DistributedBankTransfer:
    def __init__(self):
        # ๐ŸŒ Connect to multiple databases
        self.bank_a = psycopg2.connect("dbname=bank_a")
        self.bank_b = psycopg2.connect("dbname=bank_b")
    
    def transfer_money(self, from_account, to_account, amount):
        # ๐ŸŽฏ Two-phase commit for distributed transaction
        xid_a = self.bank_a.xid(1, "transfer", "bank_a")
        xid_b = self.bank_b.xid(1, "transfer", "bank_b")
        
        try:
            # ๐Ÿ“ Phase 1: Prepare
            with self.bank_a:
                cursor_a = self.bank_a.cursor()
                cursor_a.execute("BEGIN")
                cursor_a.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_account)
                )
                self.bank_a.tpc_prepare(xid_a)
            
            with self.bank_b:
                cursor_b = self.bank_b.cursor()
                cursor_b.execute("BEGIN")
                cursor_b.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_account)
                )
                self.bank_b.tpc_prepare(xid_b)
            
            # โœ… Phase 2: Commit both
            self.bank_a.tpc_commit(xid_a)
            self.bank_b.tpc_commit(xid_b)
            print("๐Ÿ’ธ Distributed transfer successful!")
            
        except Exception as e:
            # โŒ Rollback both on any failure
            self.bank_a.tpc_rollback(xid_a)
            self.bank_b.tpc_rollback(xid_b)
            print(f"๐Ÿ˜ฑ Transfer failed: {e}")
            raise

โš ๏ธ Common Pitfalls and Solutions

๐Ÿ˜ฑ Pitfall 1: Forgetting to Commit

# โŒ Wrong way - changes lost!
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
conn.close()  # ๐Ÿ’ฅ Oops! Changes not saved!

# โœ… Correct way - always commit!
conn = sqlite3.connect('data.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name) VALUES ('Alice')")
conn.commit()  # ๐Ÿ’พ Changes saved!
conn.close()

๐Ÿคฏ Pitfall 2: Deadlocks in Concurrent Transactions

# โŒ Dangerous - can cause deadlock!
def transfer_prone_to_deadlock(conn, from_id, to_id, amount):
    cursor = conn.cursor()
    # Thread 1: locks account 1, then 2
    # Thread 2: locks account 2, then 1
    # ๐Ÿ’ฅ DEADLOCK!
    cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", 
                  (amount, from_id))
    cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", 
                  (amount, to_id))

# โœ… Safe - always lock in same order!
def transfer_deadlock_free(conn, from_id, to_id, amount):
    cursor = conn.cursor()
    # ๐ŸŽฏ Always lock accounts in ID order
    first_id = min(from_id, to_id)
    second_id = max(from_id, to_id)
    
    if from_id == first_id:
        cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", 
                      (amount, first_id))
        cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", 
                      (amount, second_id))
    else:
        cursor.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", 
                      (amount, first_id))
        cursor.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?", 
                      (amount, second_id))

๐Ÿ› ๏ธ Best Practices

  1. ๐ŸŽฏ Use Context Managers: Let Python handle commits and rollbacks automatically
  2. ๐Ÿ“ Keep Transactions Short: Long transactions block other users
  3. ๐Ÿ›ก๏ธ Handle Exceptions: Always have rollback logic for failures
  4. ๐ŸŽจ Choose Right Isolation: Balance consistency needs with performance
  5. โœจ Test Concurrent Access: Simulate multiple users to find issues early

๐Ÿงช Hands-On Exercise

๐ŸŽฏ Challenge: Build a Banking System with Full ACID

Create a robust banking system with these features:

๐Ÿ“‹ Requirements:

  • โœ… Account creation with initial deposit
  • ๐Ÿ’ธ Money transfers between accounts
  • ๐Ÿ“Š Transaction history with timestamps
  • ๐Ÿ”’ Overdraft protection
  • ๐ŸŽจ Daily interest calculation

๐Ÿš€ Bonus Points:

  • Add concurrent transfer simulation
  • Implement account locking for maintenance
  • Create monthly statement generation

๐Ÿ’ก Solution

๐Ÿ” Click to see solution
import sqlite3
from datetime import datetime, timedelta
from decimal import Decimal
import threading
import time

class RobustBankingSystem:
    def __init__(self, db_name='bank.db'):
        self.db_name = db_name
        self.setup_database()
    
    def get_connection(self):
        # ๐Ÿ”Œ Thread-safe connection
        conn = sqlite3.connect(self.db_name, timeout=10.0)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys = ON")
        return conn
    
    def setup_database(self):
        with self.get_connection() as conn:
            conn.executescript('''
                CREATE TABLE IF NOT EXISTS accounts (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    account_number TEXT UNIQUE NOT NULL,
                    holder_name TEXT NOT NULL,
                    balance DECIMAL(15, 2) NOT NULL CHECK(balance >= 0),
                    account_type TEXT NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    is_locked BOOLEAN DEFAULT FALSE
                );
                
                CREATE TABLE IF NOT EXISTS transactions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    from_account TEXT,
                    to_account TEXT,
                    amount DECIMAL(15, 2) NOT NULL,
                    transaction_type TEXT NOT NULL,
                    description TEXT,
                    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    status TEXT DEFAULT 'completed',
                    FOREIGN KEY (from_account) REFERENCES accounts(account_number),
                    FOREIGN KEY (to_account) REFERENCES accounts(account_number)
                );
                
                CREATE INDEX IF NOT EXISTS idx_trans_timestamp 
                ON transactions(timestamp);
                
                CREATE INDEX IF NOT EXISTS idx_trans_accounts 
                ON transactions(from_account, to_account);
            ''')
    
    def create_account(self, holder_name, initial_deposit, account_type='savings'):
        # ๐Ÿฆ Create new account with ACID guarantees
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # Generate unique account number
            import random
            account_number = f"{account_type[:3].upper()}{random.randint(100000, 999999)}"
            
            try:
                # ๐Ÿ’ฐ Create account
                cursor.execute('''
                    INSERT INTO accounts (account_number, holder_name, balance, account_type)
                    VALUES (?, ?, ?, ?)
                ''', (account_number, holder_name, initial_deposit, account_type))
                
                # ๐Ÿ“ Record initial deposit
                cursor.execute('''
                    INSERT INTO transactions (to_account, amount, transaction_type, description)
                    VALUES (?, ?, 'deposit', 'Initial deposit')
                ''', (account_number, initial_deposit))
                
                print(f"โœ… Account {account_number} created for {holder_name}!")
                return account_number
                
            except sqlite3.IntegrityError:
                # ๐Ÿ”„ Retry with new number if duplicate
                return self.create_account(holder_name, initial_deposit, account_type)
    
    def transfer_money(self, from_account, to_account, amount):
        # ๐Ÿ’ธ Transfer with full ACID protection
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # ๐Ÿ”’ Lock accounts in order to prevent deadlock
            accounts = sorted([from_account, to_account])
            
            # Check if accounts exist and aren't locked
            cursor.execute('''
                SELECT account_number, balance, is_locked
                FROM accounts 
                WHERE account_number IN (?, ?)
                ORDER BY account_number
            ''', accounts)
            
            results = cursor.fetchall()
            if len(results) != 2:
                raise ValueError("โŒ Invalid account number(s)")
            
            account_data = {row['account_number']: row for row in results}
            
            # ๐Ÿ›ก๏ธ Validation checks
            from_data = account_data[from_account]
            to_data = account_data[to_account]
            
            if from_data['is_locked'] or to_data['is_locked']:
                raise ValueError("๐Ÿ”’ Account is locked for maintenance")
            
            if from_data['balance'] < amount:
                raise ValueError(f"๐Ÿ’ธ Insufficient funds! Balance: ${from_data['balance']}")
            
            # ๐Ÿ’ฐ Perform transfer
            cursor.execute('''
                UPDATE accounts 
                SET balance = balance - ? 
                WHERE account_number = ?
            ''', (amount, from_account))
            
            cursor.execute('''
                UPDATE accounts 
                SET balance = balance + ? 
                WHERE account_number = ?
            ''', (amount, to_account))
            
            # ๐Ÿ“ Record transaction
            cursor.execute('''
                INSERT INTO transactions 
                (from_account, to_account, amount, transaction_type, description)
                VALUES (?, ?, ?, 'transfer', ?)
            ''', (from_account, to_account, amount, 
                  f"Transfer from {from_account} to {to_account}"))
            
            print(f"โœ… Transferred ${amount} from {from_account} to {to_account}")
    
    def calculate_daily_interest(self, rate=0.03):
        # ๐Ÿ’ฐ Apply daily interest to all savings accounts
        daily_rate = rate / 365
        
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # ๐ŸŽฏ Get all savings accounts
            cursor.execute('''
                SELECT account_number, balance 
                FROM accounts 
                WHERE account_type = 'savings' AND is_locked = FALSE
            ''')
            
            for row in cursor.fetchall():
                interest = float(row['balance']) * daily_rate
                if interest > 0.01:  # Only add if more than 1 cent
                    cursor.execute('''
                        UPDATE accounts 
                        SET balance = balance + ? 
                        WHERE account_number = ?
                    ''', (interest, row['account_number']))
                    
                    cursor.execute('''
                        INSERT INTO transactions 
                        (to_account, amount, transaction_type, description)
                        VALUES (?, ?, 'interest', 'Daily interest')
                    ''', (row['account_number'], interest))
            
            print("โœจ Daily interest calculated and applied!")
    
    def get_balance(self, account_number):
        # ๐Ÿ’ฐ Get current balance
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT balance FROM accounts WHERE account_number = ?",
                (account_number,)
            )
            result = cursor.fetchone()
            return result['balance'] if result else None
    
    def get_statement(self, account_number, days=30):
        # ๐Ÿ“Š Generate account statement
        with self.get_connection() as conn:
            cursor = conn.cursor()
            
            # Get account info
            cursor.execute('''
                SELECT * FROM accounts WHERE account_number = ?
            ''', (account_number,))
            account = cursor.fetchone()
            
            if not account:
                raise ValueError("Account not found")
            
            # Get transactions
            since = datetime.now() - timedelta(days=days)
            cursor.execute('''
                SELECT * FROM transactions 
                WHERE (from_account = ? OR to_account = ?)
                AND timestamp >= ?
                ORDER BY timestamp DESC
            ''', (account_number, account_number, since))
            
            print(f"\n๐Ÿ“‹ STATEMENT FOR ACCOUNT {account_number}")
            print(f"๐Ÿ‘ค Account Holder: {account['holder_name']}")
            print(f"๐Ÿ’ฐ Current Balance: ${account['balance']}")
            print(f"๐Ÿ“… Period: Last {days} days\n")
            print("TRANSACTIONS:")
            print("-" * 60)
            
            for trans in cursor.fetchall():
                if trans['from_account'] == account_number:
                    print(f"๐Ÿ“ค {trans['timestamp'][:19]} | "
                          f"-${trans['amount']} | {trans['description']}")
                else:
                    print(f"๐Ÿ“ฅ {trans['timestamp'][:19]} | "
                          f"+${trans['amount']} | {trans['description']}")

# ๐ŸŽฎ Demo the banking system!
bank = RobustBankingSystem()

# Create accounts
alice_account = bank.create_account("Alice Johnson", 1000.00)
bob_account = bank.create_account("Bob Smith", 500.00)
charlie_account = bank.create_account("Charlie Brown", 2000.00)

# Simulate concurrent transfers
def random_transfers(name, account, other_accounts, count=5):
    import random
    for i in range(count):
        try:
            target = random.choice(other_accounts)
            amount = random.randint(10, 100)
            bank.transfer_money(account, target, amount)
            time.sleep(random.uniform(0.1, 0.5))
        except Exception as e:
            print(f"โš ๏ธ Transfer failed for {name}: {e}")

# ๐Ÿš€ Run concurrent transfers
threads = []
all_accounts = [alice_account, bob_account, charlie_account]

for name, account in [("Alice", alice_account), ("Bob", bob_account), ("Charlie", charlie_account)]:
    other_accounts = [acc for acc in all_accounts if acc != account]
    thread = threading.Thread(target=random_transfers, args=(name, account, other_accounts))
    threads.append(thread)
    thread.start()

# Wait for completion
for thread in threads:
    thread.join()

# Apply interest
bank.calculate_daily_interest()

# Show statements
print("\n" + "="*60 + "\n")
for account in all_accounts:
    bank.get_statement(account, days=1)
    print("\n" + "="*60 + "\n")

๐ŸŽ“ Key Takeaways

Youโ€™ve learned so much! Hereโ€™s what you can now do:

  • โœ… Implement transactions with confidence ๐Ÿ’ช
  • โœ… Apply ACID properties to ensure data integrity ๐Ÿ›ก๏ธ
  • โœ… Handle concurrent access without conflicts ๐ŸŽฏ
  • โœ… Debug transaction issues like a pro ๐Ÿ›
  • โœ… Build robust database applications with Python! ๐Ÿš€

Remember: Transactions are your safety net for database operations. Use them wisely! ๐Ÿค

๐Ÿค Next Steps

Congratulations! ๐ŸŽ‰ Youโ€™ve mastered database transactions and ACID properties!

Hereโ€™s what to do next:

  1. ๐Ÿ’ป Practice with the banking system exercise above
  2. ๐Ÿ—๏ธ Add transaction support to your existing database projects
  3. ๐Ÿ“š Move on to our next tutorial: Connection Pooling
  4. ๐ŸŒŸ Experiment with different isolation levels in your applications!

Remember: Every database expert started by understanding transactions. Keep practicing, keep learning, and most importantly, keep your data safe! ๐Ÿš€


Happy coding! ๐ŸŽ‰๐Ÿš€โœจ