Locking Records

[cfdeveloper]

Hell Everybody, I really need someone's help. I'm working on a cf application with a sql backend. This is an IT Logs database system. I am working on the front end for the support desk. A support desk user can view a log, add a new log or update a log. My query is specific to viewing and updating a log. The log information is pulled from the logs table. Now then, a user can associate one or more assets to a log. The asset information is in the asset table.

The asset info pop-up window pops up when the user hits on the 'asset' button from the view logs page. This is how the tables are linked. Each log has a unique log_id. Each asset has a unique asset_id. This asset_id is stored in the log table in the asset_id column. If a user changes the asset information for a particular asset, all logs that are associated with this asset display the modified asset information.

Is it possible to lock a record, so that another user can not edit delete this until you are finished with it. He should however be allowed to view the record.

Can somebody please suggest different ways to go about doing this? Some sample code would also be helpful

Best regards,
cfcoder

 

[philhege]

SQL provides for atomic transactions, which commit only when there is no conflict, and, properly coded, roll back conflicting transactions. This is the approach you need to take to deal with concurrent actions.

Read up on BEGIN...END TRANSACTION in Books On Line.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.

 

[imstillatwork]

thats fine, but that will not prevent this:

user A opens a record, then user B opens the same record. User A makes changes and saves, then user Bmakes changes and saves. The database has done its job. there was no conflict. but user a opens the same record again only to find that his changes are gone, and user B's are there.

If I understand the situation right, you want to be able to tell user B that the record is in use, and that User A has it opened?

the probelm is, that the database is not held open while the user A is reading or changing the record. it gets the record, sends it to the web app, and is done with it.

 

[diggy8]

I just came up with this same question.

And if the record could be locked, how could it be reopened automatically if the user just closes the brower?

 

[philhege]

The "user A / user B" problem can be solved with diffgrams.

If the before-change state of the record doesn't match with what's been pulled down, the changes shouldn't take place until the user's local view is refreshed.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[imstillatwork]

that would also solve the closed browser issue, since you would check for changes before updating data.

 

[TruthInSatire]

and then request the user to refresh the page loosing all the changes he/she just made just to see the changes user b made? hope it's a small form or you'll have some pretty angry clients. i'd never ask my clients to do that.


how about this instead.
save all the old values in the form as hidden form fields like suggested above. check the db for changes compaired to your old values as you update.

<cfquery>
UPDATE tableName
SET
<cfif (qDBValues.name eq form.old_name) or (form.old_name neq form.name)>
name = '#form.name#'
</cfif>
<cfif (qDBValues.phoneNumber eq form.old_phoneNumber) or (form.old_phoneNumber neq form.phoneNumber)>
,phoneNumber = '#form.phoneNumber#'
</cfif>
where ID = #form.id#
</cfquery>

this will not update fields that someone already updated preserving the changes of user "a" but will update everything else. however it also checks to see if the old values don't equal your new values so if user "a" changes form.name and user "B" changes the name field too it'll take the lastes change because it's a change that was ment to happen.

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
-Douglas Adams (1952-2001)

 

[MorganGreylock]

I had a similar problem to this a while back, and just decided to actually delete the record from the table that I wanted to update. I'd read it into a data structure, delete it from the original table, make my changes, and then resave it. It wasn't the cleanest way to go about it, but for that project, it seemed to work decently enough.

I really can't say if it would help you in this case, but I figured I'd suggest the possibility.

MG