Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!mips!apple!well!gatynen
From: gatynen@well.sf.ca.us (Gerard Tynen)
Newsgroups: comp.databases
Subject: Re: Oracle CURSORS useless ?
Message-ID: <25781@well.sf.ca.us>
Date: 29 Jun 91 22:44:47 GMT
References: <1991Jun26.033043.22948@sunaus.oz> <25731@well.sf.ca.us> <1991Jun27.225555.27108@agate.berkeley.edu>
Organization: Whole Earth 'Lectronic Link, Sausalito, CA
Lines: 35

In article <1991Jun27.225555.27108@agate.berkeley.edu> mao@eden.Berkeley.EDU (Mike Olson) writes:
>In <25731@well.sf.ca.us>, gatynen@well.sf.ca.us (Gerard Tynen) writes
>
>> We couldn't think of any reason to force a close on all cursors just
>> because you do a commit.
>
>if you're doing two-phase locking, and you release locks at commit time,
>you'd better close your cursors.  changes by concurrent transactions will
>become visible at that time, too, which means that the set of prefetched
>values cached in the cursor may be wrong if you keep it open.

I agree.  Our problem was that we were building our cursors on top of Sybase
browse mode, which doesn't lock anything. You are really working on a copy
of the data.  When you update on the cursor, it compares timestamps to see
if anybody modified that row under your nose.  Also, as you indicate, updates
that change the result set are not reflected when you fetch unless you 
reopen the cursor.  

I think we just documented that updatable cursors only fetch data as it 
existed in the database at the time the select statement executed, and if
the user wanted current data, they would have to close and reopen the cursor.

This is bogus, I know, but I feel I can blame Sybase for this bogusicity.  
They are the only database I know of that allows database connections opened
by the same process to deadlock each other.  That means you can't update a
table you are reading without blowing away the result set unless you use their
lame browse mode, but then you really just get a copy of the data as it 
existed at some point in time.  Hey. I'm ready to switch to Postgres! 

So anyway, back to the original point, I now understand that real cursors 
ought to be closed upon commit.  My cursors weren't what I would call real
cursors, after all.  I would call them Sears Cursors.  Hopefully Sybase 
5.0 will not implement Sears Cursors...  

--GT