title: sqlite database connection/locking question
body: <p>Folks
  I am implementing a file based queue (<a href="https://stackoverflow.com/questions/3154034/implementing-a-file-based-queue">see my earlier question</a>) using sqlite. I have the following threads running in background:</p>
  
  <ol>
  <li>thread-1 to empty out a memory structure into the "queue" table (an insert into "queue" table).</li>
  <li>thread-1 to read and "process" the "queue" table runs every 5 to 10 seconds</li>
  <li>thread-3 - runs very infrequently and purges old data that is no longer needed from the "queue" table and also runs vacuum so the size of the database file remains small.</li>
  </ol>
  
  <p>Now the behavior that I would like is for each thread to get whatever lock it needs (waiting with a timeout if possible) and then completing the transaction. It is ok if threads do not run concurrently - what is important is that the transaction once begins does not fail due to "locking" errors such as "database is locked".</p>
  
  <p>I looked at the <a href="http://www.sqlite.org/lang_transaction.html" rel="nofollow noreferrer">transaction documentation</a> but there does not seem to be a "timeout" facility (I am using JDBC). Can the timeout be set to a large quantity in the connection?</p>
  
  <p>One solution (untried) I can think of is to have a connection pool of max 1 connection. Thus only one thread can connect at a time and so we should not see any locking errors. Are there better ways?</p>
  
  <p>Thanx!</p>