title: How threadsafe is SQLite3?
body: <p>I have a question regarding Multithreading in SQLite3. In my scenario, I have several processes, which want to write into the same SQLite3 database.</p>
  
  <p>In the <a href="https://www.sqlite.org/threadsafe.html" rel="noreferrer">manual</a>, it says</p>
  
  <blockquote>
    <p>Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads. </p>
  </blockquote>
  
  <p>I have written an example program, where several (say 10) threads INSERT into the same table of the same database using the same database connection.</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS StatusTable (ID INTEGER PRIMARY KEY, Thread TEXT NOT NULL, Module TEXT NOT NULL, Status INTEGER NOT NULL);
  </code></pre>
  
  <p>The INSERT is</p>
  
  <pre><code>sprintf(acBuffer, "INSERT INTO StatusTable(Thread, Module, Status) VALUES('%s', 'testnumber%d', %d);", acThread, i, thisThread);
  </code></pre>
  
  <p>where <code>acThread</code> is a string containing process ID and thread ID, <code>i</code> runs from 1 to 100, and <code>thisThread</code> is the thread ID. The threads do this INSERT 100 times in a while loop and then quit.</p>
  
  <p>According to the above statement, this is not safe although I cannot see any problems. </p>
  
  <ol>
  <li>Using 10 threads with a shared sqlite3 object, each inserting 100 lines: no insertion is lost and no timeout occurs.</li>
  <li>Using 10 threads, each with a separate sqlite3 object, each inserting 100 lines: some insertions do not succeed (about 0.5 to 1%) because the database is locked.</li>
  <li>Using two processes of case 1: the same occurs as in case 2, there are some losses (about 0.1%).</li>
  </ol>
  
  <p>I am using <code>PRAGMA journal_mode=WAL;</code> and <code>PRAGMA busy_timeout=10000;</code>.</p>
  
  <p>Now my questions:</p>
  
  <ul>
  <li>Is it true that I should not share connection objects among threads? What bad things can happen if I do?</li>
  <li>Why does the first case (with shared db connection) work (all inserts succeed, no timeout)? What would I have to do to "break" SQLite? What could happen then?</li>
  <li>I assume that each thread should have its own connection. How can I avoid a fail of insertion? In my example, there are only insertions, no readers. The ridiculously high busy_timeout (10 seconds) does not work as expected, there are still "database is locked" insertion fails. I would not mind waiting 10 seconds per insert as long as the insert is successful.</li>
  </ul>