623b40a3d4c25c552af92d88aed99c5427446f36
hiram
  Mon Jun 1 12:12:37 2026 -0700
avoid race condition using INSERT WHERE NOT EXISTS refs #31811

diff --git src/hg/hubApi/liftOver.c src/hg/hubApi/liftOver.c
index b0968de83ca..274b64ede17 100644
--- src/hg/hubApi/liftOver.c
+++ src/hg/hubApi/liftOver.c
@@ -63,31 +63,31 @@
     jsonWriteString(jw, NULL, position);
     jsonWriteString(jw, NULL, coverage);
     jsonWriteListEnd(jw);
     }
 jsonWriteListEnd(jw);
 apiFinishOutput(0, NULL, jw);
 }
 
 static void listExisting()
 /* output the fromDb,toDb from liftOverChain.hgcentral SQL table */
 {
 char *filter = cgiOptionalString(argFilter);
 char *fromDb = cgiOptionalString(argFromGenome);
 char *toDb = cgiOptionalString(argToGenome);
 
-struct sqlConnection *conn = hConnectCentral();
+struct sqlConnection *conn = hConnectOtto();
 char *tableName = cloneString(liftOverChainTable());
 struct dyString *query = newDyString(0);
 sqlDyStringPrintf(query, "SELECT count(*) FROM %s", tableName);
 long long totalRows = sqlQuickLongLong(conn, dyStringContents(query));
 dyStringClear(query);
 
 if (isNotEmpty(fromDb) && isNotEmpty(toDb))
     {
     /* match a chain recorded in either direction */
     sqlDyStringPrintf(query, "SELECT * FROM %s WHERE "
         "(LOWER(fromDb) = LOWER('%s') AND LOWER(toDb) = LOWER('%s')) "
         "OR (LOWER(fromDb) = LOWER('%s') AND LOWER(toDb) = LOWER('%s'))",
         tableName, fromDb, toDb, toDb, fromDb);
     }
 else if (isNotEmpty(fromDb) || isNotEmpty(toDb))
@@ -153,58 +153,58 @@
             "ORDER BY requestTime DESC LIMIT 1",
             ottoTable, fromDb, toDb, toDb, fromDb);
         char **row;
         struct sqlResult *sr = sqlGetResult(conn, dyStringCannibalize(&pq));
         if ((row = sqlNextRow(sr)) != NULL)
             {
             jsonWriteBoolean(jw, "pending", TRUE);
             jsonWriteNumber(jw, "pendingStatus", sqlSigned(row[1]));
             jsonWriteString(jw, "pendingRequestTime", row[2]);
             }
         sqlFreeResult(&sr);
         }
     }
 
 apiFinishOutput(0, NULL, jw);
