FAQ
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

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.

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).

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.

I'm happy to do the POD writing myself if someone wants to just reply
with information to the mailing list.

Thanks all

Adam K

Search Discussions

  • Martin J. Evans at Mar 19, 2012 at 8:42 am

    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.
    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.

    Some DBDs are still littered with dTHR macros which as far as I am aware has been a noop since Perl 5.8.
    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).

    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.

    I'm happy to do the POD writing myself if someone wants to just reply
    with information to the mailing list.

    Thanks all

    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.

    I note DBD::SQLite does not seem to be using the DBIS macro so there is nothing to change.

    Sorry that does not necessarily help you with SQLite and using threads.

    Martin
    --
    Martin J. Evans
    Easysoft Limited
    http://www.easysoft.com
  • Tim Bunce at Mar 19, 2012 at 12:14 pm

    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.
  • Kenichi ishigaki at Mar 20, 2012 at 5:40 am
    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)
    otherwise.

    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.

    Regards,

    Kenichi

    (Sorry for dupe, Tim)

    2012/3/19 Tim Bunce <Tim.Bunce@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@lists.scsys.co.uk
    http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite
  • Tim Bunce at Mar 26, 2012 at 8:18 pm
    Thanks Kenichi.

    Since no one has suggested any particular version I'll disable the
    DBI thread warning for perl >= 5.12.

    Tim.

    On Tue, Mar 20, 2012 at 02:40:22PM +0900, kenichi ishigaki wrote:
    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)
    otherwise.

    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.

    Regards,

    Kenichi
    2012/3/19 Tim Bunce <Tim.Bunce@pobox.com>:
    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?

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbd-sqlite @
postedMar 19, '12 at 4:19a
activeMar 26, '12 at 8:18p
posts5
users4
websiteshadowcat.co.uk

People

Translate

site design / logo © 2021 Grokbase