Record locking...

Hi Experts,
Here's the scenario of the case...
User1 does a retrieve of information, with the intention of updating it
later. At the same time, User2 does a retrieve of information, with the
intention of updating it later. User1 updates some of the row on the web
server, and then calls an update stored procedure.Then user2 updates some of
the row on the web server, and then calls an update stored procedure.
Some of user1s updates are lost because user2 had "stale" data.
So what a lot of applications do is to implement explicit locking, so that
when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
update it later". The rule is in that case that a locked record cannot also
be locked by someone else
and update can only be called if the record was locked by that user.
We can of course add a column to every table that has to support this
"locking", but I was wondering if SQL Serversystems support some kind of
"lock this record please"?Here's an old archive on this type of issue:
http://tinyurl.com/5qevd
-oj
"Lia" <Lia@.discussions.microsoft.com> wrote in message
news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
> Hi Experts,
> Here's the scenario of the case...
> User1 does a retrieve of information, with the intention of updating it
> later. At the same time, User2 does a retrieve of information, with the
> intention of updating it later. User1 updates some of the row on the web
> server, and then calls an update stored procedure.Then user2 updates some
> of
> the row on the web server, and then calls an update stored procedure.
> Some of user1s updates are lost because user2 had "stale" data.
> So what a lot of applications do is to implement explicit locking, so that
> when user1 did the retrieve, he had to say "I want to lock it cuz Im gonna
> update it later". The rule is in that case that a locked record cannot
> also
> be locked by someone else
> and update can only be called if the record was locked by that user.
> We can of course add a column to every table that has to support this
> "locking", but I was wondering if SQL Serversystems support some kind of
> "lock this record please"?|||Hi Experts,
What is usually the system? Is it the person who updates gets the priority
or the person who retrieves (for update) gets a priority.
I have been debating on this for some time and after reading a few books i
got the impression that LOCKING records was the practice in the olden days
and in the current situation ( adLockOptimistic ) the person who clicks
update first get a priority and the other person gets an ERROR! Which can be
trapped using ON Error GOTO statement
Please clarify and help me resolve this issue.
Thanks
Manish
"oj" wrote:

> Here's an old archive on this type of issue:
> http://tinyurl.com/5qevd
>
> --
> -oj
>
> "Lia" <Lia@.discussions.microsoft.com> wrote in message
> news:9A1DD01A-FA17-4785-B6F6-3869907C9DE9@.microsoft.com...
>
>|||Hi Manish,
Thanks for your reply on my post. :-) The system will supposed to give the
priority to person who retrieves(for update) in such a way that upon
retrieving, the record will be locked. The lock will then be released after
updating the record or canceling the transaction. And if the other person
select the same record that intend to update it also, message will be given
to inform the person that the record can only be viewed and cannot be update
d
because it is locked for update by another user.
Your suggestions and comments will be highly appreciated.
Thanks,
Lia
"Manish Sawjiani" wrote:
> Hi Experts,
> What is usually the system? Is it the person who updates gets the priority
> or the person who retrieves (for update) gets a priority.
> I have been debating on this for some time and after reading a few books i
> got the impression that LOCKING records was the practice in the olden days
> and in the current situation ( adLockOptimistic ) the person who clicks
> update first get a priority and the other person gets an ERROR! Which can
be
> trapped using ON Error GOTO statement
> Please clarify and help me resolve this issue.
> Thanks
> Manish
> "oj" wrote:
>