in reply to DBI and threads

I'm not up to the most current developments with DBI, but at least 6 months ago, the case was that you should not share DBI handles across threads, and at most one thread should do the database access, as the DBDs are in general not threadsafe, and DBI itself also isn't designed for use within threads.

If you only use one database connection ("the brain"), you can start more than one thread ("the zombies"), and you can pass the database connection around between the threads ("swap around the brain"). But only one thread can access the database connection at one time, and there might be some DBI function call you need to make so that DBI gets notified of the change in the current thread.

Replies are listed 'Best First'.
Re^2: DBI and threads
by fd15k (Novice) on Jan 26, 2005 at 21:25 UTC
    I'm not sharing the handle between the threads for concurrent access, but trying to manage connection pool, and give every thread exclusive access to 1 database connection.
[reply]

      I just looked at the DBI::FAQ, and there it says:

      The DBI, as of version 1.02, does not yet support multi-threading so it would be unsafe to let more than one thread enter the DBI at the same time.
      It is expected that some future version of the DBI will at least be thread-safe (but not thread-hot) by automatically blocking threads intering the DBI while it's already in use.

      So the answer is no. Your program can have at most one DBI instance alive ("the brain").

      Update: perrin points out that my information is old and not completely correct anymore - DBI will try its best and clone every database handle that was created before a thread was spawned, and it will even try its best to prevent you from sharing a database handle across thread boundaries. But there still is the caveat:

      But BEWARE, some underlying database APIs (the code the DBD driver uses to talk to the database, often supplied by the database vendor) are not thread safe. If it's not thread safe, then allowing more than one thread to enter the code at the same time may cause subtle/serious problems. In some cases allowing more than one thread to enter the code, even if not at the same time, can cause problems. You have been warned.

      Using DBI with perl threads is not yet recommended for production environments. For more information see Things you need to know before programming Perl ithreads

      So, the "best" solution is to either have one thread to handle the database connection, or one or more external processes that connect to the database(s).

[reply]
[reply]
        So basically, I'm not allowed to use any DBI calls in case DBI is already utilized in another thread ?
[reply]

In Section Seekers of Perl Wisdom