[sqlite] Is it possible to update the same sqlite table using two different Linux pthread threads?

[Frank Chang]

   Good morning, If we use the latest version of sqlite, is it possible to
update the same sqlite table using two different Centos/RedHat Linux pthread
threads? We are using the same database connection on both pthreads but we
always update different rows on each of the two threads. We have run
some tests using the following code and occasionally  sqlite3_prepare and
sqlite2_step returns error code 21. Here is the thread function which each
thread runs. Below it, we show the main thread which creates and manages the
worker threads. Thank you.

#if defined(__unix)
  void cIntersectingGroupCache::BruteForceDistillation(void* param){
     char UpdateStatement[256];
     char WhereCondition[256];
     int bMatch1;
     int ReturnValue;
    int status;
    char* blob2;
    int ii,j;
   THREADDATA* td;

   td = (THREADDATA*)(param);

 
ReturnValue=sqlite3_prepare(td->This->Database,"BEGIN",-1,&(td->This->Statement),0);
   status =  sqlite3_step(td->This->Statement);
   status = sqlite3_finalize(td->This->Statement);
   for (ii = td->Begin; ii <= td->End; ii++){
     for (j = ii+1; j <= td->TableSize; j++){
       if (td->DistillationType == pFuzzyCompare)
         switch (td->Fuzzy ) {
           case mfAccurateNear:
               bMatch1=td->This->AccurateNearCompare(td->FieldNameBlock +
(ii - 1)* td->ComponentSize,
                 td->FieldNameBlock + (j - 1)*
td->ComponentSize,td->ComponentSize,
                 td->Near);
           break;
     default:
          bMatch1=(memcmp(td->FieldNameBlock + (ii - 1)* td->ComponentSize,
            td->FieldNameBlock + (j - 1)*
td->ComponentSize,td->ComponentSize) == 0);
         break;
    }
    if (bMatch1){
       sprintf(UpdateStatement,
         "update %s set [Vertices] = CombineBlob(Vertices,%d,(select
Vertices from %s where rowid = %d)) ",td->TableName,
          td->This,td->TableName,td->RowNumberBlock[j-1]);
       sprintf(WhereCondition," where rowid = %d ",
td->RowNumberBlock[ii-1]);
       strcat(UpdateStatement,WhereCondition);

      td->This->m_cs.Lock(); // critical section

ReturnValue=sqlite3_prepare(td->This->Database,UpdateStatement,-1,&(td->This->Statement),0);
      status =  sqlite3_step(td->This->Statement);
      status = sqlite3_finalize(td->This->Statement);
      sprintf(UpdateStatement,
         "update %s set [Vertices] = ?",td->TableName);
      sprintf(WhereCondition," where rowid = %d ", td->RowNumberBlock[j-1]);
      strcat(UpdateStatement,WhereCondition);
      
ReturnValue=sqlite3_prepare(td->This->Database,UpdateStatement,-1,&(td->This->Statement),0);
      int image[] = {0};
      blob2 = reinterpret_cast<char*>(image);
      status = sqlite3_bind_blob(td->This->Statement, 1, blob2, 0,NULL );
      status =  sqlite3_step(td->This->Statement);
       status = sqlite3_finalize(td->This->Statement);
       td->This->m_cs.UnLock();
       td->RowNumberBlock[j - 1] = td->RowNumberBlock[ii - 1];
    }
  }
 }
 
ReturnValue=sqlite3_prepare(td->This->Database,"COMMIT",-1,&(td->This->Statement),0);
 status =  sqlite3_step(td->This->Statement);
 status = sqlite3_finalize(td->This->Statement);
 pthread_t current = pthread_self();
 int i;
 for (i = 0; i < td->This->TotalThreads; i++) {
    if (pthread_equal(td->This->ThreadList[i], current)) {
      // signal condition variables when thread function
BruteForceDistillation
      // is finished
      pthread_mutex_lock(&(td->This->mMutex));
      td->This->ThreadList[i] = 0; // thread function is finished
      pthread_mutex_unlock(&(td->This->mMutex));
      pthread_cond_signal(&(td->This->mConditionVariable));
      return;
   }
 }
}
#endif

// main thread which creates threads and manages worker threads

#if defined(__unix)
   mCritSect.Lock();
   ConstructThread(&ThreadList[0], (void *(*)(void
*))&BruteForceDistillation, DataOfThread1);
   ConstructThread(&ThreadList[1], (void *(*)(void
*))&BruteForceDistillation, DataOfThread2);
   mCritSect.UnLock();

   mCritSect.Lock();
   pthread_mutex_lock(&mMutex);
   while (true) {
     // check if ThreadList array has unused element
     for (nn = 0; nn < TotalThreads; nn++) {
       if (ThreadList[nn] == 0) {
           pthread_mutex_unlock(&mMutex);
           mCritSect.UnLock();
          continue;
        }
     }
     if (ThreadList[0] == 0 && ThreadList[1] == 0)
        break;

     // wait on mConditionVariable for ThreadList array element to become
available
      pthread_cond_wait(&mConditionVariable, &mMutex);
   }
#endif
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users