Simple transaction question

• BillyWilly

  SSCrazy

  Points: 2304

  More actions

  September 3, 2004 at 7:04 am

  #60208

  Hi-

  I have an OK understanding of the various ISOLATION levels in SQL Server. However, I do have a question.

  What's the difference between these 2 Stored Procedure statements.

  SET ISOLATION LEVEL SERIALIZEABLE

  UPDATE MyTable Set Column1 = 'Updated'

  -----------------------------------------

  SET ISOLATION LEVEL SERIALIZEABLE

  BEGIN TRANSACTION

  UPDATE MyTable Set Column1 = 'Updated'

  COMMIT TRANSACTION

  -----------------------------------------

  Essentially, do I need to explicitly begin and commit transactions once I've set the isolation level?

  Thanks - B

• gbn

  SSCertifiable

  Points: 6379

  More actions

  September 6, 2004 at 3:31 am

  #521840

  In this case, nothing, because in the first the UPDATE statement is a transaction itself.

  No, you only need explicit begin and commit if you have a mult-statement "unit of work"

  Eg

  SET XACTABORT ON

  BEGIN TRAN

  IF (SELECT Balance FROM CurrentAccount) >= xferamount

  BEGIN

  UPDATE CurrentAccount Set Balance = Balance - @xferamount

  UPDATE SavingsAccount Set Balance = Balance + @xferamount

  END

  COMMIT TRAN

  Incresing isolation level here will prevent all reads until the entire statement has ran