Help needed - Row Locking without optimistic concurrency

  • Thread starter Thread starter k3
  • Start date Start date
K

k3

Hi,

I know that ADO.NET is using a disconnected method of handling data and use
of optimistic concurrency is encouraged. But in certain cases such as
modifying records in applications like customer support, medical records in
a hospital, and other similar scenarios we need to prevent other ppl from
being able to modify the record if it has been and currently is open by
another user. The 2nd or subsequent user may only view the record and has no
possibility to submit changes until the record has been closed by the
initial person who had access to it. So is there any way of making this
happen using ADO.NET and how can we detect that a row is locked if we plan
on sending a read-only warning to other user wishing to modify the opened
record?

This optimistic concurrency thing is mentioned in many text books and is
always geared towards handling situations where multiple ppl attempt to
commit changes... Isn't it just logical that in the situations mentioned
above to allow read-only access if a record is opened? I know it's not
scalable since the user that has the initial hold on the record can forget
to close the record for some long period of time, but can't we just
implement a timeout and release the record if some arbitrary amount of time
has elapsed without activity?

Regards,
Kam
 
Hi,

You can implement one IsDirty function. There are some
different solutions for this action.

The easiest way is it to add a date when the data was
last updated. When you press the update button in your
application you check if this date is newer then yours.
If it is then someone has updated some of the data. Then
you can return to the form page with the new data
instead. Or proceed by checking for changed fields.

You can also make a copy of the data container you are
using. Dataset, Entity classes...??? When you want to
update the data you can use the copy and check if the
data are the same as when you retrieved it from your data
source. If true then you can update the data source with
the other copy (the changed one). But remember if you
don't use transactions someone can modify the data the
right after you check if it has been changed.

There are lots of other ways too.


JN
NSQUARED2
 
Skip my Anwser, wasn't reading all your text...
Lunch time.
JN
 
You can create a pessimistic lock using a Transaction with RepeatableRead
enabled. I describe this techinque in my "Doing the Impossible with ADO.NET"
session. It's going to be offered next month at the SQL Connections
conference in Palm Springs. It's also published in the subscriber section of
Hardcore Visual Basic (see http://www.betav.com/pinnacle.htm)

hth

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Add a CheckOut datetime column, normally NULL - not checked out, otherwise
the date and time of checkout. If the (Now - CheckOut) > some threshhold,
then allow cancellation if someone else requests same info, and the original
lock holder has the application tell them that their lock has Expired & they
need to refresh.
 
Thanks!! That's actually very good for ppl that forget to close a record.


| Add a CheckOut datetime column, normally NULL - not checked out, otherwise
| the date and time of checkout. If the (Now - CheckOut) > some threshhold,
| then allow cancellation if someone else requests same info, and the
original
| lock holder has the application tell them that their lock has Expired &
they
| need to refresh.
|
|
|
|
|
|
| | > You can create a pessimistic lock using a Transaction with
RepeatableRead
| > enabled. I describe this techinque in my "Doing the Impossible with
| ADO.NET"
| > session. It's going to be offered next month at the SQL Connections
| > conference in Palm Springs. It's also published in the subscriber
section
| of
| > Hardcore Visual Basic (see http://www.betav.com/pinnacle.htm)
| >
| > hth
| >
| > --
| > ____________________________________
| > Bill Vaughn
| > www.betav.com
| > Please reply only to the newsgroup so that others can benefit.
| > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| > __________________________________
| > | > > Hi,
| > >
| > > I know that ADO.NET is using a disconnected method of handling data
and
| > use
| > > of optimistic concurrency is encouraged. But in certain cases such as
| > > modifying records in applications like customer support, medical
records
| > in
| > > a hospital, and other similar scenarios we need to prevent other ppl
| from
| > > being able to modify the record if it has been and currently is open
by
| > > another user. The 2nd or subsequent user may only view the record and
| has
| > no
| > > possibility to submit changes until the record has been closed by the
| > > initial person who had access to it. So is there any way of making
this
| > > happen using ADO.NET and how can we detect that a row is locked if we
| plan
| > > on sending a read-only warning to other user wishing to modify the
| opened
| > > record?
| > >
| > > This optimistic concurrency thing is mentioned in many text books and
is
| > > always geared towards handling situations where multiple ppl attempt
to
| > > commit changes... Isn't it just logical that in the situations
mentioned
| > > above to allow read-only access if a record is opened? I know it's not
| > > scalable since the user that has the initial hold on the record can
| forget
| > > to close the record for some long period of time, but can't we just
| > > implement a timeout and release the record if some arbitrary amount of
| > time
| > > has elapsed without activity?
| > >
| > > Regards,
| > > Kam
| > >
| > >
| >
| >
|
|
 
Back
Top