|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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. |
|
#3
|
|||
|
|||
|
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?
|
|
#4
|
|||
|
|||
|
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. |
|
#5
|
||||
|
||||
|
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/ |
|
#6
|
|||
|
|||
|
thanks
just wanted to say thanks for all your help and advice.
|
![]() |
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > cflock question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|