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.

What we cover in this phase

In this phase we focus on:

• What a database transaction is and why it is needed
• COMMIT vs ROLLBACK
• Isolation and concurrency problems (dirty reads, lost updates, etc.)
• How transactions apply to our Wedding Planner scenarios (bookings, RSVP, conflicts)
• What connection pooling is and why it improves performance
• How pooling would be used in a real version of our application

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 successful only if all operations succeed.

Transactions are needed whenever multiple inserts/updates must happen together, without leaving the database in an inconsistent state.

Why are transactions important? (ACID)

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 an error happens during the process, the correct behavior is:

ROLLBACK;

Transaction example connected to our project

Scenario: Venue booking confirmation

In the Wedding Planner application, when the user books a venue we must:

• insert the booking record
• update wedding information
• ensure there is no overlapping booking for the same venue

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

COMMIT
If any step fails -> ROLLBACK

Conflict detection and why transaction is needed

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, the conflict check MUST be done inside a transaction, because two users could do this at the same time:

• User A checks availability → free
• User B checks availability → free
• Both insert a booking at the same moment
• Result → double-booking

Correct approach:

• conflict check + insert booking must execute atomically inside one 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, concurrent transactions may read or write inconsistent values.

Common concurrency problems:

• Dirty read – reading uncommitted changes
• Non-repeatable read – the same query returns different result inside one transaction
• Phantom read – new rows appear in repeated query
• Lost update – two users overwrite each other’s updates

Even though SQLite is simplified, production systems must carefully choose the isolation level.

Example connected to our project

If multiple users RSVP or update attendance at the same time without transaction + isolation:

• Attendance table could end up with duplicates
• RSVP results could be overwritten
• Counts per event may become incorrect

Therefore, operations such as RSVP update and attendance update should be handled transactionally.

Transactions inside Flask prototype

How transactions would be handled in Flask

In Flask applications (SQLAlchemy or DB sessions), the usual pattern is:

• start transaction
• execute queries
• commit
• if error → rollback

Pseudocode example:

try:
    db.session.begin()

    # check availability
    # insert booking
    # update wedding

    db.session.commit()
except Exception:
    db.session.rollback()

Even though our prototype is minimal and uses SQLite, the concept stays identical and scales 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 high load, the system becomes slow and unstable

With pooling:

• application keeps a pool of reusable connections
• requests take a free connection from the pool
• after finishing, the connection returns to the pool

Why pooling improves performance

Pooling provides:

• faster response time (no repeated connection overhead)
• controlled DB resource usage
• scalability for many concurrent users

Example:

• 100 users → 100 requests
• without pooling → 100 separate new connections
• with pooling → e.g. 10 connections reused and shared

Pooling in our Wedding Planner system

In a production version of our project, pooling would be important for endpoints that can be called frequently:

• /weddings
• /weddings/<id>/guests
• RSVP update operations
• availability checks

Availability checks can be called many times, so pooling prevents performance degradation.

Example pooling config (conceptual)

In SQLAlchemy a pool configuration could look like:

engine = create_engine(
    DB_URL,
    pool_size=10,
    max_overflow=20
)

Conclusion

This phase introduced two key concepts for advanced backend applications: database transactions and database connection pooling.

Transactions ensure correctness and consistency when multiple operations must succeed together (for example: booking + conflict check, RSVP updates, attendance updates). COMMIT and ROLLBACK guarantee the database is not left in a partial or inconsistent state.

Connection pooling improves scalability and performance by reusing database connections instead of creating a new one per request. These principles apply directly to our Wedding Planner system, especially for booking, RSVP, attendance management and venue availability conflict detection.