"lack of SQLite concurrency" - SQLite has plenty of concurrency for reading see the docs: SQLite Threading Modes. I would prototype your testing app with the default Serialization mode and see what performance results.

I ran a short benchmark to see how fast the open/close function is. On my > 10-year-old machine, more than 2,000 per second. Your machine is probably much faster than mine.

You should be aware that a prepare operation can be slow. If you are stashing DBhandles, then for performance reasons, you might need to stash the prepared SQL queries for at least common situations. Note prepare is table specific.

I was shocked at this spin-lock idea. That is almost certainly not what you should do. Here is a description from Intel spin-locks harmful. In the first instance don't worry about this and let SQLite serialize things and see how fast it is. Then probably for multi-thread in your scheme, you need flock mechanism.

How big is your DB? You should be aware that it is possible to dynamically manage the amount of memory that SQLite uses. I have tested this and actually used it in one of my applications. I was creating a heavily indexed DB. I inserted all the rows, then ran RAM usage up and did the indexing en masse for a huge performance gain. If all or most of the DB is memory resident, this cuts down on disk access. You could experiment with different sizes and let SQLite decide what to put into memory and when. Of course, once your app becomes more memory and CPU-bound, it won't make much difference to have more threads than you have physical cores.

My advice is to do some benchmarking and then let that guide the design decisions. 

use strict;
use warnings;
use Time::HiRes qw( gettimeofday );

use DBI;

my $dbfile = "testDB.sqlite";

my $start = gettimeofday;

for (0..10000){
   my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","",{RaiseErro
+r => 1})
       or die "Couldn't connect to database: " . DBI->errstr;
  $dbh->disconnect; 
} 

my $end=gettimeofday;

print "",($end-$start),"\n"; #4.54577684402466
                             # so approximately 2,000 per second

[download]

In reply to Re^3: threads::shared referencing confusion by Marshall
in thread threads::shared referencing confusion by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.