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: Transaction management (was Ingres vs Informix)
Message-ID: <534@cognos.uucp>
Date: Wed, 8-Apr-87 23:48:07 EST
Article-I.D.: cognos.534
Posted: Wed Apr  8 23:48:07 1987
Date-Received: Sat, 11-Apr-87 12:30:09 EST
References: <144@mas1.UUCP>
Reply-To: garyp@cognos.UUCP (Gary Puckering)
Organization: Cognos Incorporated, Ottawa, Canada
Lines: 81

In article <144@mas1.UUCP> gupta@mas1.UUCP writes:
>In article <477@cognos.UUCP> garyp@cognos.UUCP (Gary Puckering) writes:
>> ...
>> While on the subject of transactions, has anyone encountered problems
>> with the fact that most relational database managers cannot retain
>> your cursor position once you've committed the transaction?  If so,
>> in what circumstances have you encountered the problem and how did
>> you get around it?
>
>As a DBMS implementor, I would like to ask the question:
>
>    What are the semantics of a cursor, once the transaction is committed?
>	OR, put differently,
>    What are the semantics of transactions if cursor stability is provided
>    across transaction boundaries?
>...
>The main problem is that between transactions the state of the database
>may change, thus the cursor may no longer be valid.

One approach I've seen to ensuring that the database state remains
consistent is to retain the locks across the commit boundary.  In other
words, you end the first transaction, then immediately start another
transaction just like the first one, inheriting all the locks from the
first transaction.  Concurrent read-only transactions that begin after
each commit point would see the committed changes.

Unfortunately, I don't think this addresses the type of problem we've
encountered.  In our QUICK product, which provides a screen-oriented
data entry, retrieval and update facility, we allow an operator to
change records as desired while browsing along a retrieval path.
However, we *never* allow locks to be held across a terminal read.
This is to prevent the problem of an operator walking away from the
terminal and leaving records locked for long periods of time.

For indexed file systems, we only lock records when the operator
requests an update.  Before actually modifying the records, we re-read
them and compare them to what was originally read.  If they haven't
changed, we proceed with the update.  Otherwise, we abandon it.  The
re-read is usually done on another stream or another open, so as not to
disturb the retrieval path.

For relational systems, we browse using a read-only transaction and
update on a read-write transaction, using the same re-read and
comparison approach that we use for indexed files.  This guarantees
a stable cursor on the read-only (browse) transaction, and ensures a
high degree of concurrency.  Unfortunately, with this approach, updates
could fail (a concurrent transaction could have grabbed the record and
changed between the time your transaction read it and the time it is
re-read in preparation for update).

We also provide a CONSISTENCY mode in which a single read-write
transaction is used.  This guarantees that updates will succeed and
eliminates the overhead of re-reading and comparing records.  But, it's
vulnerable to the problem of the operator going to lunch and leaving
large portions of the database locked up.  Also, once the operator
commits the updates, the cursor is lost and the retrieval path along
which he/she was browsing is stopped cold.

These two strategies seem to present an interesting dilemma.  You can
have high concurrency, or high consistency, but not both.

It would seem that the relational transaction model is appropriate for
batch-style queries and update requests.  But in an on-line transaction
environment, in which operator intervention and decision-making 
(based on retrieved data) is involved, the model has some serious
practical limitations.

I'd like to hear comments from the database implementors *and* the
theoreticians on this one!

(Nothing would please me more than to have someone out there point out
an "obvious" solution to this problem.)

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