CODEWITHSUNDEEP
sqlsql-servert-sql
w0051977
w0051977

Reputation: 15817

TSQL not causing infinite loop

Please see the code below:

declare @crimeurn varchar(20)
DECLARE @finalresults TABLE (crime_urn varchar(20))
DECLARE @potentialresults TABLE (crime_urn varchar(20))

insert into @finalresults values ('1')    

DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults
OPEN finalresults_cursor 
FETCH NEXT FROM finalresults_cursor INTO @crimeurn

WHILE @@FETCH_STATUS = 0 
BEGIN 
    print @crimeurn  

    INSERT INTO @finalresults
    values ('2')

    FETCH NEXT FROM finalresults_cursor INTO @crimeurn
END 

select * from @finalresults --line 16

CLOSE finalresults_cursor 
DEALLOCATE finalresults_cursor

Line 16 displays 5137 or 12,342 rows in SQL studio manager (it randomly varies). I expected the TSQL to cause an infinite loop because there is an insert into the table variable on every iteration of the cursor.

Why does it not cause an infinite loop? i.e. why are there 5,137 or 12,342 rows returned.

Upvotes: 3

Views: 202

Answers (3)

Martin Smith
Martin Smith

Reputation: 453897

You are inserting into a heap.

A heap is unordered. There is no particular guarantee that the row will be inserted after the current row and picked up on the next fetch.

I made a slight amend to your test framework and added an IDENTITY column. In my case it got to row 592,353 before terminating.

As you can see from the results below this final row happened to be inserted on an earlier page in the file (jumped from 1623 to 184) so an allocation ordered scan starting from the penultimate row wouldn't find it.

enter image description here

Code to reproduce.

declare @crimeurn varchar(20)
DECLARE @finalresults TABLE (crime_urn varchar(20), ID int identity)
DECLARE @potentialresults TABLE (crime_urn varchar(20))

insert into @finalresults values ('1')    

DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults
OPEN finalresults_cursor 
FETCH NEXT FROM finalresults_cursor INTO @crimeurn

WHILE @@FETCH_STATUS = 0 
BEGIN 
    print @crimeurn  

    INSERT INTO @finalresults
    --OUTPUT INSERTED.ID
    values ('2')

    FETCH NEXT FROM finalresults_cursor INTO @crimeurn
END 

select *, sys.fn_PhysLocFormatter(%%physloc%%) from @finalresults --line 16
ORDER BY ID

CLOSE finalresults_cursor 
DEALLOCATE finalresults_cursor

Upvotes: 3

Sergiu
Sergiu

Reputation: 442

However, if you do not care or know the type of the cursor, you can use the @@CURSOR_ROWS inside your loop to do some "cursor" logic :) . Here is some documentation on the possible values the @@CURSOR_ROWS variable can have, depending on the cursor's type: .

Upvotes: 0

Bacon Bits
Bacon Bits

Reputation: 32220

Edit: The information below is wrong, but I've left it because that's how I believe it's supposed to work.

By default, cursors do not run in INSENSITIVE or STATIC mode. By default cursors are DYNAMIC and OPTIMISTIC. The documentation on cursors doesn't mention how dynamic cursors behave with respect to INSERTS. INSERT behavior appears to be undocumented.

You may be able to fix this with the SCROLL_LOCKS option, which guarantees order preservation.

Because the cursor's definition is fixed when you run

DECLARE finalresults_cursor CURSOR FOR
SELECT crime_urn FROM @finalresults

It's static after that point. Updating the table variable @finalresults doesn't change the cursor finalresults_cursor.

It's like this:

X = 10
Y = X
X = 20
PRINT X, Y

Outputs this:

20        10

Upvotes: 0

Related Questions