Changes between Version 2 and Version 3 of P8
- Timestamp:
- 01/13/26 18:24:05 (7 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
P8
v2 v3 7 7 In our Wedding Planner prototype (Flask REST + SQLite), the same principles apply even though we use a lightweight database. In a production system, the application would typically use PostgreSQL/MySQL and pooling would be mandatory. 8 8 9 == What we cover in this phase == 10 In this phase we focus on: 11 * What a database transaction is and why it is needed 12 * COMMIT vs ROLLBACK 13 * Isolation and concurrency problems (dirty reads, lost updates, etc.) 14 * How transactions apply to our Wedding Planner scenarios (bookings, RSVP, conflicts) 15 * What connection pooling is and why it improves performance 16 * How pooling would be used in a real version of our application 17 9 18 == Transactions in databases == 10 19 11 20 === What is a transaction? === 12 21 A database transaction is a group of operations that must execute as a single unit. 13 The transaction is considered successful only if all operations succeed. 14 15 In practice, transactions are needed whenever multiple inserts/updates must happen together, without leaving the database in an inconsistent state. 16 17 18 === Why are transactions important? === 22 The transaction is successful only if all operations succeed. 23 24 Transactions are needed whenever multiple inserts/updates must happen together, without leaving the database in an inconsistent state. 25 26 === Why are transactions important? (ACID) === 19 27 Transactions ensure: 20 21 Atomicity → all operations succeed or none are applied 22 23 Consistency → data constraints remain valid 24 25 Isolation → concurrent operations do not corrupt data 26 27 Durability → once committed, changes remain stored 28 * Atomicity – all operations succeed or none are applied 29 * Consistency – data constraints remain valid 30 * Isolation – concurrent operations do not corrupt data 31 * Durability – once committed, changes remain stored 28 32 29 33 This is commonly summarized as the ACID principles. 30 31 34 32 35 === COMMIT and ROLLBACK === 33 36 A transaction typically works like this: 34 35 BEGIN → start transaction 36 37 Execute multiple SQL operations 38 39 COMMIT → save changes permanently if everything succeeded 40 41 ROLLBACK → undo everything if an error occurs 37 * BEGIN – start transaction 38 * Execute multiple SQL operations 39 * COMMIT – save changes permanently if everything succeeded 40 * ROLLBACK – undo everything if an error occurs 42 41 43 42 Example (SQL pseudocode): … … 50 49 51 50 COMMIT; 52 -- If any query fails → ROLLBACK; 53 }}} 54 51 }}} 52 53 If an error happens during the process, the correct behavior is: 54 55 {{{ 56 ROLLBACK; 57 }}} 55 58 56 59 === Transaction example connected to our project === 57 60 58 61 ==== Scenario: Venue booking confirmation ==== 59 In the Wedding Planner application, when the user makes a venue booking we must: 60 61 insert the booking record 62 63 update the wedding to reflect the new booking 64 65 ensure no conflicting booking exists 62 In the Wedding Planner application, when the user books a venue we must: 63 * insert the booking record 64 * update wedding information 65 * ensure there is no overlapping booking for the same venue 66 66 67 67 If one step fails, the database must not store partial data. … … 76 76 Insert venue_booking 77 77 78 Update wedding record (venue selected)78 Update wedding record 79 79 80 80 COMMIT … … 82 82 }}} 83 83 84 85 ==== Conflict detection and transaction need ==== 84 ==== Conflict detection and why transaction is needed ==== 86 85 In our prototype we check conflicts using the endpoint: 87 86 … … 90 89 }}} 91 90 92 In a real system, a conflict check MUST be done inside a transaction, because: 93 94 User A checks availability (free) 95 96 User B checks availability (free) 97 98 Both insert booking at same time 99 100 → double booking occurs 91 In a real system, the conflict check MUST be done inside a transaction, because two users could do this at the same time: 92 93 * User A checks availability → free 94 * User B checks availability → free 95 * Both insert a booking at the same moment 96 * Result → double-booking 101 97 102 98 Correct approach: 103 104 conflict check + insert booking must be executed atomically inside a transaction. 105 99 * conflict check + insert booking must execute atomically inside one transaction. 106 100 107 101 == Isolation and concurrency (basic overview) == … … 109 103 === What is isolation? === 110 104 Isolation defines how multiple users/requests interact with the database at the same time. 111 If isolation is weak, twoconcurrent transactions may read or write inconsistent values.105 If isolation is weak, concurrent transactions may read or write inconsistent values. 112 106 113 107 Common concurrency problems: 114 115 Dirty read → reading uncommitted changes 116 117 Non-repeatable read → same query returns different results in same transaction 118 119 Phantom read → new rows appear in repeated query 120 121 Even though SQLite is simplified, in production databases isolation level must be chosen carefully. 122 108 * Dirty read – reading uncommitted changes 109 * Non-repeatable read – the same query returns different result inside one transaction 110 * Phantom read – new rows appear in repeated query 111 * Lost update – two users overwrite each other’s updates 112 113 Even though SQLite is simplified, production systems must carefully choose the isolation level. 123 114 124 115 === Example connected to our project === 125 If two users RSVP at the same time, without proper isolation: 126 127 RSVP counts can become incorrect 128 129 Attendance table could end up with duplicates 130 131 The final result may not reflect correct guest statuses 132 133 Therefore, operations such as RSVP update and attendance update should be transactional. 116 If multiple users RSVP or update attendance at the same time without transaction + isolation: 117 * Attendance table could end up with duplicates 118 * RSVP results could be overwritten 119 * Counts per event may become incorrect 120 121 Therefore, operations such as RSVP update and attendance update should be handled transactionally. 134 122 135 123 == Transactions inside Flask prototype == 136 124 137 125 === How transactions would be handled in Flask === 138 In Flask (with SQLAlchemy or DB sessions), the implementation is usually: 139 140 start session/transaction 141 142 execute queries 143 144 commit 145 146 on exception → rollback 147 148 Pseudocode example (Flask-like): 126 In Flask applications (SQLAlchemy or DB sessions), the usual pattern is: 127 * start transaction 128 * execute queries 129 * commit 130 * if error → rollback 131 132 Pseudocode example: 149 133 150 134 {{{ 151 135 try: 152 db.session.begin() 153 154 # check availability 155 # insert booking 156 # update wedding 157 158 db.session.commit() 159 160 136 db.session.begin() 137 138 # check availability 139 # insert booking 140 # update wedding 141 142 db.session.commit() 161 143 except Exception: 162 db.session.rollback()163 }}} 164 165 Even if our prototype is minimal and uses SQLite, the concept remains the same and would scaledirectly to PostgreSQL.144 db.session.rollback() 145 }}} 146 147 Even though our prototype is minimal and uses SQLite, the concept stays identical and scales directly to PostgreSQL. 166 148 167 149 == Database connection pooling == … … 171 153 172 154 Without pooling: 173 174 each HTTP request opens a new DB connection 175 176 connecting is expensive 177 178 under load this becomes slow and unstable 155 * each HTTP request opens a new DB connection 156 * connecting is expensive 157 * under high load, the system becomes slow and unstable 179 158 180 159 With pooling: 181 182 application keeps a pool of reusable connections 183 184 requests take an available connection from the pool 185 186 after request finishes → connection is returned to the pool 160 * application keeps a pool of reusable connections 161 * requests take a free connection from the pool 162 * after finishing, the connection returns to the pool 187 163 188 164 === Why pooling improves performance === 189 Pooling gives: 190 191 faster response time (no repeated connection overhead) 192 193 controlled DB resource usage 194 195 scalability for multiple concurrent users 196 197 For example: 198 199 100 users → 100 requests 200 201 without pooling → 100 new connections created 202 203 with pooling → e.g. 10 reusable connections shared 165 Pooling provides: 166 * faster response time (no repeated connection overhead) 167 * controlled DB resource usage 168 * scalability for many concurrent users 169 170 Example: 171 * 100 users → 100 requests 172 * without pooling → 100 separate new connections 173 * with pooling → e.g. 10 connections reused and shared 204 174 205 175 === Pooling in our Wedding Planner system === 206 In the final real system, pooling would be very important for:207 208 /weddings209 /weddings/1/guests210 RSVP updates211 availability checks (these can be frequent) 212 Availability check endpoints can receive many calls, so pooling prevents performance degradation.176 In a production version of our project, pooling would be important for endpoints that can be called frequently: 177 * /weddings 178 * /weddings/<id>/guests 179 * RSVP update operations 180 * availability checks 181 182 Availability checks can be called many times, so pooling prevents performance degradation. 213 183 214 184 === Example pooling config (conceptual) === 215 In SQLAlchemy a pool configuration mightlook like:185 In SQLAlchemy a pool configuration could look like: 216 186 217 187 {{{ 218 188 engine = create_engine( 219 DB_URL,220 pool_size=10,221 max_overflow=20189 DB_URL, 190 pool_size=10, 191 max_overflow=20 222 192 ) 223 193 }}} 224 194 225 This ensures controlled and reusable DB connections.226 227 228 195 == Conclusion == 229 This phase introduced two key concepts for advanced backend applications: transactions and database connection pooling. 230 Transactions ensure correctness and consistency, especially when multiple operations must succeed together (such as bookings + conflict checks). 231 COMMIT and ROLLBACK ensure that the database is not left in a partial or broken state when an error occurs. 232 233 Connection pooling improves performance and scalability by reusing database connections instead of creating new ones for every request. 234 These principles directly apply to our Wedding Planner system, especially for booking, RSVP and venue availability scenarios, and they represent best practices for real production systems. 196 This phase introduced two key concepts for advanced backend applications: database transactions and database connection pooling. 197 198 Transactions ensure correctness and consistency when multiple operations must succeed together (for example: booking + conflict check, RSVP updates, attendance updates). 199 COMMIT and ROLLBACK guarantee the database is not left in a partial or inconsistent state. 200 201 Connection pooling improves scalability and performance by reusing database connections instead of creating a new one per request. 202 These principles apply directly to our Wedding Planner system, especially for booking, RSVP, attendance management and venue availability conflict detection.
