hi,
all i need is to lock a row in a table for a while untile this record is updated.
 i am working under web environment that the user read the data then he update it with new entered data, between the two actions i need to lock that row ...
is that possible.


thanks

Ahmed Ali
Software Developer

__________________
Ahmed Ali
Senior Software Developer

By default SQL does the locking for you. There are of course different lock levels and performance issues you make want to consider

I am curious as to why would you want the user of your application to lock a particular row, in the event the user opens the record and does not updated that will escalated other locks; page, table and db locks if others have to update information within that table.

But anyhow books on line has information on row level lock works. and this article gives good information on how to accomplish your task.

http://www.sql-server-performance.co...ocks_hints.asp

what i mean about locking is to lock certain row for a while after read of data by a webform untill the update happen to this row i will then release the lock... this could be done if i made a new flag field to the table but i am looking for a SQl solution .
is SQL support that

Ahmed Ali
Software Developer

I think u r in wrong way!
Why do u wanna lock the record for a while?! First of all it waste many resources, Secondly not a recomended way.
If u wanna make a exclusive update on table do it in last moment, not all the time u show the record!
Another way is: store a variable like record's unique Number & control if its free to Edit/Show or not?!

HTH.

Always:),
Hovik Melkomian.

 I've never tried this, but does

SELECT au_lname FROM authors where (RecID = 123) WITH (HOLDLOCK)

and

SELECT au_lname FROM authors where (RecID = 123) WITH (NOLOCK)

work? If is does, how would you:
1. Manage when the User walks away without signing off
2. Look up the User's name who currently has control of the record

Andy

He's trying to prevent a race condition.. He doesn't want two people to open the same record and modify it.. Thereby overwriting the other changes.

EG:

User A opens
User B opens
User B Saves
User A Saves (User B's changes are overwritten)


It is probably best to have a "checkout" marker on the row that lists who has it checked out. And when someone else goes in to edit the row they are told it's already checked out by so and so and it's read only.



Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

P.S. - You should also use a timestamp and timeout the "lock" after a short time... or you end up with a problem of people leaving sessions without updating.. leaving the row's locked


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee