wiki:P8

Context Navigation

Version 5 (modified by 211171, 9 days ago) ( diff )
--

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 a real business scenario 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.

Transaction 1 – Full Wedding Bundle Booking

Business Scenario

When a user finalises their wedding plan, all four service providers must be booked atomically: venue, photographer, band, and registrar. If any single booking fails (conflict, capacity, data error), none should be persisted.

Tables Involved

venue_booking – insert + time-overlap check on venue_id + date
photographer_booking – insert + overlap check on photographer_id + date
band_booking – insert + overlap check on band_id + date
registrar_booking – insert + overlap check on registrar_id + date
wedding – update notes after all bookings succeed

Conflict Detection Logic

All four booking tables share the same overlap predicate. Two time windows overlap when:

start_time < $new_end_time AND end_time > $new_start_time

Full Transaction – SQL

BEGIN;

```
• STEP 1: Check venue availability
```

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';
-- If any row returned → ROLLBACK (conflict)

```
• STEP 2: Check photographer availability
```

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';

```
• STEP 3: Check band availability
```

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';

```
• STEP 4: Check registrar availability
```

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';

```
• STEP 5: Insert all four bookings
```

INSERT INTO project.venue_booking
(date, start_time, end_time, status, price, venue_id, wedding_id)
VALUES ('2026-06-20','16:00','23:00','confirmed',7000,1,1);

INSERT INTO project.photographer_booking
(date, start_time, end_time, status, photographer_id, wedding_id)
VALUES ('2026-06-20','14:00','22:00','confirmed',1,1);

INSERT INTO project.band_booking
(date, start_time, end_time, status, band_id, wedding_id)
VALUES ('2026-06-20','18:00','23:00','confirmed',1,1);

INSERT INTO project.registrar_booking
(date, start_time, end_time, status, registrar_id, wedding_id)
VALUES ('2026-06-20','10:00','10:30','confirmed',1,1);

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

COMMIT;
-- On any error at any step: ROLLBACK;

Flask Implementation (SQLAlchemy & Python)

from sqlalchemy.exc import SQLAlchemyError

def book_full_wedding(wedding_id, venue_cfg, photo_cfg, band_cfg, reg_cfg):
try:
db.session.begin()

```
    # Conflict checks – raise ValueError if any overlap found
    _check_overlap('venue_booking',        'venue_id',        venue_cfg)
    _check_overlap('photographer_booking', 'photographer_id', photo_cfg)
    _check_overlap('band_booking',         'band_id',         band_cfg)
    _check_overlap('registrar_booking',    'registrar_id',    reg_cfg)

    # Inserts
    db.session.add(VenueBooking(**venue_cfg,  wedding_id=wedding_id))
    db.session.add(PhotographerBooking(**photo_cfg, wedding_id=wedding_id))
    db.session.add(BandBooking(**band_cfg,    wedding_id=wedding_id))
    db.session.add(RegistrarBooking(**reg_cfg, wedding_id=wedding_id))

    db.session.query(Wedding).filter_by(wedding_id=wedding_id)\
        .update({'notes': 'All services confirmed'})

    db.session.commit()
    return {'status': 'ok', 'wedding_id': wedding_id}

except (ValueError, SQLAlchemyError) as e:
    db.session.rollback()
    return {'status': 'error', 'reason': str(e)}, 409
```

Transaction 2 – Guest Registration with Automatic RSVP Seeding

Business Scenario

When a new guest is added to a wedding, they must immediately receive a pending RSVP record for every existing event of that wedding (ceremony, registry, wedding etc.). This guarantees no event is orphaned without a guest decision, and no guest can appear in attendance without a prior RSVP.

Tables Involved

guest – insert the new guest record
event – read all events for this wedding_id
event_rsvp – insert one pending RSVP per event (enforced by uq_rsvp: guest_id + event_id)

Why a Transaction is Required

Without a transaction, a crash would happen after inserting the guest but before inserting the RSVPs, leaving a guest with no RSVP rows. The UNIQUE constraint on event_rsvp (guest_id, event_id) prevents duplicates but cannot help if the insert never happened.

Full Transaction – SQL

BEGIN;

```
• STEP 1: Insert new guest
```

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

```
• STEP 2: Insert a 'pending' RSVP for every event of this wedding
```

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

COMMIT;
-- Error: ROLLBACK;

