Best practice Multiuser Program - locking

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?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"aaapaul" <lvpaul@gmx.net> wrote in message
news:1167894539.379388.293200@v33g2000cwv.googlegroups.com...
> 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
>

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?

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"aaapaul" <lvpaul@gmx.net> wrote in message
news:1167914706.233869.244680@11g2000cwr.googlegroups.com...
> Yes Miha !
>
> If the worker goes to lunch, nobody should be able to edit the datarow.
>
> Paul
>

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.

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"aaapaul" <lvpaul@gmx.net> wrote in message
news:1167920272.363350.299370@42g2000cwt.googlegroups.com...
>I man from the IT will change the field locked from true to false.
>
>
> paul
>

aaapaul wrote:
> I man from the IT will change the field locked from true to false.

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.

--
Sean

Thanks
Paul

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.
-------------------------------
"aaapaul" <lvpaul@gmx.net> wrote in message
news:1167920272.363350.299370@42g2000cwt.googlegroups.com...
>I man from the IT will change the field locked from true to false.
>
>
> paul
>

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

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:7BFA6486-FD00-4F29-86DF-E8AC9403F53C@microsoft.com...
> 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
>
>
> "aaapaul" wrote:
>
>> 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
>>
>>