Best practice Multiuser Program - locking

I have to develop an application with Visual Studio .NET 2005 and SQL
Server 2005 (ADO.NET).

There is a sql-server table called "Orders" with primary key ordernr.

If a user calls "Edit Order Nr XY" in the program the datarow xy should
be locked.
When the user pushes the button "store", the datarow xy should be
updated and unlocked.

Due to the locking every outer user who wants to "Edit Order Nr XY"
should get the message "not possible".

How can this solved ?

My idea is this:

Two new fileds in the table (locked: boolean; lockedby: varchar)

1) READ for editing
SQL
UPDATE orders SET locked = true where (ordernr = 'xy') and (locked =
'false')
IF the return value = 1 then read datarow else "not possible, locked by
xy"

2) UPDATE
Update fileds and locked = false

Who has experience in solving such problems?

Many thanks for your help in advance.

Best regards
aaapaul

Forget locking and use optimistic concurrency.
Locking is evil - what if somebody starts modifying record, locks computer,
goes to lunch where he/she eats a hamburger too much and gets into a comma?

Show quoteHide quote

Yes Miha !

If the worker goes to lunch, nobody should be able to edit the datarow.

Paul

And if he/she doesn't return for few days and row has to be commited?

Show quoteHide quote

I man from the IT will change the field locked from true to false.


paul

I guess your original solution might work then - using a flag.

Show quoteHide quote

Thanks
Paul

aaapaul wrote:
You know, I think you're missing the point that Miha Markic is trying to
make, so let's use an analogy.

Say you think some day that it would be fun to go down to a local
programmers convention and have the security guy at the door kick you
square in the nuts.  You've heard of other developers getting kicked in
the nuts so you figure it must be a cool thing to have done, but you're
not really too sure what the best way is to go about it, so, shaking
with anticipation, you open up your favorite newsgroup client to ask the
professionals, "What's the best way for me to get kicked in the nuts?"

A few hours of burning anxiety pass, I mean after all, you really want
to get kicked in the nuts pretty bad!  Finally, blood flowing with
anticipation of fellow developers who can pass on their words of wisdom,
you receive your first reply.  And what's this?  Somebody is actually
against getting kicked square in the nuts?  How could he possibly
suggest that there could be undesired side affects?  This can't be!
What does this guy know?

Still determined not to be persuaded otherwise, you make numerous
attempts to justify being kicked in the nuts.  This bastard!!?  He just
doesn't understand how cool it is to get kicked in the nuts like the
other developers have.  I'll show him.

At last, after failing to convince you that it just isn't a good idea,
you head off to that programmers convention to have the task committed
your own way.  I just hope you realize before you get there that it's
really not fun to get kicked in the nuts.

You could add a timestamp to the record for when it was locked,
and if it has been a certain amount of time, and someone else
wants to lock the record, let them.

Robin S.
-------------------------------
Show quoteHide quote

aaapaul,

If I were you I would do a google search on pessimistic locking or
pessimistic concurrency using ADO.Net and SQL Server.

Kerry Moorman


Show quoteHide quote

If I were him I would avoid pessimistic locking at all costs :-)

Show quoteHide quote

This is a computer science 101 question and has been discussed here (and in
all of my books) a bazillion times.
The answer is: it all depends.
Typically, a person new to shared data processing will want to use the "hold
the record while I'm working on it" approach. This can work, but it prevents
others from accessing the row or the table page that contains the row. It
can also lock the entire table depending on how the query (and lock) is
made. Generally, when one uses this approach it's done against one and only
one row within an atomic routine that locks the row, makes the changes and
unlocks the row in a single operation. It can be done from a client, but if
you take this approach you need to setup a watchdog timer that releases the
lock after a given length of time. This approach also assumes that the
client continues to run and can in fact reconnect and release the lock. As
you can see, there are a wealth of issues associated with this technique.
This is called "pessimistic" locking.

"Optimistic" locking assumes that the application and database design is
built to prevent two clients from accessing/updating the same data at the
same time. It builds concurrency into the database instead of building a
complex infrastructure to position the ambulances around the intersections
where concurrency collisions can occur.
This means you fetch a row (or rows), make your changes and test to see if
others have changed the rows as you try to update. Yes, this can be done
with a Timestamp column or with other (far cruder) methods. Again, every one
of my books discuss how to implement both techniques in great detail.

hth


Show quoteHide quote

Hi, All:

    Don't use database server side pessimistic locking as others described!
That is a bad approach. In my opinon, the best practice Multiuser Program -
locking is to use real-time notification.

    When a user wants to lock records or a cells, a user can simply send a
message to inform all of other connected users to prevent other users from
updating these locked records or a cells.

    Our SocketPro at www.udaparts.com has a built-in notification service at
http://www.udaparts.com/document/articles/chatservice.htm

    Inside the package, there are a few other samples written from C++,
VB.NET, C# to show you how to use notification service for your  locking.


Regards


Show quoteHide quote

Paul,

Your question. Best practise MultiUser Program  <---> locking don't for most
of us not fit together.

We have seen to much trouble with this technique from far in the previous
century.

But it is a technique and can in some situations be the best of the worse.

Cor

Thanks all (especially for the nice story)!

Perhaps "locking" is not the right expression, what I mean.

My aim is to avoid lost updates.

Sample:

User1 wants to increase the value x 20.
User2 wants to reduce the valux x with 5.

The original value in the database is:
500
1) User1 reads the value 500
2) User2 reads the value 500
3) User1 changes the value to 520
4) User2 changes the value to 495
5) User 1 stores the datarow to 520
6) User 2 stores the datarow to 495

The first update (20) is lost.

So I need a kind of locking in the business logic of my application.
Its not necessary to lock the recordset directly.
But the information in the database is necessary, that the recordset is
in process.

Paul

As discussed earlier: you might consider optimistic concurrency.
It would kick in at step 4) and will notify User2 that the row has changed
since he read it.
You might give him/her various options: override, merge, read again, etc.
So nothing gets lost.

Show quoteHide quote

So, at the end of your sample, what is the final state you would want to persist the data?
Is 495 not correct?

So what needs to happen is you need to communicate to User 1 that although he wishes the value
to be 520 User 2 just changed it to 495. User 1 say "oh yeah!, I'll resubmit my change to 520".
User 2 checks later in the day and sees that User 1 has set his 495 value back to 520,
he changes it back to 495.
And so on, and so on,  forever and ever.




Show quoteHide quote

Adding records through batch Webservice call
Concurrency Violation
Error when connecting to SQLExpress
general function to insert a DATE correctly
VB.net and ASP.Net Typed Datasets