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.