Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Question about strange cursor behavior - Sybase ASE 12.5.4

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 Member
Not what you were looking for? View more on this topic or Ask a question