Transaction isolation level question

• Russell Smith-401965

  Grasshopper

  Points: 16

  More actions

  November 23, 2007 at 7:10 am

  #377178

  Hi,

  If I have a stored procedure with returns a result set and I want to set the isolation level to READ UNCOMMITTED, is it necessary to wrap the contents of the stored procedure inside a transaction block?

  In other words:

  CREATE PROCEDURE myProc as

  begin

  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  BEGIN TRANSACTION

  SELECT * FROM .....

  COMMIT

  end

  is the BEGIN TRANSACTION and COMMIT necessary in order to set the isolation level in the above procedure?

• Johan Bijnens

  SSC Guru

  Points: 135603

  More actions

  November 23, 2007 at 8:09 am

  #753646

  no, that is not needed.

  in stead of the "set transaction isolation level" you

  might use the "with (nolock)" table hint if you don't want

  the scope to be proc level.

  Anyways, document why you are using this !

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data and code to get the best help
  

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Who am I ? Sometimes this is me but most of the time this is me