Thread: cflock question

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    54
    Rep Power
    12

    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.

    Code:
    <cflock timeout="60" throwontimeout="yes" type="exclusive" scope="application">
    
    <!--- insert new record --->
    
    <!--- select max(id)... --->
    
    </cflock>
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Washington DC, USA
    Posts
    156
    Rep Power
    14
    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?
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0
    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.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    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/
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    54
    Rep Power
    12

    thanks


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

IMN logo majestic logo threadwatch logo seochat tools logo