At 10:49 PM 09-12-02 -0800, you wrote:

> >You should have the insert of a master and its detail records within the
> >context of a single unit of work. You can use the default transaction or an
> >explicit one that you use for this. Keep in mind a TIBODatabase will default
> >to AutoCommit as true and a TIB_Transaction will default to AutoCommit as
> >false.

Joe,
Hope you don't mind if I hope in here with a few comments. :-)

>Does AutoCommit mean that each insert is committed immediately after
>execution? Is that a bad thing in my situation?

AutoCommit means that each DML operation in your task that is posted will
cause the transaction in which it occurs to be committed. It uses the
Commit with Retain option (which IBO calls CommitRetaining).

>Is the purpose of transactions basically to control when commits are done,
>or is there more to it than that?

That's an interesting perspective! <g> All work in Fb/IB (IBO or no IBO)
is a conversation between the client and the server. The client starts a
transaction and submits one or more requests. A SELECT is a request and it
won't happen unless there is a transaction. It returns an output set - a
set of rows which IBO accommodates in the buffer of a dataset.

As long as that transaction stays open (is not committed or rolled back),
other requests can be made inside that transaction. In particular, IBO
does stuff so that data in the buffer are used to uniquely identify and
locate rows back there on the server which its Insert and Delete methods
can operate on. It also uses characteristics of this output set to enable
rows to be inserted and to search for other rows, either in this set or in
another table.

IBO's Post method submits these data operations to the server. The server
stores the requested changes in a new record version. It will just keep
these new record versions until the client sends across a request to commit
or roll back the posted work.

CommitRetaining is almost like Commit, but not quite. Although it *does*
cause the posted work to be committed permanently to the database and to
become visible to other transactions, CR does not kill the transaction
context. It actually starts a new transaction, recycling the *same*
database cursors that the just-committed transaction already had.

>I'm a little confused about how commits and transactions interact with key
>violations. In my situation, the client queries the database to find out
>what the last key used was. It then increases that number by one and
>inserts a record with that key.

This is *NOT* a good way to generate keys in a multi-user or
transaction-protected environment. It's a legacy from desktop database
techniques that doesn't have a place in client/server and you should assign
high priority to REMOVING it.

>Let's say two users are doing this
>simultaneously. They may both query at the same time and try to store
>records with the same key. Now, if Client A inserts first, but doesn't
>commit yet, then Client B inserts, will Client B get an immediate key
>violation?

Exactly. That's why multi-user databases provide ways to autogenerate
keys. MSSQL Server has Identity types and Oracle and FB/IB have
generators. FB/IB delivers you generated values which are independent of
any transaction and can not be re-generated by another transaction. IBO
supports generators with the GeneratorLinks property.

>If not, then does the first one to commit succeed, and the
>other get a key violation? Or, does Client A get preference for having
>inserted first?

No: IB/FB has optimistic locking. Unless you explicitly cause a
"pessimistic lock" to occur, it is "first come, first served". One
transaction can't see another transaction's posted-but-uncommitted
inserts. The first to commit a PK value wins, no matter who manufactured
it first.

>I guess I'm basically asking two questions: 1) Does the
>insert reserve the key, or does the commit reserve it?

As above. The Post of the insert doesn't reserve anything. The first to
commit wins and the other(s) get key violations.

> 2) Does in insert
>trigger a key violation, or does the commit trigger it?

See above.

>Here's my concern: After inserting the master record, the app needs to
>insert several detail records, including the master's key for later
>reference. If there's going to be a key conflict on the master records, I
>want to know that BEFORE I start inserting detail records.

You're using IBO!! it is designed to make all this stuff work properly -
especially if you are using proper techniques in the database. Here's what
IBO does if you are using a Generator for the master's and details' primary
keys AND you have the GeneratorLinks set up AND you have IBO's
master/detail mechanics properly set up:

1. You insert a new master record. Behind the scenes, IBO goes and
fetches the next value from the generator. You now have this value: it
cannot be generated again for any purpose.
2. You start inserting detail records. Behind the scenes, IBO writes the
master's PK onto each detail record as it is inserted and (provided your
detail table has a generated PK) it also fetches a new PK for the detail
record.

>Otherwise, the
>detail records could get linked to the wrong master record, causing a nasty
>mess to clean up if the master record insert fails.

If you do it properly, this can't happen...if you decide (after all) to
cancel the whole new m/d structure, IBO rolls back the pending detail
transactions and also causes the master record to disappear. Nothing is
left; and it's not possible for detail records to get assigned to the
wrong master.

>Does that mean that I
>should commit the master record before inserting the detail records? If
>so, wouldn't that be a reason to make it two transactions instead of one?

I guess by now you realise that that isn't going to protect your data
integrity at all well and could leave you with either unwanted master
records or orphan detail records.

Please get rid of your transaction-dependent manufactured keys and use
Fb/IB as it was designed to be used. You might like to pick up the TI
sheet on Client/Server development, from the TechInfo section of the IBO
website...

regards,
Helen