What database(s) are you using? The reason I ask is that a statement handle should not block another statement handle on the same DB connection.

But you post is definitely useful since I have seen many issues where there were multiple DB connections from one process that caused deadlock problems. Frequently these were because the same process locked itself by doing an uncommited update through one connection and a select through the other. Or an interaction between two processes each with a connection to two DBs. Even if your database has deadlock detection, it will be unable to detect a problem in these cases since the DB does not know that a single process has multiple connections open.

I would change your advice to the more generic make sure that you hold a lock for as short a time as possible which in this case means commit your changes as soon as possible. Do as much select work as you can before making changes & issue the commit as soon as all related work is done. And any time a process has multiple DB handles open, you have to make absolutely certain that it will not deadlock. Usually you do this by making all processes that select or update do it in a defined order. And yes, doing safe transactional computing is hard.

-ben

In reply to (Knob) Re: My script locks up using DBI by knobunc
in thread My script locks up using DBI by grinder

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.