cflock question

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?

ex.

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.

Regards.

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?

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)

then

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.

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

rudy
http://r937.com/

just wanted to say thanks for all your help and advice.