Thoughts on a Cursor KBA, SAP Note 2017460

[Former Member]

Everyone loves cursors.  Everyone uses cursors.  Not everyone is curious enough to check from time to time what's new in the cursor's world in ASE.  This causes, potentially, funny things to circulate in professional world of ASE developers.

I came across the unpleasant message one day coming from the developers world:

Msg 582, Level 16, State 3:

Server '{SERVER NAME}', Line 1:

Cursor '{CURSOR NAME}' was closed implicitly because the current cursor position was deleted due to an update or a delete.

The cursor scan position could not be recovered. This happens for cursors which reference more than one table.

This is not a pleasant message to receive.  The server has closed your cursor since it has discovered that it's result set has been tampered with. 

Also this message came out in ASE  16 / 15.7, actually this situation is nothing new.  If my memory does not fail me, the earlier releases were more "permissive" to this situation.  They just continued to execute code, skipping rows, doing god knows what.  Only if you was fortunate enough to be able to catch this situation you might have sensed that something is wrong with the flow of your code.  Otherwise you just kept producing bad data unawares.

So, first of all, thank you SAP engineers for closing my cursor.  This is a good message - not a bad message.  This message means that the code I wrote sucks.  A bit.  What is more interesting, though, is that in cases like this developers (or DBAs) will usually go googling or KBAing and if they are lucky, they find some workaround to apply.

As it happened with this message there is a "lucky" number for the curious - SAP Note 2017460 (should I also say "everyone loves SAP notes" to complement my "everyone loves cursors"...  just musing).  KBA goes through a brief reproduction for this situation and comes out with a neat workaround.  I'll skip the reproduction (you may find it here:  https://websmp230.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/sno/ui_entry/entry.htm?param=69765F6D6F6465...) and jump to the lucky workaround:

Resolution

Use "order by" or "distinct" in the cursor declare.
They makes ASE optimizer use a work table.
That is, cursor position is placed on the work table which avoid error 582.

Wait a minute... Work around?  Order by?  Distinct?  Hmmmm.  That will probably work.  But is it the right way to treat situations like this?  Which release we are talking about?

Yep.  I don't know who is responsible for this KBA but this is a very curios way to engage with cursors.

Since ASE 15.0 new type of cursors were introduced:  scrollable cursors.  Although the scrollable nature of the cursor is not what helps one in this situation (scrollable actually means not that you may walk up and down the cursor but that you may fetch more than one row - including using absolute positioning). Unfortunately, when this new feature got into ASE the other feature that has been added was not given the same amount of focus - it has just been mentioned in passing.  Scrollable cursors may be "insensitive" or "semi-sensitive," or as the NFG stated back then:

"Insensitive" or "semi-sensitive" refers to the extent to which data changes from outside the cursor are visible to the cursor.  A cursor can be semi-sensitive but not scrollable

As to me, the attention must have been reversed.  Few people use scrollable cursor feature.  Some definitely do - otherwise it has not been introduced as a NF.  But all are plagued by the uncanny misbehavior of cursors which sometimes simply do not do what they are supposed to.  At least in the new releases this nasty situation is detected by the server itself and the cursors are closed, issuing error. 

Back to the KBA.  For those using ASE 15.0 and later the way to handle the 582 error is not to tweak around optimizer's access plan and sorting data artificially in order for the server to create a worktable and put the cursor data into it.  The proper way to handle is simply to declare the cursor as "insensitive."  I was very surprised that this has not been put into KBA (should not KBAs be checked by someone before they are released?). 

Now, there might seem to be a caveat to the workaround proposed in the KBA.  Insensitive cursors are.... read-only.  Let's check this:

declare CR_INS insensitive cursor for select name, suid from master..syslogins for update of name

Msg 7301, Level 15, State 1:

FOR UPDATE has been incorrectly specified for a READ ONLY cursor

Hm.  Not nice.  Ok, ok.  I got it.  Silly me.  Let's go back to the KBA workaround:

declare CR_INS cursor for select name, suid from master..syslogins order by suid for update of name

Msg 7301, Level 15, State 1:

FOR UPDATE has been incorrectly specified for a READ ONLY cursor

I rest my point.  Order by does not simply fool the optimizer.  It changes a bunch of other things.

There are two things omitted from the said KBA which I would like to emphasize:

1.  If you face the Msg. 582 situation please re-declare your cursor as insensitive.  I'd say choose this option as a "best practice" - less refactoring in future.  Do not implement the KBA advise.  It is ignorant of the cursor evolution in ASE.  I'd really like to see it removed in future.  Bad coding practice.  Negative performance impact.  Should not be on KBA.

2.  Keep in mind that insensitive cursors are read only (whether they are defined insensitive or turned into being insensitive by some tweaking - there is a list in documentation on what turns a cursor into read-only - order by is on that list).

3.  If you have been using the cursor for updating its result set and you hit the Msg. 582... Well... You are in trouble.  You can either cross fingers and let the code run opportunistically (as it all depends on concurrency it will definitely succeed more often than not) or you may put the result set into a #temp table and declare a cursor on it.  Much safer.  Usually works better too. You'll need to check @@rowcount for your updates, though, since those that were hitting 582 may return 0 row affected (not necessarily).

Anyway, the lesson for today:  do not implement KBA blindly. Talk to DBAs first.  Sometimes they have a few things to add.

HTH,

ATM