ADO.Net: how to lock records for update? options?

[feng]

Hi,

I know ADO.Net recommand using disconnected update
(optimistic concurrency) for good reasons, but it's just
not an option for us. 99% of our clients would rather
seeing "record locked" kind of message up front when they
load records, than being told "updating failed" after they
spend all the time entering their data.

So here I am spending a lot of time trying to find a good
way to do the "read with lock" type of opration. I know
that I can do some thing like that at query level if I am
running some late version of SQL server, but what if I
can't use those DB specific features? Is there a
recommanded way of doing this at ADO.Net level"? What
about ADO.Net's transaction?

If someone can point me to a right direction that would be
highly appreciated!

Thanks

Feng

[Herfried K. Wagner [MVP]]

* "feng" <an*******@discussions.microsoft.com> scripsit:

I know ADO.Net recommand using disconnected update
(optimistic concurrency) for good reasons, but it's just

You will more likely get an answer here:

<URL:news://news.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<URL:http://msdn.microsoft.com/newsgroups/?dg=microsoft.public.dotnet.framework.adonet>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

[Jay B. Harlow [MVP - Outlook]]

feng,
In addition to Herfried's comments.

You may want to look at the Pessimistic Offline Lock pattern.

http://www.martinfowler.com/eaaCatal...flineLock.html

For details on the Pessimistic Offline Lock pattern and other useful
patterns see Martin Fowler's book "Patterns of Enterprise Application
Architecture" from Addison Wesley.

http://www.martinfowler.com/books.html#eaa

Note: Fowler's book in C# & Java, however it should be easily converted to
VB.NET.

Hope this helps
Jay

"feng" <an*******@discussions.microsoft.com> wrote in message
news:1e*****************************@phx.gbl...

Hi,

I know ADO.Net recommand using disconnected update
(optimistic concurrency) for good reasons, but it's just
not an option for us. 99% of our clients would rather
seeing "record locked" kind of message up front when they
load records, than being told "updating failed" after they
spend all the time entering their data.

So here I am spending a lot of time trying to find a good
way to do the "read with lock" type of opration. I know
that I can do some thing like that at query level if I am
running some late version of SQL server, but what if I
can't use those DB specific features? Is there a
recommanded way of doing this at ADO.Net level"? What
about ADO.Net's transaction?

If someone can point me to a right direction that would be
highly appreciated!

Thanks

Feng

[Cor Ligthert]

Hi Feng,

Please do not multipost, you can crosspost, to dotNet groups, you have sand
this message some minutes before this to the ADONET newsgroup (a good place
for this question by the way).

You can without any problem, when the subject is proper for that, crosspost
to dotNet newsgroups, that means one message to more newsgroups in one time.

In addition to the others, have as well special for this subject a look what
William (Bill) Vaughn is writting about it.

He has a website and there you can find a link to his book and articles. He
has a basicly a different idea than you about pesimistic and optimistic
concurrency (what I share with him), however it is better when you read it
yourself. He is with David Sceppa one of the major bookwritters about
AdoNet.

http://www.betav.com/

I hope it gives you some idea's

Cor

[Sahil Malik]

Ahh .. so you've been stung by the "User designing the application" bee.
You've gotta prevent that from happening my man. A too smart for his own
good user is actually dangerous - my pet peeve about Extreme Programming.
(No my pet peeve is keyboard sharing .. whats next picking each other's
noses as we program?)

My first recourse would be to not using pessimistic locking, since that is
ultra unscaleable. Not only that, in an enterprise app, you would be keeping
your DBAs awfully busy clearing out locks for everyone. Might I add, you can
avoid that to some extent by better table design .. but you will be safe,
not immune. You could however implement concurrency control rather well in
..NET. (Ask me if thats an option and I'd be glad to tell you how). You can
always show a message box that "someone else updated ur row" .. (so it won't
exactly be like "Record is locked").

My second recourse is to try and tell them that "Hey I don't tell you to do
your job, so let me do mine". So if this were a financial application, walk
upto the CFO and say "Hey moron, I never asked you to file for a shelf
registration ... So why are you asking me to build in pessimistic locking?".
If it helps take a few pictures of the CFO fooling around with his secretary
with a hidden cam.

My third recourse would be to threaten suicide (just kidding don't do it).

My final recourse would be to implement pessimistic locking as the two MVPs
before me replied.

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/

"feng" <an*******@discussions.microsoft.com> wrote in message
news:1e*****************************@phx.gbl...

Hi,

I know ADO.Net recommand using disconnected update
(optimistic concurrency) for good reasons, but it's just
not an option for us. 99% of our clients would rather
seeing "record locked" kind of message up front when they
load records, than being told "updating failed" after they
spend all the time entering their data.

So here I am spending a lot of time trying to find a good
way to do the "read with lock" type of opration. I know
that I can do some thing like that at query level if I am
running some late version of SQL server, but what if I
can't use those DB specific features? Is there a
recommanded way of doing this at ADO.Net level"? What
about ADO.Net's transaction?

If someone can point me to a right direction that would be
highly appreciated!

Thanks

Feng