How to lock a record ?

[stanja]

Hello ,


How can i prevent that more users change the same record at the same time ?
The first person that opens the record may change it , the next ones may only read it or change it after the first one has closed and the data output/view is been refreshed.
I'm using asp to show the data on a page.

 

[hmckillop]

You can do it multiple ways, have a look at the Microsoft
help on "locking hints"

http://msdn.microsoft.com/library/en-us/architec/8_ar_sa2_2sit.asp

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_7a_3hdf.asp

 

[NoCoolHandle]

Be fing carefull with locking hints! they have a tendency to prevent reads!!!

For instance if you use pessimistic locking in a client recordset (ado) the as soon as one user navigates to the record it is locked from all others.. THis has strange rippledown effects. If someone was trying to access the record but it was locked the "cursor" will be locked at that record (can't move past it) unless their query uses readpast or readuncommited or nolock... Then you have a whole new set of problems.....

When I have multiuser situations I tend to place a timestamp col in the table and compare timestamps on update. If they are different someone else changed part of the row while it was open and the other user was messing around trying to make a decision.. Then I work out which col was changed in the row... If it was the same col, raise a conflict error if not just update that col.. All this logic can be contained in a stored proc and next to 0 locks with many many users..


Just my 1c

Rob
PS be carefull with issolation levels also.. they have a more global impact.. (I would recomend you try a timestamp col - more work for you or your developers, but you will find it has less impact on your users and provides much better performance wiht next to 0 concurrency issues.)

 

[andreateh]

Hi NoCoolHandle,

I'm planing moving my VFP to MSSQL Server. But before i move would like need some advices from your guys.

In one the application required Pessimistic records locking ("Reserved Records").

Below is the field that use to reserved record:


field name type
locktim timestamp
lockuser Charater


In one of the entry program the user is allow to choose where the goods to store. When the user choose the locations. The record/s in the location table is first check with the "locktim" field. If the field is empty. then the record is available for others, else the timestamp will be compare "Maxlocktiem" (~40 mininute). If timestamp greater than maxlocktime the record will be available for the user.

When user successful select the location a new timestamp will be update to the locktim field. Within the "MaxlockTime" period this record is not available to others user.

Below is the algorithom is use :

1    IF not empty(locktim)
2       IF lockdt > maxlocktime
3         IF success Update the record time stamp
4           return .T.
5         else
6           return .F.
7         endif 
8       else
9         return .F.
10      endif 
11   endif
12   return .T.

When user save the records. It will compare lockuser with the current userid. The the userid change mean this record may be is lockdt >maxlocktim has been expire the maximum reserve time already. So a error handling will pop up to ask the the user for action.

But in VFP once the record is reserve by the user it is lock pessimistic, by a command "Rlock". Even line 2 code above will run but line 3 will always fail, if the user still never close the application. (sound like stupid.. but is request by user.)

Is MSSQL server able to lock record like VFP ? Or should i change the locking strategy (or ask user to change their mind) ?

One more question, will this frequently update the lockdt (timestamp col). Will slow down the server ? Because the time stap is update everytime the user make a selection on the location. Please kindly advice. Thanks.