Simple issue of locking and checking problem. Please advise

• Vladimir Sim

  Ten Centuries

  Points: 1310

  More actions

  November 16, 2005 at 12:29 am

  #95732

  Hi,

  I have this simple issue on SQL Server with ASP.NET on my

  web application.

  Here is my issue

  User A using a ID to lock some object in a table TBL. The locking

  is to prevent other user from using that same ID util user A have

  complete his task. So, if User B come in and found similar ID in

  table TBL, he will be probihited to proceed his task.

  However, problem surface when User A might have system crash or

  hang and other issue that might halt other user from using that

  same ID. I am thinking of having some expiry date timestamp in

  table TBL to let other user check the expiry time of that ID.

  However, I not sure how long the expiry timestamp should be

  for any ID as it depend on the task involve. The expiry timestamp

  will vary for different ID involve.

  I don't think using some background task to extend the expiry

  time stamp from ASP.NET is a good ideal as it increase the network traffic for that ID.

  Is there any good ideal or way to solve above issue that I facing using

  SQL Server alone. I don't think using job in SQL server will help

  as it will not know whether any of the user task is still active or

  the system is dead/halt.

  Thank you.

• Saravanan V

  SSC Enthusiast

  Points: 186

  More actions

  November 16, 2005 at 4:32 am

  #604144

  While locking u can identifiy the user session id of sql server. Then u can put the session id and lock details in an application collection. if the user B want's to access the locked record, u r checking the lock status of the particular object. If it is locked, why don't u check the user A's user session id of sql server that, whethere that is active or not? if the session is expired (i mean dead or closed) u can force the system to unlock the object and open for user B. Try this logic.

  all the best.

  ---

  Kindest Regards,

  Saravanan V
  geocities.com

• ron k

  SSCertifiable

  Points: 6103

  More actions

  November 17, 2005 at 9:50 am

  #604469

  Similar to the answer above, I record the spid and login_time from master..sysprocesses when recording the locking info.

  Anytime I create a new lock I first delete all lock entries where the spid/login_time no longer exist in master..sysprocesses.

  ron