Does a script fetch the next item in the cursor if the transaction was rolled back based on this script setup?

user_4838216 0
Viewed 67

I'm not too familiar with cursors, but I just need to know one relatively simple thing. Take a look at the structure of the script below and note where the cursor is instantiated and where it is closed/deallocated. If the script deadlocks where I've written /* most of the code here */ and the transaction is rolled back, then reattempted, what happens when the script tries to fetch next? Since the execution never reached the close/deallocate cursor lines, I feel as though on the second attempt the the cursor would fetch the second row. Note that I'm not claiming that this is correctly written - I feel as though the issue I have is due to the cursor being deallocated AFTER committing the transaction.

declare LPCursor cursor for 
/*
...
*/

while (@deadlockretries <= @Maxlockretries)
begin

  begin try
    begin transaction

      fetch next from LPCursor into @var1, @var2, @var3
      while (@@fetch_status = 0)
      begin

        /* most of the code here */

      end 

    commit transaction

    close LPCursor
    deallocate LPCursor

  end try

  begin catch

    if (error_number() = 1205)
    begin

      if xact_state() <> 0 
      begin

        rollback transaction

      end

    end

  end catch

end
sql-server-2008-r2 tsql sql
1 Answers

It will fetch the next one, but I assume you know that doing it this way you would have a block of "processing" that would get missed because you rolled it back right in the middle. Either way here is your code modified to show you how it would move forward:

CREATE TABLE #tmp(VAL1 varchar(10), VAL2 varchar(10), VAL3 varchar(10))
INSERT INTO #tmp VALUES('val1_1','val1_2','val1_3')
INSERT INTO #tmp VALUES('val2_1','val2_2','val2_3')
INSERT INTO #tmp VALUES('val3_1','val3_2','val3_3')
INSERT INTO #tmp VALUES('val4_1','val4_2','val4_3')
INSERT INTO #tmp VALUES('val5_1','val5_2','val5_3')
INSERT INTO #tmp VALUES('val6_1','val6_2','val6_3')
INSERT INTO #tmp VALUES('val7_1','val7_2','val7_3')


DECLARE @deadlockretries int = 0
declare @Maxlockretries int = 3

declare @var1 varchar(10)
declare @var2 varchar(10)
declare @var3 varchar(10)

DECLARE LPCursor cursor for SELECT VAL1, val2, val3 from #tmp
open LPCursor
while (@deadlockretries <= @Maxlockretries)
begin
    PRINT 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'

  begin try
    begin transaction

      fetch next from LPCursor into @var1, @var2, @var3
      -- print @var1 + @var2 + @var3
      while (@@fetch_status = 0)
      begin
      print @var1 + @var2 + @var3
        /* most of the code here */
        select 1/0
        fetch next from LPCursor into @var1, @var2, @var3
    end 

    commit transaction
    SET @deadlockretries = @Maxlockretries
    close LPCursor
    deallocate LPCursor

  end try

  begin catch
  PRINT 'ERROR'
  print error_number()
    if (error_number() = 8134)
    begin

      if xact_state() <> 0 
      begin
      PRINT 'ROLLBACK'
        rollback transaction
      end
    end
  end catch
  PRINT 'END'
  SET @deadlockretries += 1
end

IF CURSOR_STATUS('global','LPCursor')>=-1
BEGIN
 DEALLOCATE LPCursor
END
user_81262190
Related