in reply to DBIx::Class and Parallel SQLite Transactions

perl threads or fork

Neither of these work with DBI without special precautions.

SQLite doesn't really cater towards multiple writers anyway, and only in recent versions picked up good support for single-writer / multiple-reader configurations. I would think hard about if you really, really need multiple writers with an SQLite database and can't move to Postgres or some other database.

  • Comment on Re: DBIx::Class and Parallel SQLite Transactions

Replies are listed 'Best First'.
Re^2: DBIx::Class and Parallel SQLite Transactions
by learnedbyerror (Monk) on Jul 14, 2011 at 12:50 UTC

    Corion, thanks for your response.

    One of the reasons that I selected DBIx::Class as an ORM was that it detects threads/forks and manages the DBI handles approrpriately. I really don't think I want to get into writing and managing code to do this on my own.

    I am thinking hard about my continued use of SQLite. Given what I am doing, there is benefit in using a file based DB. It give me flexibility in moving the execution from machine to machine without having to manage a database move/replication. However, it does come with many limitations, concurrency and write performance being the main ones.

    This query is probably my last stop on the continued use of SQLite for this project. If I don't get any eye opening responses on how to accomplish my goals with SQLite, I will be moving to another DB, probably MySQL since I have other applications that use it.

    Thanks again,

    lbe

[reply]

      DBIx::Class is easily fork/threadsafe, as it is pure Perl. But its README says

      ... [ DBIx::Class ] is fork- and thread-safe out of the box (although your DBD may not be).

      ... and I don't expect DBD::SQLite to be fork and/or threadsafe. Likely, the SQLite documentation and/or DBD::SQLite documentation even say so.

[reply]
        To the best of my knowledge, DBIx::Class is fork-safe with all DBD modules, because it just opens a new connection if it detects that a fork has happened (by comparing the current PID to the one that was recorded at the time of the last DB connect, or something similar).
        Perl 6 - second systems done right
[reply]

        I'm certainly no expert here. My belief that DBIx::Class with SQLite is threadsafe is based upon not just the CPAN documentation that you referenced but also several threads that I have read from the DBIx::Class developers. This thread from GrokBase seems to be the most complete, at least that I remember, : [Dbix-class ] "fork- and thread-safe"

        Things certainly seem to behave properly when reading from SQLite; however, I can't say the same for writing to SQLite.

        But as I previously mentioned, this appears to be an issue with how the lock state of the database is handled. I haven't tested this across separate autonomous processes, though while typing this note I thought of a way I can probably test this without too much work. I'll give it a try this evening when I have a little time.

[reply]

In Section Seekers of Perl Wisdom