Who Locked

• lalex

  Newbie

  Points: 2

  More actions

  August 6, 2003 at 10:42 am

  #159833

  Hi All,

  We use Delphi-5+Midas+ODBC - SQL Server 2000.

  We have a table EMPMASTER where primary key is

  EMPLOYEECODE.

  When one user - say USER1 takes details of an employee, we

  fetch using the Query

  SELECT * FROM EMPMASTER WITH (ROWLOCK) WHERE EMPLOYEECODE

  = '100'.

  Now if another user USER2 tries to retrieve the same

  record, he will get a timeout error . Thus we are able to

  prevent another user from taking this record.

  Now the requirement that we have is , we should be able to

  tell USER2 that USER1 has locked the record(100) , while

  we fetch the data using the above query.

  Can anybody suggest an optimised way of achieving this

  Any information on this topic is welcome

  Regards

  Alex

• mccork

  SSC-Insane

  Points: 22288

  More actions

  August 6, 2003 at 10:02 pm

  #468924

  Here's a possibility...

   
  
  select * from EMPMASTER  (xlock,rowlock,readpast) where EMPLOYEECODE = '100'
  
  IF @@ROWCOUNT = 0 AND EXISTS (select * from EMPMASTER (nolock) where EMPLOYEECODE = '100')
  
      RAISERROR('Record is locked by someone else',0,1)

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• lalex

  Newbie

  Points: 2

  More actions

  August 8, 2003 at 11:22 am

  #469226

  Hi,

  Thanks for the reply. But in this case we will not be able to tell who has locked the record