Locking

• eko indriyawan

  Hall of Fame

  Points: 3316

  More actions

  September 28, 2005 at 6:49 am

  #165349

  i have a SP

   

  if in my server has many client. When all of client use it, the first client call SP, we know that SP still doing their task to processing data. And then, at the same time, another client call SP for doing their task. My question is how to defend the second client and another in order waiting until SP that called by first client finished the task ????

   

  thanxs for you.........

   

  sorry my english is so bad.....

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  September 28, 2005 at 6:56 am

  #593639

  You could/can do may different things

  1. Create a master table that tracks stored-procedures and their related activities.  If someone is using it then the requesting SP needs to go into a LOOP and keep checking for (n) times every (y) seconds until it gives up and notifies requestor.  May be a few tables (SPs, Relationships, JobControl) manually maintained.
  2. Use an UPDLOCK to force everyone into a QUEUE.  SQL will manage this for you (see BOL for more information).
  3. Take away "real-time" batch updates (may or may not be able to).  Have everything go to a "staging" area and process the requests every (n) minutes/hours, whenever.

   

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• eko indriyawan

  Hall of Fame

  Points: 3316

  More actions

  September 28, 2005 at 7:15 am

  #593646

  i'm so sorry about your explanation......

  i still confuse...

   

  can you give me example......

  please !!!

  thanxs you very much for AJ Ahrens

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  September 29, 2005 at 11:28 am

  #594121

  Locking is SQL Server's way of making sure that the data maintains it's 'integrity' - only one person/process can update one thing at a time. Locking is not an issue if tranasctions/stored procedures are designed to perform things efficiently (quickly). It seems in this post 'integrity' and 'concurrency' are being mixed up. Locking is normal, excessive locking is not. Queueing things or using interim processing tables are design methods to achieve greater 'concurrency' and overall system throughput.

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."