Q

Sybase's cursor restrictions, part 2

Continued from part 1...

(4) Cursors do still have quite complex locking and concurrency implications but (a) this does not necessarily mean poor performance, just that care has to be taken with the design, and (b) all vendors' database servers will display such complexity - I challenge anybody to show that it is specifically a Sybase weakness. Locking behaviour is mostly driven by ANSI isolation level definitions, which are common to all vendors, although they differ in which ones they offer, and in their detailed implementation solutions to the ANSI requirements. Generally, as with Sybase, at least Level 3, Level 1, and Level 0 are offered. These correspond roughly to holdlock, no holdlock, and no read locks attempted, respectively, in Sybase traditional terminology. (The are not solely cursor related of course, although again using cursors introduces more complexity in understanding what will happen and when). Perhaps appropriate use of these options would also help.

(5) Oracle cursors have some properties, e.g. read consistency, which are the subject of legitimate debate. Some see them as dubious features that Oracle have managed to "spin" into desirable ones. I feel that like all RDBMS quirks, they have a place if carefully used for well thought out reasons, and with some eye to things that are clearly not going to stay supported for ever. For Oracle fans who have found effective uses for read consistency -- which is basically the ability to query a stable logical snapshot of data during a long lived cursor loop, but without holding up other users by taking long lived shared locks -- ASE 15.0 will include an insensitive cursors feature, which should offer very similar properties. Cursor handling in general will be considerably enhanced in 15.0 in fact.

SHORT ADVICE

  • Favor server side cursors, trial client cursors before use
  • Common sense for what is the cleaner solution fit cursor or set-based
  • Default to set-based approach, but not all the time
  • Be prepared to get competent help, or put in the work yourself, to analyse locking behaviour in very complex, critical cases, and do not swallow the idea (surely it is not pure Oracle knocking to suggest this) that any vendor offers a free ride in all situations no matter how inherently complex. This will also apply to set-based solutions, though they are probably simpler on average.

For More Information


This was first published in February 2004

Dig deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close