Changeset 15287

Timestamp:

Oct 11, 2007, 1:47:33 PM (19 years ago)

Author:

jhoblitt

Message:

resolve database deadlocks caused by subselcts on the table being inserted into

File:

• trunk/ippTools/src/pzgetimfiles.c (modified) (5 diffs)

trunk/ippTools/src/pzgetimfiles.c

@@ -261 +261 @@
     psFree(newImfiles);
 
-    // queue the imfiles (files) in pzPendingImfile -- must be done before the
-    // imfile is insterted into summitImfile (after that happens when don't
-    // know which imfiles are new anymore (well thats not exactly true but it
-    // make be someday if we have to beable to resync to modified datastore
-    // entries)
-    {
-        char *query = 
-            "INSERT INTO pzPendingImfile" 
+    // queue the imfiles (files) dest. for pzPendingImfile in pending -- must
+    // be done before the imfile is insterted into summitImfile (after that
+    // happens when don't know which imfiles are new anymore (well thats not
+    // exactly true but it may be someday if we have to beable to resync to
+    // modified datastore entries)
+    {
+        char *query = 
+            "INSERT IGNORE INTO pzPendingImfile" 
             "   SElECT"
             "       incoming.exp_name,"
@@ -275 +275 @@
             "       incoming.class,"
             "       incoming.class_id"
-            "   FROM incoming"
-            "   JOIN pzPendingExp"
-            "       USING(exp_name, camera, telescope)"
-            "   LEFT JOIN summitImfile"
-            "       USING(exp_name, camera, telescope, class, class_id)"
-            "   WHERE"
-            "       summitImfile.exp_name is NULL"
-            "       AND summitImfile.camera is NULL"
-            "       AND summitImfile.telescope is NULL"
-            "       AND summitImfile.class is NULL"
-            "       AND summitImfile.class_id is NULL";
+            "   FROM incoming";
 
         if (!p_psDBRunQuery(config->dbh, query)) {
@@ -297 +287 @@
     }
 
-    if (!p_psDBRunQuery(config->dbh, "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED")) {
-        // rollback
-        if (!psDBRollback(config->dbh)) {
-            psError(PS_ERR_UNKNOWN, false, "database error");
-        }
-        psError(PS_ERR_UNKNOWN, false, "database error");
-        return false;
-    }
-
     // copy imfiles (files) from the temp table into summitImfiles
     {
         char *query = 
-            "INSERT INTO summitImfile" 
+            "INSERT IGNORE INTO summitImfile" 
             "   SElECT"
             "       incoming.exp_name,"
@@ -320 +301 @@
             "       incoming.class_id,"
             "       incoming.uri"
-            "   FROM incoming"
-            "   LEFT JOIN summitImfile"
-            "       USING(exp_name, camera, telescope, class, class_id)"
-            "   WHERE"
-            "       summitImfile.exp_name is NULL"
-            "       AND summitImfile.camera is NULL"
-            "       AND summitImfile.telescope is NULL"
-            "       AND summitImfile.class is NULL"
-            "       AND summitImfile.class_id is NULL";
-
+            "   FROM incoming";
         if (!p_psDBRunQuery(config->dbh, query)) {
             // rollback
@@ -353 +325 @@
             "   exp_name = '%s'"
             "   AND camera = '%s'"
-            "   AND telescope = '%s'";
+            "   AND telescope = '%s'"
+            "   AND imfiles IS NULL";
         if (!p_psDBRunQuery(config->dbh, query, filesetid, camera, telescope, filesetid, camera, telescope)) {
             // rollback