P8 – Advanced Application Development (Transactions, Pooling)

Overview

This phase extends the foundational transaction concepts with concrete, schema-specific examples derived directly from the approved normalized database. Each transaction targets real business scenarios in the Wedding Planner application and references the exact tables from the schema.

In a production environment, these concepts ensure data consistency, correctness, and performance when multiple users interact with the system simultaneously.

What we cover in this phase

In this phase we focus on:

• Database transactions in real application scenarios
• Atomic operations across multiple related tables
• Conflict detection and concurrency handling
• Isolation levels and race conditions
• Savepoints for partial rollback logic
• Connection pooling and performance scaling
• Flask + SQLAlchemy implementation patterns

Transaction 1 – Full Wedding Bundle Booking

Business scenario

When a user finalises a wedding plan, all services must be booked atomically: venue, photographer, band, and registrar.

If any single booking fails, none of the changes should be saved.

Tables involved

• venue_booking (overlap check on venue_id + date)
• photographer_booking (overlap check on photographer_id + date)
• band_booking (overlap check on band_id + date)
• registrar_booking (overlap check on registrar_id + date)
• wedding (update notes after success)

Conflict detection logic

Two bookings overlap when:

start_time < new_end_time AND end_time > new_start_time

SQL Transaction

BEGIN;

-- Venue check
SELECT 1 FROM project.venue_booking
WHERE venue_id = 1
AND date = '2026-06-20'
AND start_time < '23:00'
AND end_time > '16:00'
AND status <> 'cancelled';

-- Photographer check
SELECT 1 FROM project.photographer_booking
WHERE photographer_id = 1
AND date = '2026-06-20'
AND start_time < '22:00'
AND end_time > '14:00'
AND status <> 'cancelled';

-- Band check
SELECT 1 FROM project.band_booking
WHERE band_id = 1
AND date = '2026-06-20'
AND start_time < '23:00'
AND end_time > '18:00'
AND status <> 'cancelled';

-- Registrar check
SELECT 1 FROM project.registrar_booking
WHERE registrar_id = 1
AND date = '2026-06-20'
AND start_time < '10:30'
AND end_time > '10:00'
AND status <> 'cancelled';

-- Inserts
INSERT INTO project.venue_booking (...)
VALUES ('2026-06-20','16:00','23:00','confirmed',7000,1,1);

INSERT INTO project.photographer_booking (...)
VALUES ('2026-06-20','14:00','22:00','confirmed',1,1);

INSERT INTO project.band_booking (...)
VALUES ('2026-06-20','18:00','23:00','confirmed',1,1);

INSERT INTO project.registrar_booking (...)
VALUES ('2026-06-20','10:00','10:30','confirmed',1,1);

-- Update wedding
UPDATE project.wedding
SET notes = 'All services confirmed'
WHERE wedding_id = 1;

COMMIT;

Flask implementation

def book_full_wedding(...):
try:
db.session.begin()

