[DBD-SQLite] DBI, DBD::SQLite and threads

kenichi ishigaki kishigaki at gmail.com
Tue Mar 20 05:40:22 GMT 2012

Hi. I don't usually use the "threads" module in my projects, but...

1) DBD::SQLite has several tests that use fork(), that means emulated
fork() (with ithreads) under Windows, and they've been working fine
for years.

2) DBIS/dTHR issue was fixed at DBD::SQLite 1.22_04 (released in
April, 2009) by Tim's advice.

3) Docs on SQLite3's thread safety:
  - http://www.sqlite.org/compile.html#threadsafe
  - http://www.sqlite.org/c3ref/threadsafe.html
  - http://www.sqlite.org/faq.html#q6

4) DBD::SQLite is compiled with the default THREADSAFE option if your
perl supports ithreads, or THREADSAFE=0 (single thread mode)

5) As a general rule, a) you shouldn't use DBI handles prepared
outside the threads, and b) you're advised to set
sqlite_use_immediate_transaction attribute to true (or issue BEGIN
IMMEDIATE) to avoid (dead)locks.



(Sorry for dupe, Tim)

2012/3/19 Tim Bunce <Tim.Bunce at pobox.com>:
> On Mon, Mar 19, 2012 at 08:42:04AM +0000, Martin J. Evans wrote:
>> On 19/03/12 04:19, Adam Kennedy wrote:
>> >I've noticed there's a lot of movement at the moment on DBI, threading
>> >and performance.
>> >
>> >http://www.martin-evans.me.uk/node/131
>> The issue here was that when using a threaded Perl the state structure
>> had to be protected and there was a slow and a faster way of accessing
>> it. Some DBDs were using the slow method.
> ("protection" isn't the issue, it's simply that in a threaded perl
> there's one 'DBI State' structure per thread and it's important that the
> one for the current thread is used.)
>> >In Padre we've always stuck to the use of DBI only in the parent
>> >thread, but the time is fast approaching where it would be very handy
>> >to run multiple database connections for things like background
>> >indexing of code and the like.
>> When you build DBI it still says:
>> *** You are using a perl configured with threading enabled.
>> *** You should be aware that using multiple threads is
>> *** not recommended for production environments.
>> but I'm unsure to what degree this still applies.
> I think that could be removed, at least for recent perls.
> I wonder if there's some version where a good number of thread issues
> got fixed. Perhaps 5.10, 5.12? Would anyone using thread havily care to
> venture an opinion?
>> >I was wondering if anyone has any experiences with DBD::SQLite and
>> >threads, or can speak with some authority on where we are with regards
>> >to them (I do know that the CLONE method seems to blank out the driver
>> >structure forcing it to load again in the new thread).
> I think that's needed. Certainly handles created in one thread can't be
> used in another (the DBI dispatcher will detect that and throw an error).
>> >Regardless of whether they currently work or not, I'd like to be able
>> >to write a =head2 section in the POD documentation stating our current
>> >position on threads and recommendations before the next release.
>> >
>> >If they can be used in threads, it would be nice to be able to write a
>> >DBD::SQLite::Cookbook entry on using threads.
>> >Adam K
>> I'm afraid I neither use a Perl with threads enabled or threads -
>> which is a shame as I didn't see any benefit from that huge change to
>> DBD::Oracle.
> Many people will benefit. Thanks again.
>> I note DBD::SQLite does not seem to be using the DBIS macro so there
>> is nothing to change.
> Agreed. It looks clean to me.
> Tim.
> _______________________________________________
> DBD-SQLite mailing list
> DBD-SQLite at lists.scsys.co.uk
> http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite

More information about the DBD-SQLite mailing list