One other approach you could consider (as a last resort, perhaps, if the mysql-centric approaches fail), is to have your perl script use a semaphore file, such that whenever there are multiple instances of the script running simultaneously, the chunk of code that does the database transaction can be made atomic, and when two or more processes try to do that chunk at the same time, the semaphore file will "serialize" them.

I posted a pretty good/simple semaphore file module here (drawn from a Perl Journal article by Sean Burke).

I actually had a recent case using Oracle on a solaris server, with a remote windows-2000 box running up to 12 simultaneous instances of a given perl script, each instance doing a pretty heavy query-for-update; when I left it to oracle to manage the contention, it seemed like the delays in servicing the queries were multiplicative -- time to complete a given query varied according to the number of pending requests, and as a result, some requests were pending for a very long time); when I included the use of a semaphore file in the script, so that the queries were serialized on the windows box, the delays became simply additive -- now the actual query execution always took roughly the same time.

In reply to Re: Issue with simultaneous MySQL actions by graff
in thread Issue with simultaneous MySQL actions by Anonymous Monk

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.