= 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.