title: Poor performance from SQLite, big writes bring little reads to a crawl
body: <p><strong>Related question</strong>: <a href="https://stackoverflow.com/questions/1680249/how-to-use-sqlite-in-a-multi-threaded-application">How to use SQLite in a multi-threaded application.</a></p>
  
  <p>I've been trying to get decent performance out of SQLite3 in a multi-threaded program. I've been very impressed with its performance except for write latency. That's not it's fault, it has to wait for the disk to spin to commit the data. But having reads blocked during those writes, even if they could read from cache, is pretty intolerable.</p>
  
  <p>My use case involves a large number of small read operations to get one tiny object by an indexed field, but latency is important for these operations because there are a lot of them. Writes are large and are accumulated into a single transaction. I don't want reads to have huge latency due to completing writes.</p>
  
  <p>I first just used a single connection with a mutex to protect it. However, while the writing thread is waiting for the transaction to complete, readers are blocked on disk I/O because they can't acquire the mutex until the writer releases it. I tried using multiple connections, but then I get <code>SQLITE_LOCKED</code> from <code>sqlite3_step</code>, which means having to redesign all the reading code.</p>
  
  <p>My write logic currently looks like this:</p>
  
  <ol>
  <li>Acquire connection mutex.</li>
  <li><code>START TRANSACTION</code></li>
  <li>Do all writes. (Typically 10 to 100 small ones.)</li>
  <li><code>END TRANSACTION</code> -- here's where it blocks</li>
  <li>Release mutex.</li>
  </ol>
  
  <p>Is there some solution I'm not aware of? Is there an easy way to keep my readers from having to wait for the disk to finish rotating if the entry is in cache without having to rewrite all my reading code to handle <code>SQLITE_LOCKED</code>, reset, and retry?</p>