Re: Record Update Locking

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 11/09/04 

Date: Mon, 8 Nov 2004 22:58:48 -0600

Depends on what you want. Do you want two users to be able to see the row
at the same time? In other words, does any user who fetches the row get
access, and no other user can touch the row? Or do you want to only have
this be for updating? If you just want to block users who are making
modifications, and still let users read rows, you probably want to build
your own locking mechanism for updates. So when a user wants to update a
row, you write a row into your lock table (if it has not already been
locked) and then you unlock it as part of the modification.

On the other hand, the more common way of handing this sort of thing when
the likelyhood of two users making changes is low is to use an optimistic
locking mechanism, where you assume it is unlikely, then on every
modification you check to see if any other user has made changes. You do
this by checking a timestamp column, or all of the columns in the table, to
see if the data you fetched matches the data in the table. If it doesn't,
warn the user and show them how the data has changed. 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Borikoy" <Borikoy@discussions.microsoft.com> wrote in message 
news:A845BAE8-D318-4488-B038-06093BF8E165@microsoft.com...
> How to lock a record so that only one user can update at a time.I try to 
> test
> my database using two user accessing and editing same data at the same
> time.The last user who click the save button will be the one who can get 
> the
> updated data. How can I make the update record available into one user at 
> a
> time.
Loading