I've read the DBI documentation on CPAN and ordered "Programming the Perl DBI" but haven't received it yet. My Perl skills are mediocre so I'm looking for some suggestions

I'm making a database app with MySQL and there are many tables with foreign key references. I'm using InnoDB tables that support transactions.


Say I have some tables called "People", "Address", and "LivesAt". "People" to "Address" is a many to many relationship and "LivesAt" has the foreign keys of "People" and "Address".

So what I'm planning on doing is to add a person to "People" and call "$rv = $dbh->last_insert_id;" to get the persons primary key to use as the foreign key in "LivesAt".

But say I add a person to the database then someone else milliseconds later adds another person to the database, and then I execute "$rv = $dbh->last_insert_id;", would that give me not my insert_id but the other persons insert_id? If this would give me an improper insert_id then I'll use transactions.

This may be far fetched, but I've looked all over and haven't seen any answers. Is there any other way to update multiple tables that have related foreign/primary keys and keep the foreign keys consistent besides doing an update, then getting the "last_insert_id" and using that as the foreign key for related tables?

In the "Perl Cookbook 2nd Edition" it says you can do "inserts, updates, deletes, queries here" on page 573 in the eval statement. Does this mean you can do SELECT statements while in the middle of a transaction? I'd like to do this to get the foreign key values for my new INSERTS.


Summary of Questions:
1. Say I add a person to the database then someone else milliseconds later adds another person to the database, and then I execute "$rv = $dbh->last_insert_id;", would that give me not my insert_id but the other persons insert_id from their insert?

2. Is there any other way to update multiple tables that have related foreign/primary keys and keep the foreign keys consistent besides doing an update, then getting the "last_insert_id" and using that as the foreign key for related tables?

3. Can I do SELECT statements while in the middle of a transaction?

In reply to DBI & Multiple Table Inserts, Foreign Key Inserts and Transactions by awohld

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.