> Here's a question I've been wrestling wtih. > Suppose that there is an HTML form to create a new user. > When the form > is submitted, that user's information is added to the > 'user' table. > The table uses an auto-increment integer for its primary > key/user id, > so I'd like to grab that value for the newly created user.
> I've been using queries like this one: "SELECT > MAX(user_id) FROM > users", but I worry that if two users are created at the > same time, > then my query might give me the user id of the wrong user. > Is there a better way to do this? Or maybe I shouldn't > even worry about this? > Thanks. > Chris I would definately worry about it. Yes, if you "select max(user_id) from users" to get the inserted record id after performing an insert without any other preparation, you will create a race condition in which two pages could erroneously receive the same user_id value even though 2 separate values had been inserted into the table. There are several ways to get around this issue. One is to use an nvarchar, varchar or guid column in SQL server and generate your ID prior to the insert. If you need to insert additional records in related tables for the generated user, then you'll have to either generate the ID in ColdFusion or you'll have to find another way to return that GUID to CF from SQL Server (the function NewID() is used in SQL Server to generate these id's) such as a stored procedure. Oracle by comparison will give you SQL Standard Sequences (not widely supported yet) which can be used similarly to identity columns but allow you to fetch the next id prior to insert. Personally I prefer to generate ID's in CF. Another popular method is to use a cftransaction around the insert and the select statements... <cftransaction isolation="serializable"> <cfquery ...>insert into ...</cfquery> <cfquery ...>select max() from ...</cfquery> </cftransaction> This eliminates the race condition at the expense of carving out some scalability. By this I mean that as the size of your table becomes larger, it takes longer to perform the select max() query. Generally speaking the increase in time for the 2nd query isn't noticeable, but it's possible in theory that it could be an issue with very large numbers of records (I would guess in the millions, so it's not likely to be an issue with a users table specifically, but may be with some other tables). Another option is to use database-specific tools to fetch the new id. With SQL Server 2000 or 2005 you can use a single query with 2 statements like so: <cfquery ...> insert into ... select scope_identity() as newid </cfquery> The scope_identity() function was added in SQL Server 2000 to resolve the issue of the previously available @@identity variable not being thread safe (i.e. same race condition issue you have now). Of course, the trade here is that the code then becomes dependant on the choice of SQL Server. Most of the time this is fine, but on the off chance the company hires a new CTO who's in love with Oracle, you'll have to replace any references to this method. Of course, the same is largely true of the first method above if you use newid() to generate GUIDs, although using stored procedures to perform those inserts can insulate the ColdFusion code from those changes (i.e. you would still have to do the work if you needed to convert it to use Oracle, but your CF code would remain unchanged). So -- there are some options, plus and minus. For my money it just seems to be easiest in general to generate ID's in advance of the insert with CF, although in some environments it's necessary to allow records to be inserted by non CF systems as well, at which point a database-specific tool (usually stored procedures) becomes a necessity. s. isaac dealey 434.293.6201 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://www.fusiontap.com http://coldfusion.sys-con.com/author/4806Dealey.htm ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234926 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
