If all tables are using the InnoDB engine, then you can certainly use transactions. The way to do this in DBD::mysql is to turn AutoCommit off, then issue your statements. You can optionally send an explicit "BEGIN WORK" statement, but you don't have to. (I do, but that's cause I'm paranoid.) When you're ready to commit, issue a $dbh->commit() to commit or $dbh->rollback(), if you're rolling back. (Those functions are documented in the DBI documentation.)

If any of your tables are non-InnoDB, then you would have to issue a LOCK TABLES statement, pairing that with the appropriate UNLOCK TABLES at the appropriate time. The big issue with this is that you don't have any method to rollback if you run into a problem. The other problem is that you have a lock on those tables, which means all other attempts to modify the tables (and possibly even read, depending on the lock) are blocked until such time as you release the lock. Because of this, you can enter a race condition where process 1 locks tables A and B and process 2 locks tables B and A. If they run at the same time, process 1 locks table A, then tries to get a lock on table B. But, process 2 already locked table B and is trying to get a lock on table A. This is called deadlock, which is bad.

As for the process by which you do this, you would use the $dbh->{mysql_insertid} to find the last inserted id. (This is documented in the DBD::mysql documentation.) This is a step up from, say Oracle or Sybase, where you have to explicitly issue a SELECT statement to retrieve this information.

My criteria for good software:
  1. Does it work?
  2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

In reply to Re: Inserting into several linked MySQL tables. by dragonchild
in thread Inserting into several linked MySQL tables. by techcode

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.