Changes between Version 1 and Version 2 of P8

Timestamp:

01/13/26 17:08:49 (7 days ago)

Author:

193284

Comment:

--

P8

@@ -1 @@
-= Phase P8
-
-== Content
-To be defined.
+= P8 – Advanced Application Development (Transactions, Pooling) =
+
+== Overview ==
+In this phase we explain two important database concepts used in real-world applications: database transactions and database connection pooling.
+These concepts are essential for ensuring data consistency, correctness and performance when multiple users interact with the system at the same time.
+
+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.
+
+== Transactions in databases ==
+
+=== What is a transaction? ===
+A database transaction is a group of operations that must execute as a single unit.
+The transaction is considered successful only if all operations succeed.
+
+In practice, transactions are needed whenever multiple inserts/updates must happen together, without leaving the database in an inconsistent state.
+
+
+=== Why are transactions important? ===
+Transactions ensure:
+
+Atomicity → all operations succeed or none are applied
+
+Consistency → data constraints remain valid
+
+Isolation → concurrent operations do not corrupt data
+
+Durability → once committed, changes remain stored
+
+This is commonly summarized as the ACID principles.
+
+
+=== COMMIT and ROLLBACK ===
+A transaction typically works like this:
+
+BEGIN → start transaction
+
+Execute multiple SQL operations
+
+COMMIT → save changes permanently if everything succeeded
+
+ROLLBACK → undo everything if an error occurs
+
+Example (SQL pseudocode):
+
+{{{
+BEGIN;
+
+UPDATE venue_booking SET status='CONFIRMED' WHERE booking_id=10;
+UPDATE wedding SET notes='Venue confirmed' WHERE wedding_id=1;
+
+COMMIT;
+-- If any query fails → ROLLBACK;
+}}}
+
+
+=== Transaction example connected to our project ===
+
+==== Scenario: Venue booking confirmation ====
+In the Wedding Planner application, when the user makes a venue booking we must:
+
+insert the booking record
+
+update the wedding to reflect the new booking
+
+ensure no conflicting booking exists
+
+If one step fails, the database must not store partial data.
+
+Pseudocode logic:
+
+{{{
+BEGIN TRANSACTION
+
+Check if venue is free at that time
+
+Insert venue_booking
+
+Update wedding record (venue selected)
+
+COMMIT
+If any step fails -> ROLLBACK
+}}}
+
+
+==== Conflict detection and transaction need ====
+In our prototype we check conflicts using the endpoint:
+
+{{{
+/availability/venue?venue_id=1&date=2026-06-20&start=15:00&end=21:00
+}}}
+
+In a real system, a conflict check MUST be done inside a transaction, because:
+
+User A checks availability (free)
+
+User B checks availability (free)
+
+Both insert booking at same time
+
+→ double booking occurs
+
+Correct approach:
+
+conflict check + insert booking must be executed atomically inside a transaction.
+
+
+== Isolation and concurrency (basic overview) ==
+
+=== What is isolation? ===
+Isolation defines how multiple users/requests interact with the database at the same time.
+If isolation is weak, two concurrent transactions may read or write inconsistent values.
+
+Common concurrency problems:
+
+Dirty read → reading uncommitted changes
+
+Non-repeatable read → same query returns different results in same transaction
+
+Phantom read → new rows appear in repeated query
+
+Even though SQLite is simplified, in production databases isolation level must be chosen carefully.
+
+
+=== Example connected to our project ===
+If two users RSVP at the same time, without proper isolation:
+
+RSVP counts can become incorrect
+
+Attendance table could end up with duplicates
+
+The final result may not reflect correct guest statuses
+
+Therefore, operations such as RSVP update and attendance update should be transactional.
+
+== Transactions inside Flask prototype ==
+
+=== How transactions would be handled in Flask ===
+In Flask (with SQLAlchemy or DB sessions), the implementation is usually:
+
+start session/transaction
+
+execute queries
+
+commit
+
+on exception → rollback
+
+Pseudocode example (Flask-like):
+
+{{{
+try:
+db.session.begin()
+
+# check availability
+# insert booking
+# update wedding
+
+db.session.commit()
+
+
+except Exception:
+db.session.rollback()
+}}}
+
+Even if our prototype is minimal and uses SQLite, the concept remains the same and would scale directly to PostgreSQL.
+
+== Database connection pooling ==
+
+=== What is connection pooling? ===
+Connection pooling means reusing open database connections instead of creating a new connection for every request.
+
+Without pooling:
+
+each HTTP request opens a new DB connection
+
+connecting is expensive
+
+under load this becomes slow and unstable
+
+With pooling:
+
+application keeps a pool of reusable connections
+
+requests take an available connection from the pool
+
+after request finishes → connection is returned to the pool
+
+=== Why pooling improves performance ===
+Pooling gives:
+
+faster response time (no repeated connection overhead)
+
+controlled DB resource usage
+
+scalability for multiple concurrent users
+
+For example:
+
+100 users → 100 requests
+
+without pooling → 100 new connections created
+
+with pooling → e.g. 10 reusable connections shared
+
+=== Pooling in our Wedding Planner system ===
+In the final real system, pooling would be very important for:
+
+/weddings
+/weddings/1/guests
+RSVP updates
+availability checks (these can be frequent)
+Availability check endpoints can receive many calls, so pooling prevents performance degradation.
+
+=== Example pooling config (conceptual) ===
+In SQLAlchemy a pool configuration might look like:
+
+{{{
+engine = create_engine(
+DB_URL,
+pool_size=10,
+max_overflow=20
+)
+}}}
+
+This ensures controlled and reusable DB connections.
+
+
+== Conclusion ==
+This phase introduced two key concepts for advanced backend applications: transactions and database connection pooling.
+Transactions ensure correctness and consistency, especially when multiple operations must succeed together (such as bookings + conflict checks).
+COMMIT and ROLLBACK ensure that the database is not left in a partial or broken state when an error occurs.
+
+Connection pooling improves performance and scalability by reusing database connections instead of creating new ones for every request.
+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.