At 05:28 PM 21/07/2003 +0200, you wrote:

>Helen,
>
>Thank you for replying and for sharpening my remarks. They are not allways
>accurate.
>
>I understand how readcommitted works and I understand the way IBO works with
>this:
>-autocommit = true
>-commitretaining to commit changes while continuing the same transaction
>-timerfunction of TIB_transaction to do a hard commit every x minutes.

No. The TimeoutProps set the various timings for IBO to go and commit
transactions that aren't being used. As long as a transaction is being
used, i.e. has pending updates (posted or not), or is being used for
searching, it won't be considered for timeout.

>I also understand tiConcurrency, but I don't know how to work with IBO using
>this, in combination with user-interfacing:
>
>1. user A makes a selection of records ---> so no commit after select as you
>suggested
>2. user B makes the same selection ---> also without committing after
>selecting

That's right. Both transactions can be looking at exactly the same set of
records. tiConcurrency gives each of them a snapshot of all of the rows
that were committed at the time their requests were granted. Unless they
both started their transactions at precisely the same instant in time, they
are probably not both looking at the same versions of all of the rows.

>3. user A changes one record, and I want other users to see changes
>immediate ---> so now I need a hard commit

Any sort of commit (hard or soft) will enable other users to see
changes. Any tiConcurrency-isolated transaction that starts *after* that
will see them. Any transaction that is still running will only see them if
1) it is in tiCommitted isolation and 2) it knows to refresh.

Other transactions in tiConcurrency isolation won't see the changes until
they do a hard commit and start a new transaction. Under the TDataset
model, they won't know when they need to commit. (In native IB_ data
access, you can use DML Caching to make each client aware of changes by
others, as they happen).

>put in the afterpost event of TIBOquery ?

The Commit test can go there; but, remember, the transaction that
performed the DML can *already* see the changes it made itself, even if it
called CommitRetaining.

A Post will fail if another transaction has already posted a change (and
that's the trick that IBO's Pess.Locking hack makes use of) or if another
transaction has already committed a version of the record that is newer
than the one that this transaction sees in its own isolated view of the
database state.

Every transaction assumes that, if its Post succeeds, it can
commit. Various factors of its own and others' isolation will affect
whether the commit will actually succeed.

>4. user B makes the same selection again....but does not see the changed
>records of user A.

Correct, if transaction B has tiConcurrency isolation.

>It seems he first has to COMMIT and then start the selection again to see
>the changed record.

Yes - for tiConcurrency. However, in tiCommitted isolation, the
transaction can see the user A's changes as soon as they happen. In this
case, the user will see them just by refreshing the same selection.

>That's why I stated that after every select I need a hard commit, to be sure
>user B sees all changes made to the database.

No, your understanding of transactions is wrong. The purpose of a
transaction is to create an isolated context in which a user task is to be
conducted. This context isolates the state of the database at the moment
the transaction began. Committing completes the task.

-- A hard commit invalidates the entire context. When a new transaction is
started, a completely new context is created. The transaction then will be
looking at an updated snapshot of committed record versions, regardless of
its isolation level.

-- A soft commit (commit retaining) starts a new transaction but retains
the context (database state) as it was before the soft commit. This
transaction sees the database state exactly as it was back when the
original transaction was started - plus it sees any changes that it
committed itself since that beginning. It will see changes done by others
since the beginning only if its isolation level is tiCommitted.

Now, looking at Read Committed (tiCommitted) isolation, you have some
options which affect what happens.
--- Rec[ord]Version true makes the transaction read the latest committed
version and is not concerned about other transactions that are concurrently
updating the same record, even if the other uncommitted versions are more
recent. Its update will succeed and others attempting to update after will
fail if their version is not newer. So, as long as this transaction's new
version is newer than the most recently *committed* version, it wins.

--- With No Record Version (RecVersion false) you need to set the Lock Wait
parameter.
** WAIT (LockWait true) causes A to wait until B either commits or rolls
back. If B commits, then A will fail if its version is not newer,
otherwise it will succeed (and change the record version again) and B will
fail. If B rolls back, then A succeeds because it has the newest
version. The deadlock must be broken by one transaction or the other
"giving way" by rolling back.
** NO WAIT (LockWait false) returns an immediate deadlock if another
transaction's uncommitted (posted) version is newer than the one A is
trying to commit. This enables the developer to roll back the transaction
as soon as the deadlock is evident, rather than waiting to see whether or
not it would succeed.

>But you say it is not wise to commit after each select.

Commit *ends* a transaction. The transaction context is gone, so nothing
is left to establish the state of the database. Any DML operation you
perform would have to start another transaction, which would establish its
own context unrelated to the [dead] context of the selected set. The whole
point of transaction isolation is to wrap the context around the entire task.

>Now I'm lost on that one. That's why I stick to tiCommitted at the moment.

We've been seeing a lot of messages from you so, by now, I'm getting a mix
of messages about what your priorities are. You seem to be emphasising the
fact that you don't want user B to update a row if user A had already begun
editing that row, i.e. you want the work to be strictly serialised. Yet,
somewhere, you stressed that you want to avoid the pessimistic lock. You
can't have it both ways.

Earlier, when the requirements were rather generalised, I said something
like "pessimistic locking is contrary to the optimistic row-level locking
principle". And it's true. But if your application flow is such that
different users are stepping on the same records frequently and you MUST
implement a rule that the first to start an edit has to win, then you need
the pessimistic lock, to exclude anyone else from touching that record
until that first user has finished.

Somewhere else you seemed to assume that pessimistic locking locks up
entire tables. It doesn't. Only Table Stability (tiConsistency) isolation
locks tables and you'd never use this isolation level for interactive
work. Pess.Locking locks up the **row** that the user is editing, plus any
dependent rows in other tables. ("Dependent" as defined by formal database
constraints, specifically referential triggers and any user-defined ones
that affect other tables).

Lockups will occur if the users are all running in tiConcurrency and using
CommitRetaining, because each user will be sustaining a view of database
state that sees only the record versions it changed itself. A row that was
locked to them the first time they tried to post a new version to it just
stays locked - even though the original record version was made obsolete
long ago - because their transaction contexts do not get updated.

In a busy environment set up this way, where the level of concurrent access
is high, you'll see more and more frequent deadlocks as time passes. Add
to that, end-to-end CommitRetaining prevents the server's garbage
collection from cleaning up obsolete record versions. Here you have a mix
which will grind the server's responsiveness down and down and crash it
eventually.

There's no "default IBO behaviour" to conform to, because there's no
conclusive formula for isolation and commit behaviour that works for every
task. The point with IBO is that you have implementations of everything
that the database API provides.

I guess that will do for now.

regards,
Helen