Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!dciem!nrcaer!cognos!garyp
From: garyp@cognos.uucp (Gary Puckering)
Newsgroups: comp.databases
Subject: Re: BIG, BIG fun w/Informix cursors
Message-ID: <1200@smokey.UUCP>
Date: Wed, 29-Jul-87 17:13:05 EDT
Article-I.D.: smokey.1200
Posted: Wed Jul 29 17:13:05 1987
Date-Received: Sat, 1-Aug-87 01:39:36 EDT
References: <229@paisano.UUCP> <1076@rtech.UUCP>
Reply-To: garyp@cognos.UUCP (Gary Puckering)
Organization: Cognos Inc., Ottawa, Canada
Lines: 82

In article <1076@rtech.UUCP> jeff@rtech.UUCP (Jeff Lichtman) writes:
>Now, about releasing locks when committing or rolling back a transaction:
> ...
>Database systems release locks at commit or rollback time because they
>are no longer needed: the transaction has either been entirely committed
>or entirely backed out, so the locks can be released safely without
>jeopardizing consistency.  To hold the locks longer than this wouldn't
>hurt consistency, but it would kill concurrency.  
> ...
>Suppose we want the cursor to remain open; the DBMS would still have
>to release its locks, which would mean that the cursor couldn't maintain
>whatever locks it had on its current position.  

Your statements seem inconsistent.  Why would the DBMS *have* to release
its locks?  I thought you said that they were released because it was
safe to do so, and desirable for good concurrency.  You haven't given
a reason why it is necessary.

>Suppose, before
>you tried to fetch the next row from the cursor, that someone destroyed
>the underlying table, or deleted the row that the cursor was going to
>advance to, or added a row in front of the one it was to advance to.

For almost all the applications I can think of which need the cursor
to survive a commit, it would be sufficient to end the first
transaction and begin another immediately, retaining locks on all
objects which the active cursors of the first transaction were addressing.
Locks on other objects (i.e. those which the transaction did not have
addressability to at commit time) could be released.

This approach would provide another way of ending transactions beside
the traditional COMMIT and ROLLBACK.  A good name for it escapes me,
but you are essentially ending a transaction, inheriting its cursor
(and requisite locks), and beginning a new transaction.

>What should happen?  Not only would it be extremely difficult to
>implement the DBMS to account for all possible cases of trying to
>use a cursor that has had the locks swept out from under it, it would
>be difficult to define what should happen in all cases (and many
>definitions would necessarily be arbitrary).

In the scheme I describe, the problem is avoided by not releasing
locks on objects which the cursors are addressing.

>
>It seems that Mr. De Masi got into this mess by trying to use "commit
>work" and "rollback work" to guarantee the success or failure of
>individual database statements.  That is not a correct use of transactions.

Mr. De Masi is not the only person to get himself into these problems.
Lots of other people have gone down the same garden path, led by
tantalizing database systems that do so many things for you
automatically that you can't get it to do what *you* want.

Suppose I write a simple on-line transaction program which allows me
to browse airline seat reservations given a partial name.  Assume
there are several qualifying records.  When I find the one I'm looking
for, I want to cancel the reservation.  If all this is done on a
single transaction, then every record I read will be locked.  Suppose
I decide not to cancel the reservation, and instead go to lunch.  This
leaves a bunch of records locked for a long period.  The other clerks
get very mad at me.

To avoid this, I could write the application so that browsing is done
on a versioned read (a read-only transaction).  This would eliminate
the locks.  But now I have to do the update on another transaction
(one that allows updating)!  But how do I get addressability to the
record I found during browsing?  How do I know that it hasn't changed
between the time I started the versioned read and the time I fetched
it on the update transaction?  Thanks for all the help, database
system, but why couldn't you just preserve my cursor across a COMMIT!

Actually, as I've mentioned in another article, I think there are lots
of applications where degree 3 consistency is overkill and that the
database system should allow you the option of degree 2 consistency
(with a stable cursor).  Both DB2 and SQL/DS provide this feature
(although the DB2 implementation is rather flakey).
-- 

Gary Puckering        3755 Riverside Dr.
Cognos Incorporated   Ottawa, Ontario    {allegra,decvax,ihnp4,linus,pyramid}
(613) 738-1440        CANADA  K1G 3N3    !utzoo!dciem!nrcaer!cognos!garyp