s5.07.08 - acid transactions and record locking

CREATE TABLE account (
    account_id INTEGER PRIMARY KEY,
    balance INTEGER CHECK (balance >= 0)
);

INSERT INTO account (account_id, balance) 
VALUES (101, 500), (102, 300);

import sqlite3
import sys

def flawed_transfer(db_path, from_acc, to_acc, amount):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    print(f"1. Taking £{amount} from Account {from_acc}...")
    cursor.execute(f"UPDATE account SET balance = balance - {amount} WHERE account_id = {from_acc}")
    
    # Simulate a sudden power cut or server crash
    print("2. SYSTEM CRASH! Power lost before recipient gets the money.")
    conn.commit() # The partial transaction is saved!
    sys.exit() 
    
    print(f"3. Giving £{amount} to Account {to_acc}...")
    cursor.execute(f"UPDATE account SET balance = balance + {amount} WHERE account_id = {to_acc}")
    
    conn.commit()
    conn.close()

flawed_transfer('bank.db', 101, 102, 100)

import sqlite3

def secure_transfer(db_path, from_acc, to_acc, amount):
    # isolation_level=None allows us to manually control transactions
    conn = sqlite3.connect(db_path, isolation_level=None) 
    cursor = conn.cursor()
    
    try:
        cursor.execute("BEGIN TRANSACTION;") 
        print(f"Attempting to transfer £{amount} from {from_acc} to {to_acc}...")
        cursor.execute(f"UPDATE account SET balance = balance - {amount} WHERE account_id = {from_acc}")
        # Simulate a crash if transferring to a non-existent account
        if to_acc == 999:
            raise Exception("Simulated server crash mid-transaction!")
        cursor.execute(f"UPDATE account SET balance = balance + {amount} WHERE account_id = {to_acc}")
        conn.commit() # Save everything permanently 
        print("Transfer successful! Changes written to disk.")
        
    except Exception as e:
        conn.rollback() # Reverse everything 
        print(f"Transfer failed: {e}. Transaction rolled back.")
        
    finally:
        conn.close()

# Test 1: A successful transaction
secure_transfer('bank.db', 101, 102, 100)

# Test 2: A simulated crash halfway through (Tests Atomicity via ROLLBACK)
secure_transfer('bank.db', 101, 999, 100)

# Test 3: Attempting to send money we don't have (Tests Consistency via CHECK)
secure_transfer('bank.db', 101, 102, 5000)

import sqlite3
import time
import threading

def slow_transfer():
    conn = sqlite3.connect('bank.db', timeout=1, isolation_level=None)
    cursor = conn.cursor()
    try:
        cursor.execute("BEGIN EXCLUSIVE TRANSACTION;")
        print("User A: Started transaction and locked the database.")
        
        cursor.execute("UPDATE account SET balance = balance - 50 WHERE account_id = 101")
        print("User A: Update made. Pausing for 5 seconds (simulating network lag)...")
        
        time.sleep(5) # The lock is held open while the script sleeps
        
        conn.commit()
        print("User A: Transaction finally committed and lock released.")
    except Exception as e:
        print(f"User A Error: {e}")
    finally:
        conn.close()

def concurrent_transfer():
    time.sleep(1) # Wait 1 second to ensure User A grabs the lock first
    conn = sqlite3.connect('bank.db', timeout=1, isolation_level=None)
    cursor = conn.cursor()
    try:
        print("User B: Attempting to start a transaction...")
        cursor.execute("BEGIN EXCLUSIVE TRANSACTION;")
        cursor.execute("UPDATE account SET balance = balance + 100 WHERE account_id = 101")
        conn.commit()
        print("User B: Transaction successful. (You should not see this!)")
        
    except sqlite3.OperationalError as e:
        print(f"\nUser B ERROR: {e}")
        print("DBMS ACTION: The database protected the data by blocking User B!\n")
    finally:
        conn.close()

# Start both user actions at the same time
print("--- STARTING CONCURRENT TRANSFERS ---")
thread_a = threading.Thread(target=slow_transfer)
thread_b = threading.Thread(target=concurrent_transfer)

# Launch the threads
thread_a.start()
thread_b.start()

# Tell the main program to wait for both threads to finish
thread_a.join()
thread_b.join()

print("--- TRANSFERS COMPLETE ---")