Impact on Begin tran/commit tran inside a loop

• Roust_m

  SSCoach

  Points: 17410

  More actions

  January 15, 2017 at 7:33 pm

  #320584

  Hi,

  I am reviewing a stored procedure,which is causing blocking.  I found it has begin tran/commit tran inside a while loop (please see the code below).

  Just was wondering if it would have a bad impact?  I don't think this transaction is necessary here.

  Any ideas?

  Thanks.

  ALTER PROCEDURE [dbo].[MySP] (@NumberOfRecords BIGINT=10000)
  AS
  BEGIN
  SET NOCOUNT ON;
  DECLARE @LastDateTimeToPurge datetime
  DECLARE @RowsToMoveandDeleteCount INT
  SET @LastDateTimeToPurge = (GETDATE() - 7)
  SET @RowsToMoveandDeleteCount = 1
  While @RowsToMoveandDeleteCount > 0
  BEGIN
  BEGIN TRANSACTION Purge
  -- Move and Purge Data
  DELETE TOP (@NumberOfRecords) FROM [dbo].[MyTable]
  OUTPUT deleted.* INTO MyTable2
  WHERE MyDate <= @LastDateTimeToPurge
  Set @RowsToMoveandDeleteCount = @@ROWCOUNT
  COMMIT TRANSACTION Purge
  END
  IF @@TRANCOUNT > 0 
   ROLLBACK TRANSACTION 
  END

• happycat59

  One Orange Chip

  Points: 29389

  More actions

  January 15, 2017 at 8:23 pm

  #1922853

  Roust_m - Sunday, January 15, 2017 7:33 PM

  Hi,

  I am reviewing a stored procedure,which is causing blocking.  I found it has begin tran/commit tran inside a while loop (please see the code below).

  Just was wondering if it would have a bad impact?  I don't think this transaction is necessary here.

  Any ideas?

  Thanks.

  ALTER PROCEDURE [dbo].[MySP] (@NumberOfRecords BIGINT=10000)
  AS
  BEGIN
  SET NOCOUNT ON;
  DECLARE @LastDateTimeToPurge datetime
  DECLARE @RowsToMoveandDeleteCount INT
  SET @LastDateTimeToPurge = (GETDATE() - 7)
  SET @RowsToMoveandDeleteCount = 1
  While @RowsToMoveandDeleteCount > 0
  BEGIN
  BEGIN TRANSACTION Purge
  -- Move and Purge Data
  DELETE TOP (@NumberOfRecords) FROM [dbo].[MyTable]
  OUTPUT deleted.* INTO MyTable2
  WHERE MyDate <= @LastDateTimeToPurge
  Set @RowsToMoveandDeleteCount = @@ROWCOUNT
  COMMIT TRANSACTION Purge
  END
  IF @@TRANCOUNT > 0 
   ROLLBACK TRANSACTION 
  END

  In this example, the BEGIN TRAN/COMMIT TRAN are not likely to be involved with you issue at all.  The DELETE statement is the only statement that is affected by the transaction and a single statement is always in a transaction (either explicitly as in your example or implicitly if BEGIN/COMMIT TRAN is not used). 
  If this code is causing a problem it is more likely to be related to indexes (on MyDate) or a lack of indexes and also to the number of records that are being deleted inside each iteration of the loop