```
    _check_overlap(...)
    db.session.add(...)
    db.session.commit()

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

Transaction 2 – Guest Registration with RSVP Seeding

Business scenario

When a guest is added, they must automatically receive a pending RSVP for every event in the wedding.

Tables involved

• guest (insert)
• event (read all events)
• event_rsvp (insert pending rows)

Why transaction is needed

Without a transaction, a system crash could leave:

• guest inserted
• but missing RSVP records → inconsistent state

SQL Transaction

BEGIN;

INSERT INTO project.guest (...)
VALUES ('Marija','Ilievska','[marija@gmail.com](mailto:marija@gmail.com)',1)
RETURNING guest_id;

INSERT INTO project.event_rsvp (...)
SELECT 'pending', CURRENT_DATE, :new_guest_id, e.event_id
FROM project.event e
WHERE e.wedding_id = 1;

COMMIT;

Flask implementation

db.session.begin()

guest = Guest(...)
db.session.add(guest)
db.session.flush()

events = db.session.query(Event).filter_by(wedding_id=wedding_id).all()

for event in events:
db.session.add(EventRSVP(...))

db.session.commit()

Key idea: flush() is used to obtain guest_id before commit.

Transaction 3 – RSVP Acceptance and Seat Assignment

Business scenario

When a guest accepts an RSVP:

• RSVP status updates to accepted
• Attendance row is created or updated

Both must happen together.

Tables involved

• event_rsvp (update)
• attendance (UPSERT)

SQL Transaction

BEGIN;

UPDATE project.event_rsvp
SET status = 'accepted',
response_date = CURRENT_DATE
WHERE guest_id = 1 AND event_id = 2;

INSERT INTO project.attendance (...)
VALUES ('attending', 5, 'Guest', 1, 2)
ON CONFLICT (guest_id, event_id)
DO UPDATE SET
status = EXCLUDED.status,
table_number = EXCLUDED.table_number,
role = EXCLUDED.role;

COMMIT;

Key concept

ON CONFLICT prevents race conditions and ensures atomic seat assignment.

Transaction 4 – Wedding Cancellation with Cascade

Business scenario

Cancelling a wedding must cascade across:

• bookings
• events
• attendance records

Partial cancellation is not allowed.

Tables involved

• wedding
• venue_booking
• photographer_booking
• band_booking
• registrar_booking
• event
• attendance

SQL Transaction

BEGIN;

UPDATE project.wedding
SET notes = 'Wedding cancelled'
WHERE wedding_id = 1;

UPDATE project.venue_booking SET status='cancelled' WHERE wedding_id=1;
UPDATE project.photographer_booking SET status='cancelled' WHERE wedding_id=1;
UPDATE project.band_booking SET status='cancelled' WHERE wedding_id=1;
UPDATE project.registrar_booking SET status='cancelled' WHERE wedding_id=1;

UPDATE project.event SET status='cancelled' WHERE wedding_id=1;

UPDATE project.attendance
SET status='absent'
WHERE event_id IN (
SELECT event_id FROM project.event WHERE wedding_id = 1
);

COMMIT;

Concurrency risk

Without a transaction, new RSVP updates could be inserted during cancellation, causing inconsistent state.

Transaction 5 – Budget Validation with Row Locking

Business scenario

Before finalising a wedding, ensure total cost does not exceed budget.

Must prevent race conditions where new bookings are inserted during validation.

Tables involved

• wedding (FOR UPDATE lock)
• venue_booking
• photographer_booking
• band_booking
• registrar_booking

SQL Transaction

BEGIN;

SELECT budget FROM project.wedding
WHERE wedding_id = 1
FOR UPDATE;

-- Compute total cost (venue + photographer + band + registrar)

IF total_cost > budget THEN
ROLLBACK;
END IF;

UPDATE project.wedding
SET notes = 'Budget validated – wedding finalised'
WHERE wedding_id = 1;

COMMIT;

Why FOR UPDATE matters

It prevents TOCTOU (Time-of-check vs Time-of-use) race conditions by locking the wedding row.

Savepoints – Partial Rollback

Concept

Savepoints allow partial rollback inside a transaction without cancelling everything.

Example

BEGIN;

INSERT venue_booking;
INSERT photographer_booking;

SAVEPOINT after_core;

INSERT band_booking;

ROLLBACK TO SAVEPOINT after_core;

COMMIT;

Core bookings remain, optional upgrade is discarded safely.

Isolation Levels

Overview

Isolation controls how concurrent transactions interact.

Common problems:

• Dirty reads
• Non-repeatable reads
• Phantom reads
• Lost updates

Levels used in this system

• READ COMMITTED – simple reads (guest lists, catalog)
• REPEATABLE READ – RSVP operations
• SERIALIZABLE – full booking and budget validation

SQLAlchemy setting

db.session.execute(text('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'))

Connection Pooling

Why pooling is needed

Without pooling:

• each request opens a new DB connection
• high overhead and slow response

With pooling:

• connections are reused
• better scalability and stability

Key endpoints affected

• /availability/*
• /rsvp
• /weddings/<id>/book-all
• /guests

Pool configuration

engine = create_engine(
'postgresql://user:pass@localhost/weddingdb',
pool_size=10,
max_overflow=20,
pool_timeout=30,
pool_recycle=1800,
pool_pre_ping=True
)

Parameter meaning

• pool_size – base connections
• max_overflow – burst capacity
• pool_timeout – wait time for connection
• pool_recycle – avoid stale DB connections
• pool_pre_ping – detect dead connections

Conclusion

This phase introduced advanced database reliability and scalability concepts applied directly to the Wedding Planner system.

Key outcomes:

• Transactions ensure atomic multi-table operations
• Concurrency control prevents race conditions and inconsistencies
• Isolation levels protect against anomalies
• Savepoints allow partial rollback for optional features
• Connection pooling improves performance under load

These mechanisms are essential for production-grade backend systems handling concurrent users and complex relational workflows.