Transactions and Locking

[gbrownuk]

I could do with some help on implementing locking in a multiuser environment.
The scenario is quite simple and I'm temporarily using MS Access to test the
principle before moving on to MSSQL.

Users can VIEW a customer record from a list, select EDIT (which generates
another SQL Select to get latest information) and then select SAVE to update
the record after an amendment (using SQL Update). In a client server
environment I would put a transaction around the SELECT and UPDATE to LOCK the
record from update by other users but still allow them to read the record. When
the user saves or cancel's EDIT, the transaction ends and the LOCK is released.

The problem I have is I can't get my head around how to do this in a HTML/CFML
environment. I know how to use CFTRANSACTION but you can't open it in the page
that executes the SELECT and puts the data in the FORM, then terminate the
CFTRANSACTION in the page called by the SUBMIT action where the UPDATE is run.
If SELECT and UPDATE are not within the tranaction the customer record could
have been changed by another user before the current user have saved their
changes.

I would appreciate any advise on how to do this.

Cheers
Graham

[gbrownuk]

Would using a timestamp be the answer? Select the record and note the value in
a date/time field, make changes to the record then submit. Before the update is
actioned the record is queried again and the record locked. If the timestamps
match the record is updated, if they don't the operation is cancelled and the
user informed the record had changed?

Is the the right approach?

Any help would be appreciated.

[Dan Bracuk]

If more than one person can update the same record, then they can overwrite
previous updates irrespective of any code you write. So you might choose to
not worry about it.

If you do want to have a bit of control over things, you can add a field to
the table that you set to 0/1, y/n whatever. The value ot the field determines
whether the record is available for updating.

Originally posted by: gbrownuk
I could do with some help on implementing locking in a multiuser environment.
The scenario is quite simple and I'm temporarily using MS Access to test the
principle before moving on to MSSQL.

Users can VIEW a customer record from a list, select EDIT (which generates
another SQL Select to get latest information) and then select SAVE to update
the record after an amendment (using SQL Update). In a client server
environment I would put a transaction around the SELECT and UPDATE to LOCK the
record from update by other users but still allow them to read the record. When
the user saves or cancel's EDIT, the transaction ends and the LOCK is released.

The problem I have is I can't get my head around how to do this in a HTML/CFML
environment. I know how to use CFTRANSACTION but you can't open it in the page
that executes the SELECT and puts the data in the FORM, then terminate the
CFTRANSACTION in the page called by the SUBMIT action where the UPDATE is run.
If SELECT and UPDATE are not within the tranaction the customer record could
have been changed by another user before the current user have saved their
changes.

I would appreciate any advise on how to do this.

Cheers
Graham

[mxstu]

gbrownuk,

You might want to have a look at the response by Kronin555 in this thread:

[url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=2&thread[/url]
id=1055297

It describes the concept of versioning which sounds like what you're
describing.