Some time ago I submitted a question titled DBIX::Class and transactions while trying to identify the best way to handle transactions when working with DBIC, Moritz pointed me in the direction of txn_do. I have been using this approach but have not been totally happy with it when trying to have multiple threads/processes update my SQLite database.

My need/desire/want is to have multiple threads that gather information from various sources and update_or_insert it into my SQLite DB. For the purposes of this question, please consider thread(s) to mean either perl threads or fork as I get the same error regardless of parallel methodology.

My current methodology is to have a set of worker threads that pick up work from a Thread::Queue, call it q1, and gather the information as defined in the queue entry. The thread then stores the information in a reference and enqueues it on Thread::Queue q2. I then have a single thread that dequeues the reference from q2 and call the update subroutine using txn_do. The update subroutine iterates through the reference and performs multiple DBIC update_or_insert calls, anywhere from 1 - 10000, inside a single transaction. As long as I run a single update thread, this works fine. However, this single thread is the limiting factor in overall run time.

NOTE: The database rows affected by a transaction are mutually exclusive from those of any other transaction. My need for transactions are to insure that all or the rows affected are updated consistently or that none are and the issue is logged.

My problem is that any time that I try to run more than one thread, all but one of threads, die and the only error message that I get is that the thread terminated abnormally. I have tried to capture the error message; however, txn_do is recorded as having thrown the exception. The associated trace does not contain any detail about where the actually error occurred. In fact, it only points to the line at which txn_do was called and not the the line in the coderef that actually fails. Using logging, I have been able to determine that it fails when I make the DBIC call to update_or_insert.

My current opinion/assessment of the matter is that txn_do in conjunction with the SQLite drive place the database into an exclusive lock for a relatively long time period (2 - 5 secs as determined by attempting to run a select in the sqlite3 CLP when running updates) and that there is an inssuficient retry count/period to cover this. While I feel confident in my observations, I am much less confident about my conclusion and event less confident at picking a path forward.

So my question(s) is/are

  1. Have any of you seen this issue before and do you have a recommended solution?
  2. Is it feasible to maintain update concurrency with SQLite using DBIC?
  3. Would I do better to code directly in DBI and use a semaphore to ensure that only one thread is executing a commit at one time?
  4. And lastly, am I stepping all over a limitation of SQLite and should I move this to another DB like MySQL?

Thanks in advance for your assistance O Monks! I am sure that you can help me to see the light!

lbe

In reply to DBIx::Class and Parallel SQLite Transactions by learnedbyerror

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.