MS SQL 7 Stored proc serial or parallel execution

• Robi

  SSC Rookie

  Points: 31

  More actions

  May 13, 2003 at 11:57 am

  #159947

  Hi

  Does any one know if stored procs are executed in a serial or parallel fashion ?

  For example , I write a stored proc that

  a) Reads an int value from a table

  (single record table)

  b) Adds 1 to this value and updates the

  table with this new value

  c) Returns the incremented number.

  If two applications call the same stored proc

  simultanously , would I be guaranteed that

  only one instance of the stored proc would execute at a time , or are two instances active and executing simultaneously ?

  The reason I ask , I have an OLTP (POS) app that needs a unique slip number , and as there are multiple copies of the POS running

  I need to be sure each POS gets its own unique slip number.

  I do this at the moment with a

  BEGIN TRANSACTION

  SELECT slip FROM x WITH (TABLOCKX)

  (App increments number)

  UPDATE x SET slip = NewNumber from App

  COMMIT TRANSACTION

  But a stored proc may be more elegant ?.

  Regards

  Robi Kihm

  needs

• dgermundson

  Hall of Fame

  Points: 3175

  More actions

  May 13, 2003 at 9:31 pm

  #457059

  You should be wrapping things in a transaction so this doesn't occur. Like you do w/ begin and commit tran. Have you considered using Component Services COM+ applications to manage your transactions? Does your application have a business layer that is running on an application server that could use this?

  Darren

  
  Darren