Optimistic Concurrency Help

[Jack]

Hi,

I am trying to design optimistic concurrency in my application. The
application connects to a MS SQL server and is 3 to 5 user concurrent
application. The application works with one record at a time (ranges from 8
to 12 tables) and if the update fails everything is rolledback via the
transaction object. So, if the record is being updated by another user, I do
not want to update pieces of the data that is not locked (could happen). I
was thinking about implementing on the main table (similiar to Order table
in Northwind) either a timestamp column or datetime column which would be
used to test before the update. Is this a good approach? I rather not have
a column in each table because I do not want to update pieces, I want to
update the whole record or nothing at all. I know I could do this rather
easily by using pestimistic concurrency however I want a scalable solution
and I do not want to keep the connection open. Any suggestions?

Thanks

 

[William Ryan eMVP]

Hi,

I am trying to design optimistic concurrency in my application. The
application connects to a MS SQL server and is 3 to 5 user concurrent
application. The application works with one record at a time (ranges from 8
to 12 tables) and if the update fails everything is rolledback via the
transaction object. So, if the record is being updated by another user, I do
not want to update pieces of the data that is not locked (could happen).

None of it should be locked. You have to test to see if the original values
have been modified, but the whole notion of disconnected datasets is that
they don't lock the record on the db side.

I

was thinking about implementing on the main table (similiar to Order table
in Northwind) either a timestamp column or datetime column which would be
used to test before the update. Is this a good approach?

It definitely can be.
I rather not have

a column in each table because I do not want to update pieces, I want to
update the whole record or nothing at all.

The Optimistic concurrency is for you. All you need to do is compare each
value, not just the key.
I know I could do this rather

easily by using pestimistic concurrency however I want a scalable solution
and I do not want to keep the connection open. Any suggestions?

Thanks
HTH,

Bill

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[Jack]

Is the best approach to compare each value? I rather not have to look at
each table but rather look at the main record to see if it has been updated
since I retrieved it last. If it has then flag the user and take some
action and if it not then update the db.

Thanks

 

[William Ryan eMVP]

Optimistic concurrency, if you use the Configuration wizard, checks the
original value of each value and compares it against the db. If anything's
changed you have an issue. If you use pessimistic concurrency, it just uses
the Key field. You can also use a TimeStamp which I personally prefer but
many people don't like b/c a bunch of reasons most of which aren't terribly
important. http://www.15seconds.com/issue/030604.htm

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/