Application-Level Record Locking

[Bob Barrows]

Before you go too far down this road, check this out:
[url]http://www.microsoft.com/mspress/books/sampchap/3445.asp#SampleChapter[/url]


HTH,
Bob Barrows

Carl Revell wrote:

> I need to write a logging application using ASP for an intranet. I've
> written several before which, due to design, I haven't been so
> concerned about multiple-edits by different operators on the same log
> record.
>
> However, I am now in a position where I need to handle this situation
> carefully.
>
> More than one operator could choose to edit a log at the same time.
> I'm not worried about database-level locking as SQL Server will
> handle that. I'm more concerned with application-level locking.
>
> The log editor is an ASP page with several HTML form elements,
> textareas, inputs, etc holding data for an individual log. User A
> could choose to edit log L1 and up pops his HTML form. He reads it
> but without closing the browser, goes off to make a coffee.
> Meanwhile, in another office User B also chooses to edit log L1 and
> up pops his HTML form. He changes some text (for example) and saves
> his changes and exits. User A comes back with his coffee, his HTML
> page still shows the original (before User B's changes) data. He
> changes some text and saves his changes, overwriting User B's
> changes. :-(
>
> Now I could "remember" the original state of each log prior to each
> edit and then, just before saving the changes I could compare the
> current DB record with the original values. If they've changed, I
> could warn the user that someone else has edited the record but this
> then leaves the user (and me) with an awkward decision on whose
> changes to accept and/or how to merge the new changes with the
> existing changes.
>
> I'd _much_ prefer that User A locks the record for editing which
> prevents User B from saving (although he can still view) the record
> until User A has mad his changes and left the log. I want to also
> prevent User A from sitting on the edit screen of a particular record
> indefinately, locking it for anyone else.
>
> I can perhaps have a lock table in my application or a "locked"
> column for each record.
>
> I need to unlock a record once a change has been saved (easy) but I
> am stuck as to how I can unlock a record if a user simple closes
> their browser or sits with their browser open all day. I figure the
> session (default 20 minutes) could come into play somehow but not
> exactly sure how best to implement this.
>
> Does anyone have any practical experience of this they could share
> with me?
>
> Thanks for your help,
>
> Carl

[Carl Revell]

Bob,

Thanks for this link. I was unaware of this functionality offered by the ADO
client-side cursor - I assume this doesn't work for server-side cursors?

It may come in useful but it's not the best solution for the issue I wish to
resolve which really needs to present a read-only view of any record opened
by another user for editing.

I need an effective way of ensuring I unlock records even when a user simply
closes their browser.

Regards,

Carl

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:%23pLEEsHRDHA.2320@TK2MSFTNGP12.phx.gbl...

> Before you go too far down this road, check this out:
> [url]http://www.microsoft.com/mspress/books/sampchap/3445.asp#SampleChapter[/url]
>
>
> HTH,
> Bob Barrows
>
> Carl Revell wrote:

> > I need to write a logging application using ASP for an intranet. I've
> > written several before which, due to design, I haven't been so
> > concerned about multiple-edits by different operators on the same log
> > record.
> >
> > However, I am now in a position where I need to handle this situation
> > carefully.
> >
> > More than one operator could choose to edit a log at the same time.
> > I'm not worried about database-level locking as SQL Server will
> > handle that. I'm more concerned with application-level locking.
> >
> > The log editor is an ASP page with several HTML form elements,
> > textareas, inputs, etc holding data for an individual log. User A
> > could choose to edit log L1 and up pops his HTML form. He reads it
> > but without closing the browser, goes off to make a coffee.
> > Meanwhile, in another office User B also chooses to edit log L1 and
> > up pops his HTML form. He changes some text (for example) and saves
> > his changes and exits. User A comes back with his coffee, his HTML
> > page still shows the original (before User B's changes) data. He
> > changes some text and saves his changes, overwriting User B's
> > changes. :-(
> >
> > Now I could "remember" the original state of each log prior to each
> > edit and then, just before saving the changes I could compare the
> > current DB record with the original values. If they've changed, I
> > could warn the user that someone else has edited the record but this
> > then leaves the user (and me) with an awkward decision on whose
> > changes to accept and/or how to merge the new changes with the
> > existing changes.
> >
> > I'd _much_ prefer that User A locks the record for editing which
> > prevents User B from saving (although he can still view) the record
> > until User A has mad his changes and left the log. I want to also
> > prevent User A from sitting on the edit screen of a particular record
> > indefinately, locking it for anyone else.
> >
> > I can perhaps have a lock table in my application or a "locked"
> > column for each record.
> >
> > I need to unlock a record once a change has been saved (easy) but I
> > am stuck as to how I can unlock a record if a user simple closes
> > their browser or sits with their browser open all day. I figure the
> > session (default 20 minutes) could come into play somehow but not
> > exactly sure how best to implement this.
> >
> > Does anyone have any practical experience of this they could share
> > with me?
> >
> > Thanks for your help,
> >
> > Carl

>
>
>

[Bob Barrows]

Carl Revell wrote:

> Bob,
>
> Thanks for this link. I was unaware of this functionality offered by
> the ADO client-side cursor - I assume this doesn't work for
> server-side cursors?

Right

>
> It may come in useful but it's not the best solution for the issue I
> wish to resolve which really needs to present a read-only view of any
> record opened by another user for editing.
>
> I need an effective way of ensuring I unlock records even when a user
> simply closes their browser.
>

IMO:
You should not attempt to reinvent the wheel. In my experience, attempts to
create custom record-locking schemes usually fail. One of the reasons is
mentioned in your last paragraph here.

Let's look at what you're trying to prevent again:
Users A and B open a record for editing. User B finishes his changes first
and saves them. User A then saves his changes, overwriting B's changes.

There are two possible scenarios:
1. They were editing different fields
This is handled in one of these ways:
a) Only update the columns that the user changes. This means not using
recordsets for data modifications: Update statements only. In my apps, I use
an XML data island to store the record's original data on the client. When
the user clicks Save, I compare the data in the data island to the data in
the UI elements and build an XML Document containing only the data that was
changed. I pass that to the server-side page, which implements the changes*.
No overwrites occur. There are other ways to make this happen if you have an
aversion to XML.

b) If Users A and B have different functions which lead to their editing
only certain fields which the other user never edits, you can consider
putting these columns in different tables, so neither ever overwrites the
other.

2. They were editing the same fields
Your locking scheme will do nothing about this. Look at it this way:

You go to a lot of time and trouble to create a custom record-locking scheme
that somehow works. User A opens a record for editing. User B is
successfully locked out. User A makes his changes and saves. User B
immediately opens the record, makes his changes, and saves them, overwriting
User A's changes! You haven't gained anything here!

HTH,
Bob Barrows
*In a couple of my apps, I included criteria in my update statement to cause
it to update only data that had not been changed by another user:
update table
set col2 = <new data>
where <record id criteria>
AND col2 = <original data>

If no records are affected, I notify the user that the data has been changed
and ask if he wants to overwrite those changes.