October 29th, 2003, 07:16 PM
If I have a registration form where a new record is inserted into one table and then want to select that new record's id using a select max(id) query, can I surround my code with cflock to ensure that the max(id) selected is actually the id of the record just inserted? Will the cflock prevent other potential users from inserting a new record until the previous record is inserted and it's id is selected?
<cflock timeout="60" throwontimeout="yes" type="exclusive" scope="application">
<!--- insert new record --->
<!--- select max(id)... --->
November 5th, 2003, 06:47 PM
I think if you combine the lock with a CFTRANSACTION block it may guarantee that the max ID selected by the second query is in fact the record inserted by the first one, but I'm not 100% sure. In any event, this is messy...I'd do it on the database side if possible via a stored procedure. By the way this is really a database contention issue and not a ColdFusion-specific issue.
November 19th, 2003, 08:20 PM
I would avoid putting a CFQUERY (or any other "time consuming process") inside of a CFLOCK tag. You'll likely grind your app server to a halt under any kind of load. The best way to handle it is as kiteless suggests with the CFTRANSACTION tag. What database are you using?
November 27th, 2003, 12:01 PM
maybe I'm old fashioned but my preferred method to get the id of a record that was just inserted is to
1. when the data is first being inserted, insert a unique identifier along with the other data (could use the cf rand function)
2. select the row containing the unique identifier
selecting (max) could give you the worng record if the db is updated in that moment between transactions.
November 28th, 2003, 11:04 AM
i'd like to second highwaysjammed4's approach
when you think about it, an auto_increment or identity field is a surrogate key, and the table will likely have some other column(s) which are the "real" primary (i.e. unique) key
simply retain those column values after the INSERT and then query the row back in a subsequent SELECT
locking is not required, so this approach works really well in a heavy load and/or multi-threaded app
it's usually not necessary to have a separate unique identifier as well
December 4th, 2003, 11:06 PM
just wanted to say thanks for all your help and advice.