title: Threaded File Handling and database access using MySQL/C API
body: <p>I have a pthread function that has this code,</p>
  
  <p>Each thread accesses 1000 rows (different for each thread) from the database (each thread has its a pointer <code>MYSQL_RES</code> type,declared as local variable in the thread function which points to the whole result set),seeks to the desired row and does <code>mysql_fetch_row</code> for 1000 rows (there is a defined subset of rows each thread has to acquire) or until it returns <code>NULL</code>(whichever comes first)). </p>
  
  <p>List of variables. (global)</p>
  
  <pre><code>MYSQL* connect  = NULL;
  MYSQL_RES* res = NULL;
  std::fstream f;
  pthread_mutex_t dBMutex = PTHREAD_MUTEX_INITIALIZER;
  pthread_mutex_t fMutex = PTHREAD_MUTEX_INITIALIZER;
  </code></pre>
  
  <p>List of variables local to the thread function,</p>
  
  <pre><code>MYSQL_ROW row = NULL;
  MYSQL_RES* res = ::res; // local pointer to global result set 
  int count = eachThread; // where #define eachThread 1000
  char c[50];
  </code></pre>
  
  <p>Portion of code in thread function,</p>
  
  <pre><code>pthread_mutex_lock(&amp;dBMutex);
  mysql_data_seek(res,((arg)));
  
  while(count &amp;&amp; ((row = mysql_fetch_row(res))!=NULL))
  {
  strcpy(c,row[0]);
  //pthread_mutex_unlock(&amp;dBMutex);  ///--------------------&gt;
  
  pthread_mutex_lock(&amp;fMutex); 
      f&lt;&lt;c&lt;&lt;std::endl;
  pthread_mutex_unlock(&amp;fMutex); 
  
  count--;
  //pthread_mutex_lock(&amp;dBMutex); ///--------------------&gt;
  }
  pthread_mutex_unlock(&amp;dBMutex); 
  </code></pre>
  
  <p>If I comment the two unlock (marked by <code>///--------------------&gt;</code>) and the corressponding lock statements, (keep the entire execution of <code>while</code> loop in lock) the code works fine. (The database has 11112 lines and it writes 11112 lines in the file). </p>
  
  <p>If I don't i.e I make the <code>mysql_row_fetch</code> unlock the lock, allowing other threads to acquire it. There are more number of lines written in the file (goes upto 12000 ). </p>
  
  <p>Why is it so? The <code>while</code> loop is controlled using local variables, so there is no race possible.  How can the no. of lines in the file be more than the number of times the loop can possibly execute? (I am using a mutex on the file so only one thread can access it. If it can't it blocks on the mutex)</p>
  
  <p>Alternatively, if I wrap the file access code in mutex <code>dBMutex</code> the code works fine. </p>
  
  <pre><code>pthread_mutex_lock(&amp;dBMutex); 
  //pthread_mutex_lock(&amp;fMutex); 
      f&lt;&lt;c&lt;&lt;std::endl;
  //pthread_mutex_unlock(&amp;fMutex); 
  pthread_mutex_unlock(&amp;dBMutex); 
  </code></pre>