Changes between Version 3 and Version 4 of P8


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

--

Legend:

Unmodified
Added
Removed
Modified

  • P8

    v3 v4  
    22
    33== Overview ==
    4 In this phase we explain two important database concepts used in real-world applications: database transactions and database connection pooling.
    5 These concepts are essential for ensuring data consistency, correctness and performance when multiple users interact with the system at the same time.
    6 
    7 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.
     4This 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.
     5
     6In a production environment, these concepts ensure data consistency, correctness, and performance when multiple users interact with the system simultaneously.
    87
    98== What we cover in this phase ==
    109In this phase we focus on:
    11  * What a database transaction is and why it is needed
    12  * COMMIT vs ROLLBACK
    13  * Isolation and concurrency problems (dirty reads, lost updates, etc.)
    14  * How transactions apply to our Wedding Planner scenarios (bookings, RSVP, conflicts)
    15  * What connection pooling is and why it improves performance
    16  * How pooling would be used in a real version of our application
    17 
    18 == Transactions in databases ==
    19 
    20 === What is a transaction? ===
    21 A database transaction is a group of operations that must execute as a single unit.
    22 The transaction is successful only if all operations succeed.
    23 
    24 Transactions are needed whenever multiple inserts/updates must happen together, without leaving the database in an inconsistent state.
    25 
    26 === Why are transactions important? (ACID) ===
    27 Transactions ensure:
    28  * Atomicity – all operations succeed or none are applied
    29  * Consistency – data constraints remain valid
    30  * Isolation – concurrent operations do not corrupt data
    31  * Durability – once committed, changes remain stored
    32 
    33 This is commonly summarized as the ACID principles.
    34 
    35 === COMMIT and ROLLBACK ===
    36 A transaction typically works like this:
    37  * BEGIN – start transaction
    38  * Execute multiple SQL operations
    39  * COMMIT – save changes permanently if everything succeeded
    40  * ROLLBACK – undo everything if an error occurs
    41 
    42 Example (SQL pseudocode):
    43 
    44 {{{
    45 BEGIN;
    46 
    47 UPDATE venue_booking SET status='CONFIRMED' WHERE booking_id=10;
    48 UPDATE wedding SET notes='Venue confirmed' WHERE wedding_id=1;
    49 
    50 COMMIT;
    51 }}}
    52 
    53 If an error happens during the process, the correct behavior is:
    54 
    55 {{{
    56 ROLLBACK;
    57 }}}
    58 
    59 === Transaction example connected to our project ===
    60 
    61 ==== Scenario: Venue booking confirmation ====
    62 In the Wedding Planner application, when the user books a venue we must:
    63  * insert the booking record
    64  * update wedding information
    65  * ensure there is no overlapping booking for the same venue
    66 
    67 If one step fails, the database must not store partial data.
    68 
    69 Pseudocode logic:
    70 
    71 {{{
    72 BEGIN TRANSACTION
    73 
    74 Check if venue is free at that time
    75 
    76 Insert venue_booking
    77 
    78 Update wedding record
    79 
    80 COMMIT
    81 If any step fails -> ROLLBACK
    82 }}}
    83 
    84 ==== Conflict detection and why transaction is needed ====
    85 In our prototype we check conflicts using the endpoint:
    86 
    87 {{{
    88 /availability/venue?venue_id=1&date=2026-06-20&start=15:00&end=21:00
    89 }}}
    90 
    91 In a real system, the conflict check MUST be done inside a transaction, because two users could do this at the same time:
    92 
    93  * User A checks availability → free
    94  * User B checks availability → free
    95  * Both insert a booking at the same moment
    96  * Result → double-booking
    97 
    98 Correct approach:
    99  * conflict check + insert booking must execute atomically inside one transaction.
    100 
    101 == Isolation and concurrency (basic overview) ==
    102 
    103 === What is isolation? ===
    104 Isolation defines how multiple users/requests interact with the database at the same time.
    105 If isolation is weak, concurrent transactions may read or write inconsistent values.
    106 
    107 Common concurrency problems:
    108  * Dirty read – reading uncommitted changes
    109  * Non-repeatable read – the same query returns different result inside one transaction
    110  * Phantom read – new rows appear in repeated query
    111  * Lost update – two users overwrite each other’s updates
    112 
    113 Even though SQLite is simplified, production systems must carefully choose the isolation level.
    114 
    115 === Example connected to our project ===
    116 If multiple users RSVP or update attendance at the same time without transaction + isolation:
    117  * Attendance table could end up with duplicates
    118  * RSVP results could be overwritten
    119  * Counts per event may become incorrect
    120 
    121 Therefore, operations such as RSVP update and attendance update should be handled transactionally.
    122 
    123 == Transactions inside Flask prototype ==
    124 
    125 === How transactions would be handled in Flask ===
    126 In Flask applications (SQLAlchemy or DB sessions), the usual pattern is:
    127  * start transaction
    128  * execute queries
    129  * commit
    130  * if error → rollback
    131 
    132 Pseudocode example:
    133 
    134 {{{
     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
     19== Transaction 1 – Full Wedding Bundle Booking ==
     20
     21=== Business scenario ===
     22When a user finalises a wedding plan, all services must be booked atomically:
     23venue, photographer, band, and registrar.
     24
     25If 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 ===
     36Two bookings overlap when:
     37{{{
     38start_time < new_end_time AND end_time > new_start_time
     39}}}
     40
     41=== SQL Transaction ===
     42{{{
     43BEGIN;
     44
     45-- Venue check
     46SELECT 1 FROM project.venue_booking
     47WHERE venue_id = 1
     48AND date = '2026-06-20'
     49AND start_time < '23:00'
     50AND end_time > '16:00'
     51AND status <> 'cancelled';
     52
     53-- Photographer check
     54SELECT 1 FROM project.photographer_booking
     55WHERE photographer_id = 1
     56AND date = '2026-06-20'
     57AND start_time < '22:00'
     58AND end_time > '14:00'
     59AND status <> 'cancelled';
     60
     61-- Band check
     62SELECT 1 FROM project.band_booking
     63WHERE band_id = 1
     64AND date = '2026-06-20'
     65AND start_time < '23:00'
     66AND end_time > '18:00'
     67AND status <> 'cancelled';
     68
     69-- Registrar check
     70SELECT 1 FROM project.registrar_booking
     71WHERE registrar_id = 1
     72AND date = '2026-06-20'
     73AND start_time < '10:30'
     74AND end_time > '10:00'
     75AND status <> 'cancelled';
     76
     77-- Inserts
     78INSERT INTO project.venue_booking (...)
     79VALUES ('2026-06-20','16:00','23:00','confirmed',7000,1,1);
     80
     81INSERT INTO project.photographer_booking (...)
     82VALUES ('2026-06-20','14:00','22:00','confirmed',1,1);
     83
     84INSERT INTO project.band_booking (...)
     85VALUES ('2026-06-20','18:00','23:00','confirmed',1,1);
     86
     87INSERT INTO project.registrar_booking (...)
     88VALUES ('2026-06-20','10:00','10:30','confirmed',1,1);
     89
     90-- Update wedding
     91UPDATE project.wedding
     92SET notes = 'All services confirmed'
     93WHERE wedding_id = 1;
     94
     95COMMIT;
     96}}}
     97
     98=== Flask implementation ===
     99{{{
     100def book_full_wedding(...):
    135101try:
    136     db.session.begin()
    137 
    138     # check availability
    139     # insert booking
    140     # update wedding
    141 
     102db.session.begin()
     103
     104```
     105    _check_overlap(...)
     106    db.session.add(...)
    142107    db.session.commit()
     108
    143109except Exception:
    144110    db.session.rollback()
    145 }}}
    146 
    147 Even though our prototype is minimal and uses SQLite, the concept stays identical and scales directly to PostgreSQL.
    148 
    149 == Database connection pooling ==
    150 
    151 === What is connection pooling? ===
    152 Connection pooling means reusing open database connections instead of creating a new connection for every request.
    153 
     111```
     112
     113}}}
     114
     115== Transaction 2 – Guest Registration with RSVP Seeding ==
     116
     117=== Business scenario ===
     118When 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 ===
     127Without a transaction, a system crash could leave:
     128
     129* guest inserted
     130* but missing RSVP records → inconsistent state
     131
     132=== SQL Transaction ===
     133{{{
     134BEGIN;
     135
     136INSERT INTO project.guest (...)
     137VALUES ('Marija','Ilievska','[marija@gmail.com](mailto:marija@gmail.com)',1)
     138RETURNING guest_id;
     139
     140INSERT INTO project.event_rsvp (...)
     141SELECT 'pending', CURRENT_DATE, :new_guest_id, e.event_id
     142FROM project.event e
     143WHERE e.wedding_id = 1;
     144
     145COMMIT;
     146}}}
     147
     148=== Flask implementation ===
     149{{{
     150db.session.begin()
     151
     152guest = Guest(...)
     153db.session.add(guest)
     154db.session.flush()
     155
     156events = db.session.query(Event).filter_by(wedding_id=wedding_id).all()
     157
     158for event in events:
     159db.session.add(EventRSVP(...))
     160
     161db.session.commit()
     162}}}
     163
     164Key idea: flush() is used to obtain guest_id before commit.
     165
     166== Transaction 3 – RSVP Acceptance and Seat Assignment ==
     167
     168=== Business scenario ===
     169When a guest accepts an RSVP:
     170
     171* RSVP status updates to accepted
     172* Attendance row is created or updated
     173
     174Both must happen together.
     175
     176=== Tables involved ===
     177
     178* event_rsvp (update)
     179* attendance (UPSERT)
     180
     181=== SQL Transaction ===
     182{{{
     183BEGIN;
     184
     185UPDATE project.event_rsvp
     186SET status = 'accepted',
     187response_date = CURRENT_DATE
     188WHERE guest_id = 1 AND event_id = 2;
     189
     190INSERT INTO project.attendance (...)
     191VALUES ('attending', 5, 'Guest', 1, 2)
     192ON CONFLICT (guest_id, event_id)
     193DO UPDATE SET
     194status = EXCLUDED.status,
     195table_number = EXCLUDED.table_number,
     196role = EXCLUDED.role;
     197
     198COMMIT;
     199}}}
     200
     201=== Key concept ===
     202ON CONFLICT prevents race conditions and ensures atomic seat assignment.
     203
     204== Transaction 4 – Wedding Cancellation with Cascade ==
     205
     206=== Business scenario ===
     207Cancelling a wedding must cascade across:
     208
     209* bookings
     210* events
     211* attendance records
     212
     213Partial 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 ===
     226{{{
     227BEGIN;
     228
     229UPDATE project.wedding
     230SET notes = 'Wedding cancelled'
     231WHERE wedding_id = 1;
     232
     233UPDATE project.venue_booking SET status='cancelled' WHERE wedding_id=1;
     234UPDATE project.photographer_booking SET status='cancelled' WHERE wedding_id=1;
     235UPDATE project.band_booking SET status='cancelled' WHERE wedding_id=1;
     236UPDATE project.registrar_booking SET status='cancelled' WHERE wedding_id=1;
     237
     238UPDATE project.event SET status='cancelled' WHERE wedding_id=1;
     239
     240UPDATE project.attendance
     241SET status='absent'
     242WHERE event_id IN (
     243SELECT event_id FROM project.event WHERE wedding_id = 1
     244);
     245
     246COMMIT;
     247}}}
     248
     249=== Concurrency risk ===
     250Without a transaction, new RSVP updates could be inserted during cancellation, causing inconsistent state.
     251
     252== Transaction 5 – Budget Validation with Row Locking ==
     253
     254=== Business scenario ===
     255Before finalising a wedding, ensure total cost does not exceed budget.
     256
     257Must 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 ===
     268{{{
     269BEGIN;
     270
     271SELECT budget FROM project.wedding
     272WHERE wedding_id = 1
     273FOR UPDATE;
     274
     275-- Compute total cost (venue + photographer + band + registrar)
     276
     277IF total_cost > budget THEN
     278ROLLBACK;
     279END IF;
     280
     281UPDATE project.wedding
     282SET notes = 'Budget validated – wedding finalised'
     283WHERE wedding_id = 1;
     284
     285COMMIT;
     286}}}
     287
     288=== Why FOR UPDATE matters ===
     289It prevents TOCTOU (Time-of-check vs Time-of-use) race conditions by locking the wedding row.
     290
     291== Savepoints – Partial Rollback ==
     292
     293=== Concept ===
     294Savepoints allow partial rollback inside a transaction without cancelling everything.
     295
     296=== Example ===
     297{{{
     298BEGIN;
     299
     300INSERT venue_booking;
     301INSERT photographer_booking;
     302
     303SAVEPOINT after_core;
     304
     305INSERT band_booking;
     306
     307ROLLBACK TO SAVEPOINT after_core;
     308
     309COMMIT;
     310}}}
     311
     312Core bookings remain, optional upgrade is discarded safely.
     313
     314== Isolation Levels ==
     315
     316=== Overview ===
     317Isolation controls how concurrent transactions interact.
     318
     319Common problems:
     320
     321* Dirty reads
     322* Non-repeatable reads
     323* Phantom reads
     324* Lost updates
     325
     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 ===
     333{{{
     334db.session.execute(text('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE'))
     335}}}
     336
     337== Connection Pooling ==
     338
     339=== Why pooling is needed ===
    154340Without pooling:
    155  * each HTTP request opens a new DB connection
    156  * connecting is expensive
    157  * under high load, the system becomes slow and unstable
     341
     342* each request opens a new DB connection
     343* high overhead and slow response
    158344
    159345With pooling:
    160  * application keeps a pool of reusable connections
    161  * requests take a free connection from the pool
    162  * after finishing, the connection returns to the pool
    163 
    164 === Why pooling improves performance ===
    165 Pooling provides:
    166  * faster response time (no repeated connection overhead)
    167  * controlled DB resource usage
    168  * scalability for many concurrent users
    169 
    170 Example:
    171  * 100 users → 100 requests
    172  * without pooling → 100 separate new connections
    173  * with pooling → e.g. 10 connections reused and shared
    174 
    175 === Pooling in our Wedding Planner system ===
    176 In a production version of our project, pooling would be important for endpoints that can be called frequently:
    177  * /weddings
    178  * /weddings/<id>/guests
    179  * RSVP update operations
    180  * availability checks
    181 
    182 Availability checks can be called many times, so pooling prevents performance degradation.
    183 
    184 === Example pooling config (conceptual) ===
    185 In SQLAlchemy a pool configuration could look like:
    186 
     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 ===
    187358{{{
    188359engine = create_engine(
    189     DB_URL,
    190     pool_size=10,
    191     max_overflow=20
     360'postgresql://user:pass@localhost/weddingdb',
     361pool_size=10,
     362max_overflow=20,
     363pool_timeout=30,
     364pool_recycle=1800,
     365pool_pre_ping=True
    192366)
    193367}}}
    194368
     369=== Parameter meaning ===
     370
     371* pool_size – base connections
     372* 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
     376
    195377== Conclusion ==
    196 This phase introduced two key concepts for advanced backend applications: database transactions and database connection pooling.
    197 
    198 Transactions ensure correctness and consistency when multiple operations must succeed together (for example: booking + conflict check, RSVP updates, attendance updates).
    199 COMMIT and ROLLBACK guarantee the database is not left in a partial or inconsistent state.
    200 
    201 Connection pooling improves scalability and performance by reusing database connections instead of creating a new one per request.
    202 These principles apply directly to our Wedding Planner system, especially for booking, RSVP, attendance management and venue availability conflict detection.
     378This phase introduced advanced database reliability and scalability concepts applied directly to the Wedding Planner system.
     379
     380Key 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
     388These mechanisms are essential for production-grade backend systems handling concurrent users and complex relational workflows.