If I understand ikegami correctly, you can only use a DB handle in the thread that created it. Regardless of any concurrent operations.

I didn't say that. It might be true, but it's not what I said.

I said you couldn't share one.

So how could multiple threads have a copy of the handle if it can't be shared? Through the cloning process that happens a new thread is created.

my $x = 123;

( async {
   $x = 456;
   say $x;  # 456
} )->join();

say $x;  # 123
[download]

There are two $x since the entire interpreter is cloned when it creates a thread.

There's even a callback that allows libraries with external resources to clone themselves. Whether DBI and DBD::SQLite takes advantage of that or not is unknown to me. But I find it unlikely. How do you clone a database connection? So it's probably true that one can only use DB handle in the thread that created it.

You wrote:

my $x = 123;

( async {
   $x = 456;
   say $X;  # 456
} )->join();

say $x;  # 123
[download]

but I don't think you tried it; on the line you annotate with # 456, you probably mean to say $x, but you put say $X. Tragically, Perl is case-sensitive when it comes to variables.

Thanks, fixed.

and you didn't notice before you clicked the "create" button in the preview page for the comment?

For future reference see Markup in the Monastery. Also note the following text just above the edit field you entered your comment into:

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
[download]

and the helpful material immediately below the edit field that emphasizes the use of paragraph tags and code tags.

Optimising for fewest key strokes only makes sense transmitting to Pluto or beyond

Actually no, I did not. I thought what was in the preview was what I had typed and it looked (well) like what I typed. My bad. Doh. Hey ya'll using p and code now.

Right now I can't even make sense of the use of what I think is just about as simple an example I can start with. The Hash keys value changes between subsequent prints and that has me looping on wtf.

and OMG, using p and code produced entirely different preview results!!!

What I am trying to do is share a Hash (actually may Hash's) of Perl managed SQLite (module) DB's by using threads and threads::shared such that a Hash of 'many' open DB handles gets used by 'many' threads (concurrently executing but restricting concurrently executing on the same DB handle). Around that I have other Hash associated with the Hash of DB handles that help me ensure single thread usage of individual handles so that I can process either a single (for right now) query of many DB (handles) concurrently OR (more generically and more directly to the real intent) many threads serving many query requests concurrently while ensuring that each DB handle is only being used by one thread at a time (using a spin-lock structure).

I might have 10's, 100's or even 1000's of DB handles open concurrently and 'many' threads.

What this does is remove the 'cost' of opening/closing individual DB's for a query. The actual open/close are done by the threads, if a DB is targeted for query but is not in the open DB handle Hash it gets opened (for all to see) while another thread is maintaining the maximum-open-handles using a LRU structure so I can (example) configure to 1000 open DB handles (max) and threads accumulate their list of target DB files, open the file if not already in the DB handle hash, query pre-DB-handle only-one-at-a-time (enforced/signaled by a shared spin-lock Hash), and the maximum open is monitored and enforced by a LRU monitor, spin/repeat.

Right now this is essentially all happening in development code but now as I try to turn the non-shared Hash into shared I am getting 'compile' errors. So because the Hash are non-shared each thread subroutine is of course copying stuff when instantiated but then runs locally with duplicated rather than shared handles -- not what I want (it runs but not the next level of refinement).

[root@itx2 ~]# cat x8.pl
use DBI;

use threads;
use threads::shared;

my $dsn :shared;

my %hash :shared;

my $Sdatabase :shared;

$Sdatabase = '/opt/asd/sql/dbi-indexed-GeoLite2.db-sqlite-old';# $ARGV
+[0];

$dsn = "DBI:SQLite:dbname=$Sdatabase";

$hash{"$Sdatabase"} = shared_clone(DBI->connect($dsn, '', '', { PrintE
+rror => 1, RaiseError => 1, AutoCommit => 0, ReadOnly => 1 }));

print '$hash{"$Sdatabase"} -> ' . $Sdatabase . ' -> ' . $hash{"$Sdatab
+ase"} . "\n";

print '$hash{"$Sdatabase"} -> ' . $Sdatabase . ' -> ' . $hash{"$Sdatab
+ase"} . "\n";

print '$hash{"$Sdatabase"} -> ' . $Sdatabase . ' -> ' . $hash{"$Sdatab
+ase"} . "\n";

exit;
[root@itx2 ~]# perl x8.pl
$hash{"$Sdatabase"} -> /opt/asd/sql/dbi-indexed-GeoLite2.db-sqlite-old
+ -> DBI::db=HASH(0x562a9cb92f50)
$hash{"$Sdatabase"} -> /opt/asd/sql/dbi-indexed-GeoLite2.db-sqlite-old
+ -> DBI::db=HASH(0x562a9c858f60)
$hash{"$Sdatabase"} -> /opt/asd/sql/dbi-indexed-GeoLite2.db-sqlite-old
+ -> DBI::db=HASH(0x562a9c8593c8)
[root@itx2 ~]#
[download]

Right now because I can't even get a single Hash key to have a stable value between repeated prints all the other stuff, possible or not, will just have to wait realizing my goal of a shared Hash of SQLite DB handles....