Question about strange cursor behavior - Sybase ASE 12.5.4

[Former Member]

Hi folks,

I have a stored procedure in Sybase ASE 12.5.4 presenting a strange behavior. Follow below some code to simulate the error.

/*----------------------------------------------------------------------*/

/* Create the table structure */

drop table tbx1

go

create table tbx1

(c1 int,

c2 int,

c3 char)

go

/* Create a CLUSTERED index NOT UNIQUE */

create clustered index idx1 on tbx1 (c1)

go

/* Populate the table with few records */

insert into tbx1 select 1, 1, 'N'

insert into tbx1 select 2, 1, 'N'

insert into tbx1 select 2, 2, 'N'

go

/* Test case #1 - The following code will not fetch the 3rd record */

declare c1 cursor for

select c1, c2 from tbx1 where c3 = 'N'

go

declare @c1 int, @c2 int

open c1

fetch c1 into @c1, @c2

while @@sqlstatus = 0

begin

  select @c1, @c2  -- View the current fetched row

  update tbx1 set c3 = 'N' where c1 = @c1 and c2 = @c2

  fetch c1 into @c1, @c2 

end

close c1

deallocate cursor c1

/*----------------------------------------------------------------------*/

/* Recreate the table structure */

drop table tbx1

go

create table tbx1

(c1 int,

c2 int,

c3 char)

go

create clustered index idx1 on tbx1 (c1)

go

/* Repopulate the table */

insert into tbx1 select 1, 1, 'N'

insert into tbx1 select 2, 1, 'N'

insert into tbx1 select 2, 2, 'N'

go

/* Test case #2 - The following code enters in an infinite loop, fetching the 3rd record indefinitely */

declare c1 cursor for

select c1, c2 from tbx1 where c3 = 'N'

go

declare @c1 int, @c2 int

open c1

fetch c1 into @c1, @c2

while @@sqlstatus = 0

begin

  select @c1, @c2 -- View the current fetched row

  update tbx1 set c3 = 'N' where c1 = @c1

  fetch c1 into @c1, @c2 

end

close c1

deallocate cursor c1

/*----------------------------------------------------------------------*/

If we execute a select on tbx1 before execute Test case #1, the result is:

c1   c2    c3

---- ----  ---

1    1     N

2    1     N

2    2     N

If we execute the same select on tbx1 after execute Test case #1, the result is:

c1   c2    c3

---- ----  ---

1    1     N

2    2     N   --> row position changed

2    1     N   --> row position changed

Analyzing the execution plan, updates on both Test cases are being done in deferred mode.

Could someone answer my questions, please:

1 - After executing Test case #1, 2nd and 3rd record change this position in table. How could this happening since I'm not updating any fields in the clustered index? This have any relation with deferred update deleting and reinserting the rows on overflows pages?

2 - Suppose the updates are changing the rows position in the table, how could explain the inconsistent fetch order on cursor processing (on Test Case #1, only 2 records are fetched - on Test Case #2, the loop never ends)?

Regards,

Douglas

[former_member182259]

In addition to the above (due normal ANSI isolation behavior about rows reappearing/disappearing and cursor sensitivity), in the second example each of the updates where c1=2 affects multiple rows, you get the deferred update.   You might want to have declared the cursor 'for update' and used the update where current of cursor syntax.    As it is, the current cursor definition is bad logic - you updated each row twice (once for the first row and once for the second row).

[Former Member]

Thanks for your replies. I've already solved my problem, just creating a unique clustered index. But I haven't understood what happened yet in the 2 cases above.

As suggested by Mark, I've read Chapter 18 from Transact SQL Users Guide. In section

"Join cursor processing and data modifications", there is a citation about what happens with cursor position after a update:

"A searched or positioned update on an allpages-locked table can change the location of the row; for example, if it updates key columns of a clustered index. The cursor does not track the row; it remains positioned just before the next row at the original location. Positioned updates are not allowed until a subsequent fetch returns the next row. The updated row may be visible to the cursor a second time, if the row moves to a later position in the search order".

As stated above, the cursor position keeps the same independently if the rows change their order.

I don't know if my logic is very simplistic, but I imagine that what should happen in #Test Case 1 is (assuming the access method is a clustered index scan):

a) After the 2nd fetch, cursor position is on 2nd row:

c1   c2    c3

---- ----  ---

1    1     N  

2    1     N   <-- cursor position

2    2     N  

b) After the update, 2nd and 3rd rows switch their positions, but cursor should keep its position (before 3rd row):

c1   c2    c3

---- ----  ---

1    1     N  

2    2     N  

                <-- cursor position

2    1     N

c) On 3rd fetch, the 3rd row is read "again" and cursor reaches the end of table.

In relation #Test Case 2, I can't imagine how a cursor could enters a infinite loop since the cursor position shouldn't change and "apparently the row positions are the same" after the "incorrect" update.

Moreover, I still have doubts about how a field update that isn't in a clustered index can change row position in a allpages-locked table.

The main goal of my questions isn't solve the problems but get a better knowledge about some Sybase internals (and avoid similar problems in the future).

Please give me more detailed explanations about my questions and comment my analysis. Related readings about this issues are welcome.

Douglas