title: Sqlite C API in multiprocess
body: <p>I'm testing Sqlite C Api for multiprocess concurrency and I'm hitting a bug that I don't understand.</p>
  
  <p>The test is simple: 1 database, 1 table of 1000 rows with 2 columns. Column 1 are uuids, column 2 is a status set to 0. I'm launching then 2 workers that concurrently select 2 uuids at status 0 and update them to 1 until no uuids are left to be updated.</p>
  
  <p>Here the code in the main loop of the worker:</p>
  
  <pre><code>std::vector&lt;std::string&gt; uuids;
  res = sqlite3_exec(database,
      "BEGIN IMMEDIATE TRANSACTION;", nullptr, nullptr, nullptr);
  if (res != SQLITE_OK)
  {
      Log("Worker: Failed to begin transaction, wait for other worker to finish");
      std::this_thread::sleep_for(std::chrono::milliseconds(5));
      continue;
  }
  Log("Worker: Begin Transaction");
  
  // SELECT UUIDS
  std::string selectString = "SELECT " + m_uuidStr + " FROM " + m_tableStr + " WHERE " + m_statusStr + "=" + std::to_string(0) + " LIMIT " + std::to_string(2);
  // Log("Worker: SelectString = " + selectString);
  auto selectStatement = selectString.c_str();
  
  res = sqlite3_exec(database,
      selectStatement,
      fill_uuids,
      &amp;uuids, nullptr);
  if (res != SQLITE_OK)
  {
      Log("Worker: Failed to select uuids");
      return 3;
  }
  
  if (uuids.empty())
  {
      sqlite3_exec(database, "ROLLBACK;", nullptr, nullptr, nullptr);
      Log("Worker: nothing more to update");
      break;
  }
  
  // UPDATE UUIDS
  std::stringstream ss;
  ss &lt;&lt; '\"' &lt;&lt; uuids[0] &lt;&lt; '\"';
  for (int i = 1; i &lt; uuids.size(); ++i)
      ss &lt;&lt; ", \"" &lt;&lt; uuids[i] &lt;&lt; '\"';
  Log("Worker: UUIDs selected = " + ss.str());
  
  std::string updateString = "UPDATE " + m_tableStr
      + " SET " + m_statusStr + "=" + std::to_string(1)
      + " WHERE (" + m_uuidStr + " in (" + ss.str() + ")) AND (" + m_statusStr + "=" + std::to_string(0) + ");";
  auto updateStatement = updateString.c_str();
  
  // BIG CRASH HERE WITH IMMEDIATE TRANSACTION ///
  res = sqlite3_exec(database,
      updateStatement,
      nullptr, nullptr, nullptr);
  
  if (res != SQLITE_OK)
  {
      Log("Worker: Failed to update uuids, err = " + std::string(err));
      return 4;
  }
  
  // COMMIT CHANGES
  res = sqlite3_exec(database,
      "COMMIT;", nullptr, nullptr, nullptr);
  if (res != SQLITE_OK)
  {
      Log("Worker: Failed to commit, err = " + std::string(err));
      return 5;
  }
  
  Log("Worker: Transaction ended");
  </code></pre>
  
  <p>When using immediate transactions, one of the worker crashes with a negative error code (-1073741819 but I doubt it helps) when executing the UPDATE statement. When using exclusive transactions, I don't have any crash. The version with immediate transactions works with a unique worker. The database is open using the flags <code>SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX</code>, Sqlite is compiled with option <code>SQLITE_THREADSAFE=1</code>.</p>
  
  <p>There are no other process accessing the database while the workers are running. In that respect I don't understand why immediate transactions causes a crash and exclusive transactions do not, as the behavior should be similar (since no thread/process is creating reading statement during the worker execution).</p>
  
  <p>I'm very new to Sqlite so maybe there's an obvious mistake lying in my code. Any help is appreciated.</p>