Changeset 002cf5f

Timestamp:

09/16/26 22:59:59 (7 days ago)

Author:

Boris Gjorgjievski <boris@…>

Branches:

master

Children:

0496e6e

Parents:

1b20b22

Message:

Phase 8: connection pooling and concurrent-enrolment handling

Two enrolments for the same semester sent at once both pass the
"already enrolled" check, because each transaction reads the state from
before the other one wrote. UNIQUE (user_id, semester_id) is what
settles it, so EnrollAsync now catches the 23505 and returns the same
sentence instead of a 500.

Sizes the connection pool in the connection string (max 10, min 1)
rather than taking Npgsql's default of 100 - every physical connection
is one more channel through the SSH tunnel - and switches to
AddDbContextPool, which AppDbContext qualifies for since its only
constructor takes DbContextOptions.

docs/Ph8.md is the wiki page: the three transactional scenarios, the
isolation level and what it does not cover, and the pool settings with
the measured connection counts.

Files:

• docs/Ph8.md (added)
• iknow-api/Program.cs (modified) (1 diff)
• iknow-api/Services/Implementations/EnrollmentService.cs (modified) (3 diffs)
• iknow-api/appsettings.Development.json.example (modified) (1 diff)

iknow-api/Program.cs

@@ -66 +66 @@
 // Postgres type by name; PgEnumLabels covers the members whose label is not
 // simply the lowercased member name.
-builder.Services.AddDbContext<AppDbContext>(options =>
+// AddDbContextPool reuses the DbContext instances themselves; the connections
+// underneath them are pooled separately by Npgsql, sized in the connection
+// string. Both matter here because every physical connection is one more
+// channel through the SSH tunnel.
+builder.Services.AddDbContextPool<AppDbContext>(options =>
     options.UseNpgsql(
         builder.Configuration.GetConnectionString("DefaultConnection"),

iknow-api/Services/Implementations/EnrollmentService.cs

@@ -3 +3 @@
 using iknow_api.Models;
 using Microsoft.EntityFrameworkCore;
+using Npgsql;
 
 namespace iknow_api.Services
@@ -220 +221 @@
                 };
             }
+            catch (DbUpdateException ex) when (IsUniqueViolation(ex))
+            {
+                // Two enrolments for the same semester sent at the same time both
+                // pass the check above, because each transaction reads the state
+                // from before the other one wrote. UNIQUE (user_id, semester_id)
+                // is what actually settles it, so the loser gets the same
+                // sentence as if the check had caught it.
+                await transaction.RollbackAsync();
+                return Fail("You are already enrolled in that semester.");
+            }
             catch
             {
@@ -226 +237 @@
             }
         }
+
+        /// <summary>23505 is unique_violation.</summary>
+        private static bool IsUniqueViolation(DbUpdateException ex) =>
+            ex.InnerException is PostgresException { SqlState: "23505" };
 
         private static EnrollSemesterResultDto Fail(string message) =>

iknow-api/appsettings.Development.json.example

@@ -7 +7 @@
   },
   "ConnectionStrings": {
-    "DefaultConnection": "Host=localhost;Port=9999;Database=db_202526z_va_prj_know2026;Username=db_202526z_va_prj_iknow2026_owner;Password=YOUR_DB_PASSWORD_HERE;Search Path=project;Include Error Detail=true"
+    "DefaultConnection": "Host=localhost;Port=9999;Database=db_202526z_va_prj_know2026;Username=db_202526z_va_prj_iknow2026_owner;Password=YOUR_DB_PASSWORD_HERE;Search Path=project;Include Error Detail=true;Maximum Pool Size=10;Minimum Pool Size=1;Connection Idle Lifetime=300;Timeout=15"
   },
   "Jwt": {