How do I prevent transactions submitted by different users from getting intermingled?

[Jane_Websdell]

We are using ColdFusion 8 with Oracle.  I have a situation where 2 users are creating entirely separate records (there is no relationship between them) at the same time, and then they attempt to make changes to them.  Once they have submitted their changes, their records re-display to them, but the users can now see parts of the other's record in their own.  We are using cftransaction, but we are not using cflock in our code.  It is my understanding that cflock should not be necessary in ColdFusion versions later than 4.5.  Is that correct?  If so, what can I do to remedy the above?  Any help would be greatly appreciated.

Jane

[Adam Cameron.]

What do you mean, precisely, by "the users can now see parts of the other's record"?

What's the code you're using to update the DB, and what code are you running to refetch the data & displaying it?

And is the data updated correctly in the DB, or is it being munged?

<cftransaction> and <cflock> are only superficially related in that they effect kinda the same thing, but the former acts on the database (or - more to the point - is an instruction to the database to handle operations transactionally), the latter on CF code.  But the two are not connected in such a way that one specifically needs to use one or the other or both at the same time.

--

Adam

[Owainnorth]

+1 what Adam said, there is *absolutely* no reason this should ever happen.

Are you attempting some kind of subselect to get the max id in the database to display to them perhaps? Or something similar?

It should be as simple as:

Get sequence ID.

Insert data using ID as PK

Read data using ID

Oracle sequences will only ever give out a PK once so you don't even need a CFTRANSACTION block around it unless you're using CURRVALs and NEXTVALs, in which case it's advisable.