Changes between Version 4 and Version 5 of P8


Ignore:
Timestamp:
05/17/26 18:15:15 (9 days ago)
Author:
211171
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • P8

    v4 v5  
    22
    33== Overview ==
    4 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.
     4This 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.
    55
    66In a production environment, these concepts ensure data consistency, correctness, and performance when multiple users interact with the system simultaneously.
    77
    8 == What we cover in this phase ==
    9 In this phase we focus on:
    10 
    11 * Database transactions in real application scenarios
    12 * Atomic operations across multiple related tables
    13 * Conflict detection and concurrency handling
    14 * Isolation levels and race conditions
    15 * Savepoints for partial rollback logic
    16 * Connection pooling and performance scaling
    17 * Flask + SQLAlchemy implementation patterns
    18 
    198== Transaction 1 – Full Wedding Bundle Booking ==
    20 
    21 === Business scenario ===
    22 When a user finalises a wedding plan, all services must be booked atomically:
    23 venue, photographer, band, and registrar.
    24 
    25 If any single booking fails, none of the changes should be saved.
    26 
    27 === Tables involved ===
    28 
    29 * venue_booking (overlap check on venue_id + date)
    30 * photographer_booking (overlap check on photographer_id + date)
    31 * band_booking (overlap check on band_id + date)
    32 * registrar_booking (overlap check on registrar_id + date)
    33 * wedding (update notes after success)
    34 
    35 === Conflict detection logic ===
    36 Two bookings overlap when:
    37 {{{
    38 start_time < new_end_time AND end_time > new_start_time
    39 }}}
    40 
    41 === SQL Transaction ===
    42 {{{
    43 BEGIN;
    44 
    45 -- Venue check
     9=== Business Scenario ===
     10When 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.
     11
     12=== Tables Involved ===
     13• venue_booking – insert + time-overlap check on venue_id + date
     14• photographer_booking – insert + overlap check on photographer_id + date
     15• band_booking – insert + overlap check on band_id + date
     16• registrar_booking – insert + overlap check on registrar_id + date
     17• wedding – update notes after all bookings succeed
     18
     19=== Conflict Detection Logic ===
     20All four booking tables share the same overlap predicate. Two time windows overlap when:
     21
     22start_time < $new_end_time AND end_time > $new_start_time
     23
     24=== Full Transaction – SQL ===
     25{{{
     26BEGIN;
     27
     28```
     29• STEP 1: Check venue availability
     30```
     31
    4632SELECT 1 FROM project.venue_booking
    47 WHERE venue_id = 1
    48 AND date = '2026-06-20'
    49 AND start_time < '23:00'
    50 AND end_time > '16:00'
    51 AND status <> 'cancelled';
    52 
    53 -- Photographer check
     33WHERE  venue_id    = 1
     34AND    date        = '2026-06-20'
     35AND    start_time  < '23:00'
     36AND    end_time    > '16:00'
     37AND    status     <> 'cancelled';
     38-- If any row returned → ROLLBACK (conflict)
     39
     40```
     41• STEP 2: Check photographer availability
     42```
     43
    5444SELECT 1 FROM project.photographer_booking
    55 WHERE photographer_id = 1
    56 AND date = '2026-06-20'
    57 AND start_time < '22:00'
    58 AND end_time > '14:00'
    59 AND status <> 'cancelled';
    60 
    61 -- Band check
     45WHERE  photographer_id = 1
     46AND    date             = '2026-06-20'
     47AND    start_time       < '22:00'
     48AND    end_time         > '14:00'
     49AND    status          <> 'cancelled';
     50
     51```
     52• STEP 3: Check band availability
     53```
     54
    6255SELECT 1 FROM project.band_booking
    63 WHERE band_id = 1
    64 AND date = '2026-06-20'
    65 AND start_time < '23:00'
    66 AND end_time > '18:00'
    67 AND status <> 'cancelled';
    68 
    69 -- Registrar check
     56WHERE  band_id    = 1
     57AND    date        = '2026-06-20'
     58AND    start_time  < '23:00'
     59AND    end_time    > '18:00'
     60AND    status     <> 'cancelled';
     61
     62```
     63• STEP 4: Check registrar availability
     64```
     65
    7066SELECT 1 FROM project.registrar_booking
    71 WHERE registrar_id = 1
    72 AND date = '2026-06-20'
    73 AND start_time < '10:30'
    74 AND end_time > '10:00'
    75 AND status <> 'cancelled';
    76 
    77 -- Inserts
    78 INSERT INTO project.venue_booking (...)
     67WHERE  registrar_id = 1
     68AND    date          = '2026-06-20'
     69AND    start_time    < '10:30'
     70AND    end_time      > '10:00'
     71AND    status       <> 'cancelled';
     72
     73```
     74• STEP 5: Insert all four bookings
     75```
     76
     77INSERT INTO project.venue_booking
     78(date, start_time, end_time, status, price, venue_id, wedding_id)
    7979VALUES ('2026-06-20','16:00','23:00','confirmed',7000,1,1);
    8080
    81 INSERT INTO project.photographer_booking (...)
     81INSERT INTO project.photographer_booking
     82(date, start_time, end_time, status, photographer_id, wedding_id)
    8283VALUES ('2026-06-20','14:00','22:00','confirmed',1,1);
    8384
    84 INSERT INTO project.band_booking (...)
     85INSERT INTO project.band_booking
     86(date, start_time, end_time, status, band_id, wedding_id)
    8587VALUES ('2026-06-20','18:00','23:00','confirmed',1,1);
    8688
    87 INSERT INTO project.registrar_booking (...)
     89INSERT INTO project.registrar_booking
     90(date, start_time, end_time, status, registrar_id, wedding_id)
    8891VALUES ('2026-06-20','10:00','10:30','confirmed',1,1);
    8992
    90 -- Update wedding
     93-- STEP 6: Update wedding record
    9194UPDATE project.wedding
    92 SET notes = 'All services confirmed'
    93 WHERE wedding_id = 1;
    94 
    95 COMMIT;
    96 }}}
    97 
    98 === Flask implementation ===
    99 {{{
    100 def book_full_wedding(...):
     95SET    notes = 'All services confirmed'
     96WHERE  wedding_id = 1;
     97
     98COMMIT;
     99-- On any error at any step: ROLLBACK;
     100}}}
     101
     102=== Flask Implementation (SQLAlchemy & Python) ===
     103{{{
     104from sqlalchemy.exc import SQLAlchemyError
     105
     106def book_full_wedding(wedding_id, venue_cfg, photo_cfg, band_cfg, reg_cfg):
    101107try:
    102108db.session.begin()
    103109
    104110```
    105     _check_overlap(...)
    106     db.session.add(...)
     111    # Conflict checks – raise ValueError if any overlap found
     112    _check_overlap('venue_booking',        'venue_id',        venue_cfg)
     113    _check_overlap('photographer_booking', 'photographer_id', photo_cfg)
     114    _check_overlap('band_booking',         'band_id',         band_cfg)
     115    _check_overlap('registrar_booking',    'registrar_id',    reg_cfg)
     116
     117    # Inserts
     118    db.session.add(VenueBooking(**venue_cfg,  wedding_id=wedding_id))
     119    db.session.add(PhotographerBooking(**photo_cfg, wedding_id=wedding_id))
     120    db.session.add(BandBooking(**band_cfg,    wedding_id=wedding_id))
     121    db.session.add(RegistrarBooking(**reg_cfg, wedding_id=wedding_id))
     122
     123    db.session.query(Wedding).filter_by(wedding_id=wedding_id)\
     124        .update({'notes': 'All services confirmed'})
     125
    107126    db.session.commit()
    108 
    109 except Exception:
     127    return {'status': 'ok', 'wedding_id': wedding_id}
     128
     129except (ValueError, SQLAlchemyError) as e:
    110130    db.session.rollback()
    111 ```
    112 
    113 }}}
    114 
    115 == Transaction 2 – Guest Registration with RSVP Seeding ==
    116 
    117 === Business scenario ===
    118 When a guest is added, they must automatically receive a pending RSVP for every event in the wedding.
    119 
    120 === Tables involved ===
    121 
    122 * guest (insert)
    123 * event (read all events)
    124 * event_rsvp (insert pending rows)
    125 
    126 === Why transaction is needed ===
    127 Without a transaction, a system crash could leave:
    128 
    129 * guest inserted
    130 * but missing RSVP records → inconsistent state
    131 
    132 === SQL Transaction ===
    133 {{{
    134 BEGIN;
    135 
    136 INSERT INTO project.guest (...)
    137 VALUES ('Marija','Ilievska','[marija@gmail.com](mailto:marija@gmail.com)',1)
     131    return {'status': 'error', 'reason': str(e)}, 409
     132```
     133
     134}}}
     135
     136== Transaction 2 – Guest Registration with Automatic RSVP Seeding ==
     137=== Business Scenario ===
     138When 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.
     139
     140=== Tables Involved ===
     141• guest – insert the new guest record
     142• event – read all events for this wedding_id
     143• event_rsvp – insert one pending RSVP per event (enforced by uq_rsvp: guest_id + event_id)
     144
     145=== Why a Transaction is Required ===
     146Without 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.
     147
     148=== Full Transaction – SQL ===
     149{{{
     150BEGIN;
     151
     152```
     153• STEP 1: Insert new guest
     154```
     155
     156INSERT INTO project.guest (first_name, last_name, email, wedding_id)
     157VALUES ('Marija', 'Ilievska', '[marija@gmail.com](mailto:marija@gmail.com)', 1)
    138158RETURNING guest_id;
    139159
    140 INSERT INTO project.event_rsvp (...)
    141 SELECT 'pending', CURRENT_DATE, :new_guest_id, e.event_id
     160```
     161• STEP 2: Insert a 'pending' RSVP for every event of this wedding
     162```
     163
     164INSERT INTO project.event_rsvp (status, response_date, guest_id, event_id)
     165SELECT
     166'pending',
     167CURRENT_DATE,
     168:new_guest_id,
     169e.event_id
    142170FROM project.event e
    143171WHERE e.wedding_id = 1;
    144172
    145173COMMIT;
    146 }}}
    147 
    148 === Flask implementation ===
    149 {{{
     174-- Error: ROLLBACK;
     175}}}
     176
     177=== Flask Implementation ===
     178{{{
     179def register_guest(wedding_id, first_name, last_name, email):
     180try:
    150181db.session.begin()
    151182
    152 guest = Guest(...)
    153 db.session.add(guest)
    154 db.session.flush()
    155 
    156 events = db.session.query(Event).filter_by(wedding_id=wedding_id).all()
    157 
    158 for event in events:
    159 db.session.add(EventRSVP(...))
    160 
    161 db.session.commit()
    162 }}}
    163 
    164 Key idea: flush() is used to obtain guest_id before commit.
     183```
     184    guest = Guest(first_name=first_name, last_name=last_name,
     185                  email=email, wedding_id=wedding_id)
     186    db.session.add(guest)
     187    db.session.flush()
     188
     189    events = db.session.query(Event)\
     190        .filter_by(wedding_id=wedding_id).all()
     191
     192    for event in events:
     193        rsvp = EventRSVP(
     194            status='pending',
     195            response_date=date.today(),
     196            guest_id=guest.guest_id,
     197            event_id=event.event_id
     198        )
     199        db.session.add(rsvp)
     200
     201    db.session.commit()
     202    return {'guest_id': guest.guest_id, 'rsvps_created': len(events)}
     203
     204except SQLAlchemyError as e:
     205    db.session.rollback()
     206    return {'error': str(e)}, 500
     207```
     208
     209}}}
     210
     211Key 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.
    165212
    166213== Transaction 3 – RSVP Acceptance and Seat Assignment ==
    167 
    168 === Business scenario ===
    169 When a guest accepts an RSVP:
    170 
    171 * RSVP status updates to accepted
    172 * Attendance row is created or updated
    173 
    174 Both must happen together.
    175 
    176 === Tables involved ===
    177 
    178 * event_rsvp (update)
    179 * attendance (UPSERT)
    180 
    181 === SQL Transaction ===
    182 {{{
    183 BEGIN;
     214=== Business Scenario ===
     215When 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.
     216
     217=== Tables Involved ===
     218• event_rsvp – update status from pending to accepted
     219• attendance – UPSERT the row for this guest_id + event_id using the uq_attendance constraint
     220
     221=== Full Transaction ===
     222{{{
     223BEGIN;
     224
     225```
     226• STEP 1: Update RSVP status
     227```
    184228
    185229UPDATE project.event_rsvp
    186 SET status = 'accepted',
     230SET    status        = 'accepted',
    187231response_date = CURRENT_DATE
    188 WHERE guest_id = 1 AND event_id = 2;
    189 
    190 INSERT INTO project.attendance (...)
    191 VALUES ('attending', 5, 'Guest', 1, 2)
     232WHERE  guest_id  = 1
     233AND    event_id  = 2;
     234else ROLLBACK
     235
     236```
     237• STEP 2: Upsert attendance record
     238```
     239
     240INSERT INTO project.attendance
     241(status, table_number, role, guest_id, event_id)
     242VALUES
     243('attending', 5, 'Guest', 1, 2)
    192244ON CONFLICT (guest_id, event_id)
    193245DO UPDATE SET
    194 status = EXCLUDED.status,
     246status       = EXCLUDED.status,
    195247table_number = EXCLUDED.table_number,
    196 role = EXCLUDED.role;
    197 
    198 COMMIT;
    199 }}}
    200 
    201 === Key concept ===
    202 ON CONFLICT prevents race conditions and ensures atomic seat assignment.
    203 
    204 == Transaction 4 – Wedding Cancellation with Cascade ==
    205 
    206 === Business scenario ===
    207 Cancelling a wedding must cascade across:
    208 
    209 * bookings
    210 * events
    211 * attendance records
    212 
    213 Partial cancellation is not allowed.
    214 
    215 === Tables involved ===
    216 
    217 * wedding
    218 * venue_booking
    219 * photographer_booking
    220 * band_booking
    221 * registrar_booking
    222 * event
    223 * attendance
    224 
    225 === SQL Transaction ===
     248role         = EXCLUDED.role;
     249
     250COMMIT;
     251-- Error: ROLLBACK;
     252}}}
     253
     254=== Key pattern ===
     255The 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.
     256
     257== Transaction 4 – Wedding Cancellation with Status Cascade ==
     258=== Business Scenario ===
     259When 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.
     260
     261=== Tables Involved ===
     262• wedding – update status/notes
     263• venue_booking – cancel bookings
     264• photographer_booking – cancel bookings
     265• band_booking – cancel bookings
     266• registrar_booking – cancel bookings
     267• event – cancel events
     268• attendance – mark attendance absent
     269
     270=== Full Transaction – SQL ===
    226271{{{
    227272BEGIN;
    228273
    229274UPDATE project.wedding
    230 SET notes = 'Wedding cancelled'
    231 WHERE wedding_id = 1;
    232 
    233 UPDATE project.venue_booking SET status='cancelled' WHERE wedding_id=1;
    234 UPDATE project.photographer_booking SET status='cancelled' WHERE wedding_id=1;
    235 UPDATE project.band_booking SET status='cancelled' WHERE wedding_id=1;
    236 UPDATE project.registrar_booking SET status='cancelled' WHERE wedding_id=1;
    237 
    238 UPDATE project.event SET status='cancelled' WHERE wedding_id=1;
     275SET    notes = 'Wedding cancelled'
     276WHERE  wedding_id = 1;
     277
     278UPDATE project.venue_booking
     279SET    status = 'cancelled'
     280WHERE  wedding_id = 1;
     281
     282UPDATE project.photographer_booking
     283SET    status = 'cancelled'
     284WHERE  wedding_id = 1;
     285
     286UPDATE project.band_booking
     287SET    status = 'cancelled'
     288WHERE  wedding_id = 1;
     289
     290UPDATE project.registrar_booking
     291SET    status = 'cancelled'
     292WHERE  wedding_id = 1;
     293
     294UPDATE project.event
     295SET    status = 'cancelled'
     296WHERE  wedding_id = 1;
    239297
    240298UPDATE project.attendance
    241 SET status='absent'
    242 WHERE event_id IN (
    243 SELECT event_id FROM project.event WHERE wedding_id = 1
     299SET    status = 'absent'
     300WHERE  event_id IN (
     301SELECT event_id FROM project.event
     302WHERE  wedding_id = 1
    244303);
    245304
    246305COMMIT;
    247 }}}
    248 
    249 === Concurrency risk ===
    250 Without a transaction, new RSVP updates could be inserted during cancellation, causing inconsistent state.
     306-- Error: ROLLBACK;
     307}}}
     308
     309=== Concurrency Risk Without Transaction ===
     310A concurrent RSVP or attendance insert could occur between steps, causing inconsistent data tied to a cancelled wedding.
    251311
    252312== Transaction 5 – Budget Validation with Row Locking ==
    253 
    254 === Business scenario ===
    255 Before finalising a wedding, ensure total cost does not exceed budget.
    256 
    257 Must prevent race conditions where new bookings are inserted during validation.
    258 
    259 === Tables involved ===
    260 
    261 * wedding (FOR UPDATE lock)
    262 * venue_booking
    263 * photographer_booking
    264 * band_booking
    265 * registrar_booking
    266 
    267 === SQL Transaction ===
     313=== Business Scenario ===
     314Before finalising a wedding, the system must ensure total cost does not exceed budget. The check and update must be atomic.
     315
     316=== Tables Involved ===
     317• wedding – locked using FOR UPDATE
     318• venue_booking
     319• photographer_booking
     320• band_booking
     321• registrar_booking
     322
     323=== Full Transaction – SQL ===
    268324{{{
    269325BEGIN;
    270326
    271327SELECT budget FROM project.wedding
    272 WHERE wedding_id = 1
     328WHERE  wedding_id = 1
    273329FOR UPDATE;
    274330
    275 -- Compute total cost (venue + photographer + band + registrar)
     331-- Calculate total cost across all services
    276332
    277333IF total_cost > budget THEN
    … …  
    280336
    281337UPDATE project.wedding
    282 SET notes = 'Budget validated – wedding finalised'
    283 WHERE wedding_id = 1;
     338SET    notes = 'Budget validated – wedding finalised'
     339WHERE  wedding_id = 1;
    284340
    285341COMMIT;
    … …  
    287343
    288344=== Why FOR UPDATE matters ===
    289 It prevents TOCTOU (Time-of-check vs Time-of-use) race conditions by locking the wedding row.
     345Prevents TOCTOU race conditions by locking the wedding row during validation.
    290346
    291347== Savepoints – Partial Rollback ==
    292 
    293348=== Concept ===
    294 Savepoints allow partial rollback inside a transaction without cancelling everything.
     349Savepoints allow partial rollback inside a transaction.
    295350
    296351=== Example ===
    … …  
    310365}}}
    311366
    312 Core bookings remain, optional upgrade is discarded safely.
    313 
    314367== Isolation Levels ==
    315 
    316368=== Overview ===
    317 Isolation controls how concurrent transactions interact.
    318 
    319 Common problems:
     369Isolation defines how concurrent transactions behave.
     370
     371Problems prevented:
    320372
    321373* Dirty reads
    … …  
    324376* Lost updates
    325377
    326 === Levels used in this system ===
    327 
    328 * READ COMMITTED – simple reads (guest lists, catalog)
    329 * REPEATABLE READ – RSVP operations
    330 * SERIALIZABLE – full booking and budget validation
    331 
    332 === SQLAlchemy setting ===
     378=== Levels ===
     379
     380* READ COMMITTED – basic reads
     381* REPEATABLE READ – RSVP consistency
     382* SERIALIZABLE – full booking + budget validation
     383
     384=== SQLAlchemy ===
    333385{{{
    334386db.session.execute(text('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'))
    … …  
    336388
    337389== Connection Pooling ==
    338 
    339 === Why pooling is needed ===
    340 Without pooling:
    341 
    342 * each request opens a new DB connection
    343 * high overhead and slow response
    344 
    345 With pooling:
    346 
    347 * connections are reused
    348 * better scalability and stability
    349 
    350 === Key endpoints affected ===
    351 
    352 * /availability/*
    353 * /rsvp
    354 * /weddings/<id>/book-all
    355 * /guests
    356 
    357 === Pool configuration ===
     390=== Why needed ===
     391Pooling reuses DB connections instead of creating new ones per request.
     392
     393=== Affected endpoints ===
     394
     395* availability endpoints
     396* RSVP endpoints
     397* booking endpoints
     398
     399=== Configuration ===
    358400{{{
    359401engine = create_engine(
    … …  
    367409}}}
    368410
    369 === Parameter meaning ===
     411=== Parameters ===
    370412
    371413* pool_size – base connections
    372414* max_overflow – burst capacity
    373 * pool_timeout – wait time for connection
    374 * pool_recycle – avoid stale DB connections
    375 * pool_pre_ping – detect dead connections
     415* pool_timeout – wait limit
     416* pool_recycle – prevents stale connections
     417* pool_pre_ping – checks connection health
    376418
    377419== Conclusion ==
    378 This phase introduced advanced database reliability and scalability concepts applied directly to the Wedding Planner system.
    379 
    380 Key outcomes:
    381 
    382 * Transactions ensure atomic multi-table operations
    383 * Concurrency control prevents race conditions and inconsistencies
    384 * Isolation levels protect against anomalies
    385 * Savepoints allow partial rollback for optional features
    386 * Connection pooling improves performance under load
    387 
    388 These mechanisms are essential for production-grade backend systems handling concurrent users and complex relational workflows.
     420This phase introduced five full, schema-specific transactions for the Wedding Planner system:
     421
     422* Full wedding booking with atomic service allocation
     423* Guest registration with RSVP seeding
     424* RSVP acceptance with attendance UPSERT
     425* Wedding cancellation with full cascade
     426* Budget validation with row locking
     427
     428These ensure ACID compliance, prevent race conditions, and guarantee consistent multi-table operations in a concurrent environment.