[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