[thelist] Free Tip about SQL

rudy rudy937 at rogers.com
Mon Jul 21 17:34:36 CDT 2003


>> someone else might manage to add another new record *after*
>> your record got added and *before* your select statement gets run.
>
> Unless you execute the insert + select as a single transaction
> with appropriate lock-levels

that's true

but as you may know, creating a transaction block is a very expensive
proposition for a database engine, with possible performance implications

in order of preference, here are the techniques --

1  @@identity, mysql_last_id, or whatever method the database provides

2  INSERT, then SELECT back using the "real" primary key value(s)

3  transaction block around  INSERT, SELECT MAX(id)

note that there is absolutely no danger of getting the wrong row back using
method 2, and further, method 2 does not require a transaction block


rudy





More information about the thelist mailing list