Sistren and Brethren. Let me tell you my tale.

My app gets input from far and wide via CGI; it then does two things with this input: (A) enters it into a MySQL table, generating an AUTO_INCREMENT LineID for each new record; (B) sends an email to the user, which, inter alia, refers to the newly-created record by giving the new LineID. (For this example, col headings in tbl are LineID MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, Col2, Col3.) So my first thought was, I do:

$dbh->do("INSERT INTO tbl VALUES(NULL,'foo','bar')");
[download]

and then either

my $ref = $dbh->selectcol_arrayref(
    "SELECT MAX(LineID) FROM tbl"
);
my $LineID = $$ref_booking[0];
[download]

or

my $ref = $dbh->selectcol_arrayref(
    "SELECT LAST_INSERT_ID() FROM tbl
");
[download]

But I tested this by making my script sleep between the INSERT and the SELECT, and whilst it was asleep running a second INSERT from another script. And in both cases, the result was that the SELECT returns the LineID for the record created by the second INSERT.

So now I think I need to do some table locking. And think I know how to do this in SQL. But I'd rather keep it in the family, and use the eval {..} construct mentioned in the docs:

$dbh->{AutoCommit} = 0;  # enable transactions, if possible
$dbh->{RaiseError} = 1;
eval {
    $dbh->do("INSERT INTO tbl VALUES(NULL,'foo','bar')");
    my $ref = $dbh->selectcol_arrayref(
        "SELECT MAX(LineID) FROM tbl"
    );
    $dbh->commit;   # commit the changes if we get this far
};
if ($@) {
    warn "Transaction aborted because $@";
    $dbh->rollback; # undo the incomplete changes
    # add other application on-error-clean-up code here
}
[download]

... but, and here I tear out what little hair I have left, my MySQL doesn't support transactions.

So my question is, is there a perl way to get the same result as locking? Or should I just make the effort and learn some of the basics about MySQL?

(With thanks to all those who helped me get this far in the CB this morning - and I hasten to add, I've been working on other stuff too since then!)

§ George Sherston

---

---

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! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

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:
	&		&
	<		&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.