Relay-Version: version B 2.10 5/3/83; site utzoo.UUCP
Path: utzoo!mnetor!uunet!seismo!ll-xn!husc6!ut-sally!im4u!speegle
From: speegle@im4u.UUCP (Greg Speegle)
Newsgroups: comp.databases
Subject: Re: BIG, BIG fun w/Informix cursors
Message-ID: <2063@im4u.UUCP>
Date: Mon, 3-Aug-87 13:50:02 EDT
Article-I.D.: im4u.2063
Posted: Mon Aug  3 13:50:02 1987
Date-Received: Tue, 4-Aug-87 04:22:35 EDT
References: <229@paisano.UUCP> <1076@rtech.UUCP> <1200@smokey.UUCP>
Organization: U. Texas CS Dept., Austin, Texas
Lines: 68
Summary: Using nested transactions could help this.

In article <1200@smokey.UUCP>, garyp@cognos.uucp (Gary Puckering) writes:
 
> 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.
> In the scheme I describe, the problem is avoided by not releasing
> locks on objects which the cursors are addressing.
> 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!

The problem you propose here is called a long-duration transaction, which is
a serious problem for design databases, like CAD systems. One solution, which
solves many of the problems posted here, involves nested transactions. A nested
transaction is a tree structured representation of transactions, where a leaf
is a database operation (read/write) and a non-leaf node is a collection of
nodes. At the level above the leaves, a node would be the same as a standard
transaction, while the level above that would have a transaction as an 
operation. Thus, for your problem, one transaction would perform all the reads,
and a second transaction would perform all of the writes, but both of these
transactions would be subtransactions of the SAME TRANSACTION, thus locks could be preserved across the commit of the subtransaction. There are protocols which
ensure serializability and other stuff as well. I have not heard of any 
production system which uses nested transactions, and if anyone out there
does know of such a system, I would appreciate hearing about it, as nested
transactions are part of my dissertation research.

More information about nested transactions can be found in work done by
J. Elliot B. Moss, currently of the University of Massachusetts, in his
dissertation, now a book published by MIT Press called, "Nested Transactions".
This work is primarily involved with distributed databases, but it clearly
explains the concepts of nested transactions. Other references can be supplied 
on request.

Apparently, the above is not sufficient text to get this article posted, so
I'll ramble some more about nested transactions. The lock protocol proposed in
Moss allowed lock sharing as well. A transaction could lock an object only if
holders of incompatible locks are ancestors of the transaction needing the lock.
Once a transaction finishes, it releases its lock to its parent. Thus, in the 
example problem presented here, the first subtransaction would attempt to get
a read lock on the items needed, while the second transaction would aquire a 
write lock on the reservation which would need to be cancelled. the cancelled 
reservation would still be in the database because the parent of the "write" 
subtransaction would still hold the read locks. This is only a brief sketch 
of the concept, but I think it may prove very useful in more difficult 
database applications, such as CAD systems.

Greg Speegle
{ihnp4,siesmo,pyramid}!ut-sally!speegle    speegle@sally.UTEXAS.EDU