"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]