Flask Implementation

def register_guest(wedding_id, first_name, last_name, email):
try:
db.session.begin()

```
    guest = Guest(first_name=first_name, last_name=last_name,
                  email=email, wedding_id=wedding_id)
    db.session.add(guest)
    db.session.flush()

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

    for event in events:
        rsvp = EventRSVP(
            status='pending',
            response_date=date.today(),
            guest_id=guest.guest_id,
            event_id=event.event_id
        )
        db.session.add(rsvp)

    db.session.commit()
    return {'guest_id': guest.guest_id, 'rsvps_created': len(events)}

except SQLAlchemyError as e:
    db.session.rollback()
    return {'error': str(e)}, 500
```

Key pattern: db.session.flush() is used to obtain the new guest_id from the database sequence before the transaction commits, so it can be used in the RSVP inserts within the same transaction.

Transaction 3 – RSVP Acceptance and Seat Assignment

Business Scenario

When a guest accepts an RSVP, the system must simultaneously update event_rsvp to accepted and create or update the attendance record with a table number. These two operations are inseparable: an accepted RSVP with no attendance row causes inconsistency.

Tables Involved

event_rsvp – update status from pending to accepted
attendance – UPSERT the row for this guest_id + event_id using the uq_attendance constraint

Full Transaction

BEGIN;

```
• STEP 1: Update RSVP status
```

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

```
• STEP 2: Upsert attendance record
```

INSERT INTO project.attendance
(status, table_number, role, guest_id, event_id)
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;
-- Error: ROLLBACK;

Key pattern

The attendance table has UNIQUE (guest_id, event_id). A guest may need to be reassigned to a different table later. Using ON CONFLICT DO UPDATE handles both the initial seat assignment and any re-assignment atomically, avoiding race conditions.

Transaction 4 – Wedding Cancellation with Status Cascade

Business Scenario

When a wedding is cancelled, every resource tied to it must be cancelled atomically: all service bookings (venue, photographer, band, registrar), all events, and all attendance records. A partial cancellation would leave inconsistent state.

Tables Involved

wedding – update status/notes
venue_booking – cancel bookings
photographer_booking – cancel bookings
band_booking – cancel bookings
registrar_booking – cancel bookings
event – cancel events
attendance – mark attendance absent

Full Transaction – SQL

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;
-- Error: ROLLBACK;

Concurrency Risk Without Transaction

A concurrent RSVP or attendance insert could occur between steps, causing inconsistent data tied to a cancelled wedding.

Transaction 5 – Budget Validation with Row Locking

Business Scenario

Before finalising a wedding, the system must ensure total cost does not exceed budget. The check and update must be atomic.

Tables Involved

wedding – locked using FOR UPDATE
venue_booking
photographer_booking
band_booking
registrar_booking

Full Transaction – SQL

BEGIN;

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

-- Calculate total cost across all services

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

Prevents TOCTOU race conditions by locking the wedding row during validation.

Savepoints – Partial Rollback

Concept

Savepoints allow partial rollback inside a transaction.

Example

BEGIN;

INSERT venue_booking;
INSERT photographer_booking;

SAVEPOINT after_core;

INSERT band_booking;

ROLLBACK TO SAVEPOINT after_core;

COMMIT;

Isolation Levels

Overview

Isolation defines how concurrent transactions behave.

Problems prevented:

Dirty reads
Non-repeatable reads
Phantom reads
Lost updates

Levels

READ COMMITTED – basic reads
REPEATABLE READ – RSVP consistency
SERIALIZABLE – full booking + budget validation

SQLAlchemy

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

Connection Pooling

Why needed

Pooling reuses DB connections instead of creating new ones per request.

Affected endpoints

availability endpoints
RSVP endpoints
booking endpoints

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
)

Parameters

pool_size – base connections
max_overflow – burst capacity
pool_timeout – wait limit
pool_recycle – prevents stale connections
pool_pre_ping – checks connection health

Conclusion

This phase introduced five full, schema-specific transactions for the Wedding Planner system:

Full wedding booking with atomic service allocation
Guest registration with RSVP seeding
RSVP acceptance with attendance UPSERT
Wedding cancellation with full cascade
Budget validation with row locking

These ensure ACID compliance, prevent race conditions, and guarantee consistent multi-table operations in a concurrent environment.

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text