1"""
2Transactions and ACID Properties
3
4Demonstrates ACID properties of database transactions:
5- Atomicity: All or nothing execution
6- Consistency: Database moves from one valid state to another
7- Isolation: Concurrent transactions don't interfere
8- Durability: Committed changes persist
9
10Theory:
11- Transaction: logical unit of work (BEGIN...COMMIT/ROLLBACK)
12- Atomicity via logging and rollback mechanisms
13- Consistency enforced by constraints and triggers
14- Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE
15- Durability via write-ahead logging (WAL)
16
17Common anomalies:
18- Dirty read: Reading uncommitted data
19- Non-repeatable read: Same query returns different results
20- Phantom read: New rows appear in range queries
21"""
22
23import sqlite3
24import time
25from threading import Thread
26from typing import List
27
28
29def demonstrate_atomicity():
30 """Demonstrate atomicity: all-or-nothing execution."""
31 print("=" * 60)
32 print("ATOMICITY: All or Nothing")
33 print("=" * 60)
34 print()
35
36 conn = sqlite3.connect(':memory:')
37 cursor = conn.cursor()
38
39 # Setup: Bank accounts
40 cursor.execute('''
41 CREATE TABLE Accounts (
42 account_id INTEGER PRIMARY KEY,
43 balance REAL CHECK(balance >= 0)
44 )
45 ''')
46 cursor.execute("INSERT INTO Accounts VALUES (1, 1000), (2, 500)")
47 conn.commit()
48
49 print("Initial state:")
50 print("-" * 60)
51 cursor.execute("SELECT * FROM Accounts")
52 for row in cursor.fetchall():
53 print(f" Account {row[0]}: ${row[1]:.2f}")
54
55 # Successful transaction
56 print("\n\n1. SUCCESSFUL TRANSACTION: Transfer $200 from Account 1 to 2")
57 print("-" * 60)
58 try:
59 cursor.execute("BEGIN TRANSACTION")
60 cursor.execute("UPDATE Accounts SET balance = balance - 200 WHERE account_id = 1")
61 cursor.execute("UPDATE Accounts SET balance = balance + 200 WHERE account_id = 2")
62 cursor.execute("COMMIT")
63 print("✓ Transaction committed")
64 except sqlite3.Error as e:
65 cursor.execute("ROLLBACK")
66 print(f"✗ Transaction rolled back: {e}")
67
68 cursor.execute("SELECT * FROM Accounts")
69 print("\nAfter successful transfer:")
70 for row in cursor.fetchall():
71 print(f" Account {row[0]}: ${row[1]:.2f}")
72
73 # Failed transaction (constraint violation)
74 print("\n\n2. FAILED TRANSACTION: Try to transfer $1000 from Account 1")
75 print("-" * 60)
76 print(" (Would violate CHECK constraint: balance >= 0)")
77 try:
78 cursor.execute("BEGIN TRANSACTION")
79 cursor.execute("UPDATE Accounts SET balance = balance - 1000 WHERE account_id = 1")
80 cursor.execute("UPDATE Accounts SET balance = balance + 1000 WHERE account_id = 2")
81 cursor.execute("COMMIT")
82 print("ERROR: Should have failed!")
83 except sqlite3.IntegrityError as e:
84 cursor.execute("ROLLBACK")
85 print(f"✓ Transaction rolled back: {e}")
86
87 cursor.execute("SELECT * FROM Accounts")
88 print("\nAfter failed transfer (unchanged):")
89 for row in cursor.fetchall():
90 print(f" Account {row[0]}: ${row[1]:.2f}")
91
92 # Explicit rollback
93 print("\n\n3. EXPLICIT ROLLBACK: Start transfer but cancel")
94 print("-" * 60)
95 cursor.execute("BEGIN TRANSACTION")
96 cursor.execute("UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1")
97 print("Transfer started... changed Account 1 balance")
98 cursor.execute("ROLLBACK")
99 print("✓ Explicitly rolled back")
100
101 cursor.execute("SELECT * FROM Accounts")
102 print("\nAfter rollback (unchanged):")
103 for row in cursor.fetchall():
104 print(f" Account {row[0]}: ${row[1]:.2f}")
105
106 conn.close()
107 print()
108
109
110def demonstrate_consistency():
111 """Demonstrate consistency: maintain invariants."""
112 print("=" * 60)
113 print("CONSISTENCY: Maintain Database Invariants")
114 print("=" * 60)
115 print()
116
117 conn = sqlite3.connect(':memory:')
118 cursor = conn.cursor()
119
120 # Setup: Inventory system
121 cursor.execute('''
122 CREATE TABLE Inventory (
123 product_id INTEGER PRIMARY KEY,
124 quantity INTEGER CHECK(quantity >= 0),
125 reserved INTEGER DEFAULT 0 CHECK(reserved >= 0),
126 CHECK(reserved <= quantity)
127 )
128 ''')
129 cursor.execute("INSERT INTO Inventory VALUES (1, 100, 0)")
130 conn.commit()
131
132 print("Invariant: reserved <= quantity (can't reserve more than available)")
133 print("-" * 60)
134
135 print("\nInitial state:")
136 cursor.execute("SELECT * FROM Inventory")
137 print(f" Product 1: quantity={cursor.fetchone()[1]}, reserved=0")
138
139 # Valid reservation
140 print("\n1. Valid operation: Reserve 30 units")
141 print("-" * 60)
142 try:
143 cursor.execute("BEGIN TRANSACTION")
144 cursor.execute("UPDATE Inventory SET reserved = 30 WHERE product_id = 1")
145 cursor.execute("COMMIT")
146 print("✓ Reservation successful")
147 except sqlite3.IntegrityError as e:
148 cursor.execute("ROLLBACK")
149 print(f"✗ Failed: {e}")
150
151 cursor.execute("SELECT quantity, reserved FROM Inventory WHERE product_id = 1")
152 qty, res = cursor.fetchone()
153 print(f" State: quantity={qty}, reserved={res}")
154
155 # Invalid reservation
156 print("\n2. Invalid operation: Try to reserve 150 units (more than available)")
157 print("-" * 60)
158 try:
159 cursor.execute("BEGIN TRANSACTION")
160 cursor.execute("UPDATE Inventory SET reserved = 150 WHERE product_id = 1")
161 cursor.execute("COMMIT")
162 print("ERROR: Should have failed!")
163 except sqlite3.IntegrityError as e:
164 cursor.execute("ROLLBACK")
165 print(f"✓ Constraint violation prevented: {e}")
166
167 cursor.execute("SELECT quantity, reserved FROM Inventory WHERE product_id = 1")
168 qty, res = cursor.fetchone()
169 print(f" State: quantity={qty}, reserved={res} (unchanged)")
170
171 # Using triggers for complex constraints
172 print("\n3. Trigger for complex consistency rule")
173 print("-" * 60)
174 cursor.execute('''
175 CREATE TRIGGER validate_fulfillment
176 BEFORE UPDATE OF quantity ON Inventory
177 BEGIN
178 SELECT RAISE(ABORT, 'Cannot reduce quantity below reserved')
179 WHERE NEW.quantity < OLD.reserved;
180 END
181 ''')
182
183 print("Trigger: Cannot reduce quantity below reserved amount")
184 try:
185 cursor.execute("UPDATE Inventory SET quantity = 20 WHERE product_id = 1")
186 print("ERROR: Should have failed!")
187 except sqlite3.IntegrityError as e:
188 print(f"✓ Trigger prevented inconsistency: {e}")
189
190 conn.close()
191 print()
192
193
194def demonstrate_isolation_levels():
195 """Demonstrate isolation levels and read phenomena."""
196 print("=" * 60)
197 print("ISOLATION: Concurrent Transaction Isolation")
198 print("=" * 60)
199 print()
200
201 print("Isolation Levels (from least to most strict):")
202 print("-" * 60)
203 print("1. READ UNCOMMITTED: Allows dirty reads")
204 print("2. READ COMMITTED: Prevents dirty reads")
205 print("3. REPEATABLE READ: Prevents non-repeatable reads")
206 print("4. SERIALIZABLE: Prevents phantom reads")
207 print()
208
209 # SQLite uses SERIALIZABLE by default when WAL mode is enabled
210 print("SQLite Isolation:")
211 print("-" * 60)
212 print("Default: SERIALIZABLE (most strict)")
213 print("Writers block writers, readers don't block writers (MVCC)")
214 print()
215
216 conn1 = sqlite3.connect(':memory:')
217 conn2 = sqlite3.connect(':memory:')
218
219 # Setup
220 c1 = conn1.cursor()
221 c1.execute("CREATE TABLE Data (id INTEGER PRIMARY KEY, value INTEGER)")
222 c1.execute("INSERT INTO Data VALUES (1, 100)")
223 conn1.commit()
224
225 # Share database (in real scenario, both would connect to same file)
226 # For demonstration, we'll show concepts
227
228 print("Scenario: Dirty Read Prevention")
229 print("-" * 60)
230 print("Transaction 1: BEGIN")
231 print("Transaction 1: UPDATE Data SET value = 200 WHERE id = 1")
232 print("Transaction 2: SELECT value FROM Data WHERE id = 1")
233 print()
234 print("READ UNCOMMITTED: T2 would see 200 (dirty read)")
235 print("READ COMMITTED: T2 would see 100 (prevents dirty read)")
236 print("✓ SQLite prevents dirty reads")
237 print()
238
239 print("Scenario: Non-Repeatable Read")
240 print("-" * 60)
241 print("Transaction 1: SELECT value FROM Data WHERE id = 1 → 100")
242 print("Transaction 2: UPDATE Data SET value = 200 WHERE id = 1; COMMIT")
243 print("Transaction 1: SELECT value FROM Data WHERE id = 1 → ???")
244 print()
245 print("READ COMMITTED: T1 would see 200 (non-repeatable read)")
246 print("REPEATABLE READ: T1 would see 100 (snapshot isolation)")
247 print("✓ SQLite provides snapshot isolation")
248
249 conn1.close()
250 conn2.close()
251 print()
252
253
254def demonstrate_durability():
255 """Demonstrate durability: persistence of committed changes."""
256 print("=" * 60)
257 print("DURABILITY: Persistence After Commit")
258 print("=" * 60)
259 print()
260
261 import os
262 import tempfile
263
264 # Create temporary database file
265 fd, db_path = tempfile.mkstemp(suffix='.db')
266 os.close(fd)
267
268 try:
269 # Write data
270 print("1. Writing data to database file")
271 print("-" * 60)
272 conn = sqlite3.connect(db_path)
273 cursor = conn.cursor()
274 cursor.execute("CREATE TABLE Logs (id INTEGER PRIMARY KEY, message TEXT, timestamp REAL)")
275 cursor.execute("INSERT INTO Logs VALUES (1, 'System started', ?)", (time.time(),))
276 conn.commit()
277 print(f"✓ Committed 1 log entry to {db_path}")
278 conn.close()
279
280 # Simulate crash and recovery
281 print("\n2. Simulating database crash and recovery")
282 print("-" * 60)
283 print("Closing connection (simulating crash)...")
284 time.sleep(0.1)
285 print("Reopening database...")
286
287 conn = sqlite3.connect(db_path)
288 cursor = conn.cursor()
289 cursor.execute("SELECT * FROM Logs")
290 rows = cursor.fetchall()
291 print(f"✓ Recovered {len(rows)} log entries after 'crash'")
292 for row in rows:
293 print(f" {row}")
294
295 # WAL mode for better durability
296 print("\n3. Write-Ahead Logging (WAL) mode")
297 print("-" * 60)
298 cursor.execute("PRAGMA journal_mode=WAL")
299 print("✓ Enabled WAL mode")
300 print(" Benefits:")
301 print(" - Readers don't block writers")
302 print(" - Better crash recovery")
303 print(" - More durable commits")
304
305 cursor.execute("INSERT INTO Logs VALUES (2, 'WAL enabled', ?)", (time.time(),))
306 conn.commit()
307 conn.close()
308
309 finally:
310 # Cleanup
311 if os.path.exists(db_path):
312 os.unlink(db_path)
313 wal_path = db_path + '-wal'
314 if os.path.exists(wal_path):
315 os.unlink(wal_path)
316 shm_path = db_path + '-shm'
317 if os.path.exists(shm_path):
318 os.unlink(shm_path)
319
320 print()
321
322
323def demonstrate_transaction_problems():
324 """Demonstrate problems without proper transaction handling."""
325 print("=" * 60)
326 print("PROBLEMS WITHOUT TRANSACTIONS")
327 print("=" * 60)
328 print()
329
330 conn = sqlite3.connect(':memory:')
331 cursor = conn.cursor()
332
333 cursor.execute("CREATE TABLE Counter (value INTEGER)")
334 cursor.execute("INSERT INTO Counter VALUES (0)")
335 conn.commit()
336
337 print("Problem: Lost Update")
338 print("-" * 60)
339 print("Without proper locking, concurrent updates can be lost")
340 print()
341
342 # Read-modify-write without transaction
343 print("Bad approach (no transaction):")
344 cursor.execute("SELECT value FROM Counter")
345 value = cursor.fetchone()[0]
346 print(f" Read value: {value}")
347 print(f" Compute new value: {value + 1}")
348 # Another transaction could modify value here!
349 cursor.execute("UPDATE Counter SET value = ?", (value + 1,))
350 conn.commit()
351 print(" ✗ Lost update possible if concurrent access")
352
353 print("\n✓ Good approach (atomic update in transaction):")
354 cursor.execute("BEGIN TRANSACTION")
355 cursor.execute("UPDATE Counter SET value = value + 1")
356 cursor.execute("COMMIT")
357 print(" Use UPDATE with expression (atomic)")
358
359 cursor.execute("SELECT value FROM Counter")
360 print(f" Final value: {cursor.fetchone()[0]}")
361
362 conn.close()
363 print()
364
365
366if __name__ == "__main__":
367 print("""
368╔══════════════════════════════════════════════════════════════╗
369║ TRANSACTIONS AND ACID PROPERTIES ║
370║ Atomicity, Consistency, Isolation, Durability ║
371╚══════════════════════════════════════════════════════════════╝
372""")
373
374 demonstrate_atomicity()
375 demonstrate_consistency()
376 demonstrate_isolation_levels()
377 demonstrate_durability()
378 demonstrate_transaction_problems()
379
380 print("=" * 60)
381 print("SUMMARY: ACID PROPERTIES")
382 print("=" * 60)
383 print("Atomicity: All or nothing (COMMIT/ROLLBACK)")
384 print("Consistency: Maintain invariants (constraints, triggers)")
385 print("Isolation: Concurrent transactions don't interfere")
386 print("Durability: Committed changes persist (WAL)")
387 print()
388 print("Best Practices:")
389 print(" ✓ Use transactions for multi-step operations")
390 print(" ✓ Keep transactions short")
391 print(" ✓ Use appropriate isolation level")
392 print(" ✓ Handle errors with ROLLBACK")
393 print("=" * 60)