-hDisconnectCentral(&conn);
+hDisconnectOtto(&conn);
 }
 
 static void loginStatus()
 /* output current user login status as JSON */
 {
 char *userName = (loginSystemEnabled() || wikiLinkEnabled()) ? wikiLinkUserName() : NULL;
 struct jsonWrite *jw = apiStartOutput();
 char hgLoginLink[2048];
 safef(hgLoginLink, sizeof(hgLoginLink), "%shgLogin", hLoginHostCgiBinUrl());
 
 if (userName != NULL)
     {
     // Get both email and realName from gbMembers table
-    struct sqlConnection *sc = hConnectCentral();
+    struct sqlConnection *sc = hConnectOtto();
     struct dyString *query = sqlDyStringCreate("select email, realName from gbMembers where userName = '%s'", userName);
     struct sqlResult *sr = sqlGetResult(sc, dyStringCannibalize(&query));
     char **row = sqlNextRow(sr);
 
     char *email = NULL;
     char *realName = NULL;
     if (row != NULL)
         {
         email = cloneString(row[0] ? row[0] : "");
         realName = cloneString(row[1] ? row[1] : "");
         }
     sqlFreeResult(&sr);
-    hDisconnectCentral(&sc);
+    hDisconnectOtto(&sc);
 
     // Build logout URL with returnto parameter
     char *returnTo = cgiOptionalString("returnTo");
     struct dyString *logoutUrl = dyStringNew(0);
     dyStringPrintf(logoutUrl, "%s?hgLogin.do.displayLogout=1", hgLoginLink);
     if (isNotEmpty(returnTo))
         {
         char *encodedReturnUrl = cgiEncodeFull(returnTo);
 
         dyStringPrintf(logoutUrl, "&returnto=%s", encodedReturnUrl);
         freeMem(encodedReturnUrl);
         }
 
     jsonWriteString(jw, "userName", userName);
     jsonWriteString(jw, "email", email ? email : "");
@@ -404,108 +404,144 @@
 char *toGenome = cgiOptionalString(argToGenome);
 char *email = cgiOptionalString(argEmail);
 char *comment = cgiOptionalString(argComment);
 
 /* probably want a silent exit here */
 if (isEmpty(fromGenome) || isEmpty(toGenome) || isEmpty(email) || isEmpty(comment))
     apiErrAbort(err400, err400Msg, "must have all arguments: %s, %s, %s, %s for endpoint '/liftRequest", argFromGenome, argToGenome, argEmail, argComment);
 
 /* Require a session cookie.  Robots that have not
  *   passed the challenge will not have one. */
 char *cookieName = hUserCookie();
 char *userId = findCookieData(cookieName);
 if (isEmpty(userId))
     apiErrAbort(err400, err400Msg, "can not find required inputs for endpoint '/liftRequest");
 
+/* verify (again) that the requested assemblies actually exist */
+struct dbDb *fromDb = hDbDb(fromGenome);
+if (fromDb == NULL)
+    {
+    fromDb = genarkLiftOverDb(fromGenome);
+    }
+struct dbDb *toDb = hDbDb(toGenome);
+if (toDb == NULL)
+    {
+    toDb = genarkLiftOverDb(toGenome);
+    }
+if ( (fromDb == NULL) || (fromDb == NULL) )
+    {
+    if ( (fromDb == NULL) && (toDb == NULL) )
+	    apiErrAbort(err400, err400Msg, "can not find either 'fromGenome=%s' or 'toGenome=%s' for endpoint '/liftOver", fromGenome, toGenome);
+        else
+	    apiErrAbort(err400, err400Msg, "can not find 'fromoGenome=%s' for endpoint '/liftOver", fromGenome);
+    if (toDb == NULL)
+        apiErrAbort(err400, err400Msg, "can not find 'toGenome=%s' for endpoint '/liftOver", toGenome);
+    }
+
 /* duplicate-row guard: any existing row in ottoRequest for this pair
  * (either direction, any status) blocks resubmission.  The form's JS
  * already shows a "pending" panel for this case via the listExisting
  * endpoint; this is the backstop for clients that bypass the form. */
 {
 char *dupOttoTable = cfgOption("ottoTable");
 if (isNotEmpty(dupOttoTable))
     {
-    struct sqlConnection *conn = hConnectCentral();
+    struct sqlConnection *conn = hConnectOtto();
     if (sqlTableExists(conn, dupOttoTable))
         {
         struct dyString *dq = newDyString(0);
         sqlDyStringPrintf(dq,
             "SELECT COUNT(*) FROM %s WHERE requestType='liftOver' AND "
             "((fromDb='%s' AND toDb='%s') OR (fromDb='%s' AND toDb='%s'))",
             dupOttoTable, fromGenome, toGenome, toGenome, fromGenome);
         int dupCount = sqlQuickNum(conn, dyStringCannibalize(&dq));
-        hDisconnectCentral(&conn);
+        hDisconnectOtto(&conn);
         if (dupCount > 0)
             apiErrAbort(err409, err409Msg,
                 "A request for %s <-> %s has already been submitted "
                 "and is on record.  Duplicates are not accepted.",
                 fromGenome, toGenome);
         }
     else
-        hDisconnectCentral(&conn);
+        hDisconnectOtto(&conn);
     }
 }
 
 /* per-email daily rate limit, per requestType, calendar-day server time */
 char *limitStr = cfgOption("liftDailyLimit");
 int dailyLimit = isNotEmpty(limitStr) ? atoi(limitStr) : 0;
 if (dailyLimit > 0)
     {
     char *limitOttoTable = cfgOption("ottoTable");
     if (isNotEmpty(limitOttoTable))
         {
-        struct sqlConnection *conn = hConnectCentral();
+        struct sqlConnection *conn = hConnectOtto();
         if (sqlTableExists(conn, limitOttoTable))
             {
             struct dyString *q = newDyString(0);
             sqlDyStringPrintf(q,
                 "SELECT COUNT(*) FROM %s "
                 "WHERE requestType='liftOver' AND email='%s' "
                 "AND DATE(requestTime) = CURDATE()",
                 limitOttoTable, email);
             int todayCount = sqlQuickNum(conn, dyStringCannibalize(&q));
-            hDisconnectCentral(&conn);
+            hDisconnectOtto(&conn);
             if (todayCount >= dailyLimit)
                 apiErrAbort(err429, err429Msg,
                     "Daily limit reached: %d liftOver requests per day. "
                     " Please try again tomorrow.",
                     dailyLimit);
             }
         else
-            hDisconnectCentral(&conn);
+            hDisconnectOtto(&conn);
         }
     }
 
 char *toAddr = cfgOption("chainFileRequestEmail");
 char *fromAddr = cfgOption("apiFromEmail");
 
 if (isNotEmpty(toAddr) && isNotEmpty(fromAddr))
     {
     char nowTime[256];
     time_t seconds = clock1();
     struct tm *timeNow = localtime(&seconds);
     strftime(nowTime, sizeof nowTime, "%Y-%m-%d %H:%M:%S", timeNow);
 
     struct dyString *msg = newDyString(0);
     /* may need to encode these inputs to make them safe */
     dyStringPrintf(msg, "%s\nLift over request\nfrom: %s\nto: %s\nemail '%s'\ncomment: '%s'", nowTime, fromGenome, toGenome, email, comment);
 
     /* some kind of response here back to the request page */
     struct jsonWrite *jw = apiStartOutput();
     jsonWriteString(jw, "msg", dyStringCannibalize(&msg));
     apiFinishOutput(0,NULL,jw);
     char *ottoTable = cfgOption("ottoTable");	/* probably ottoRequest */
     if (isNotEmpty(ottoTable))
         {
-        struct sqlConnection *conn = hConnectCentral();
+        struct sqlConnection *conn = hConnectOtto();
         if (sqlTableExists(conn, ottoTable))
 	    {
+            /* Atomic insert with duplicate check - prevents race condition */
             struct dyString *update = newDyString(0);
             sqlDyStringPrintf(update,
-		"INSERT INTO %s (requestType, fromDb, toDb, email, comment, requestTime, status, buildDir) VALUES ( 'liftOver', '%s','%s','%s','%s',now(), 0, '')",
-		ottoTable,  fromGenome, toGenome, email, comment);
-            sqlUpdate(conn, dyStringCannibalize(&update));
+                "INSERT INTO %s (requestType, fromDb, toDb, email, comment, requestTime, status, buildDir) "
+                "SELECT 'liftOver', '%s', '%s', '%s', '%s', now(), 0, '' "
+                "WHERE NOT EXISTS ("
+                "  SELECT 1 FROM %s WHERE requestType='liftOver' AND "
+                "  ((fromDb='%s' AND toDb='%s') OR (fromDb='%s' AND toDb='%s'))"
+                ")",
+                ottoTable, fromGenome, toGenome, email, comment,
+                ottoTable, fromGenome, toGenome, toGenome, fromGenome);
+            int rowsAffected = sqlUpdateRows(conn, dyStringCannibalize(&update), NULL);
+            if (rowsAffected == 0)
+                {
+                hDisconnectOtto(&conn);
+                apiErrAbort(err409, err409Msg,
+                    "A request for %s <-> %s has already been submitted "
+                    "and is on record.  Duplicates are not accepted.",
+                    fromGenome, toGenome);
+                }
 	    }
-        hDisconnectCentral(&conn);
+        hDisconnectOtto(&conn);
         }
     }
 }	/*	void apiLiftRequest(char *words[MAX_PATH_INFO])	*/