You need database transactions. I am NOT speaking for mysql, since I am not a fan of it, so YMMV.

Every DBD has a way of turning transactions on if the db supports it and the dbd supports it. A transaction is simply a bunch of statements, language related to the db (sql,pgsql, pl/sql, transact sql), which are treated like an atomic unit. Either it all pushes through or the state before the transaction even started is restored. They only get pushed through when a commit (a "hey, i'm done now" occurrs).

At any rate, open your connection, do your thousands of statements and issue a $dbh->commit().

If your DBD doesn't support it, a lot of databases allow you to start a transaction arbitrarly via SQL. YMMV on databases.. oracle supports it as a BEGIN and END statement in a single statement handle, so ->commit is kinda pointless, at least from what i've seen. PostgreSQL allows you to span your transaction over multiple statement handles as transactions are connection oriented, so ->commit or "BEGIN ... COMMIT" works over multiple statement handles.

Notes..
Do this when your DB has a small load if you can. And make sure that you spend the least time between your first statement and your commit's. The more time you spend, the more other queries can get queued up due to locking. When a transaction is occuring with a write, other transactions wait for the writes to end.

Transactions incur a NASTY cost on your db depending on how your db is implemented. For instance, older versions of mysql didn't have row level locking.. so writing to a particular row would lock the entire table and thus prevent others from accessing it until the write was done. Oracle is usually nice about locking, though it can't get around people doing long winded updates, like UPDATE TABLE SET COLUMN = NULL.


Play that funky music white boy..

In reply to Re: Executing a Script to Completion by exussum0
in thread Executing a Script to Completion by Baz

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.