There are mechanisms for locking tables. However, they vary greatly from database to database. This will work - but if you're already having load issues, this increases the database bottleneck by forcing everyone to wait, even if they wouldn't have collided, which could be disastrous.

It would be better to have a unique set of keys. You might not be able to have just one unique key, but you can specify a set of keys, so that you only get, say, one valid entry per customer per day. The second attempt to insert will throw a SQL error, so make sure you wrap the call in an eval and catch it. Ensuring database integrity with the right keys so you never get duplicate data where it would be inappropriate is a major design goal of any db table.

Also, you may (or may not, it's hard to tell) be able to do some rearchitecturing of your perl layer to stop the race conditions. If you're having load problems causing things to back up, there's probably other issues to consider, and it's time to think about performance tuning and perhaps different methodologies to get your job done.

-- Kirby, WhitePages.com

In reply to Re: Locking a database connection by kirbyk
in thread Locking a database connection by cosmicperl

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.