At 11:17 PM 29-10-02 +0000, you wrote:

>Hi.
>
>I'd like to understand the above error. (D6 & IBO4.2I)
>
>My scenario:
>I have a client table
>CL_ID INTEGER PK,
>NAME VARCHAR(64)
>
>And I have a client CCEmail table
>CL_ID INTEGER FK (CLIENT)
>EMAIL VARCHAR(64)
>NAME VARCHAR(64)
>PRIMARY KEY (CL_ID, EMAIL)
>
>I have separate transactions for the CCEmail & the Client tables'
>access.
>When a new record is inserted into CCEmail, I manually enter the
>CL_ID (in OnNewRecord), and the user fills out EMAIL & NAME.
>
>If the CLIENT transaction has a pending commit (I have edited some
>detail in the client table), and I then try to enter a new record
>into CCEmail, (for the same CL_ID) I get the above error.
>
>If the CLIENT transaction has a pending commit, and I try to enter a
>new record into CCEmail for a DIFFERENT CL_ID, there is no problem.
>
>If the CLIENT transaction has NO pending commits, and I try to enter
>a new record into CCEmail for the same CL_ID, there is no problem.
>
>In other words, the error only occurs when I try to enter a new
>record into CCEmail, for a certain CL_ID which has pending
>commit/rollback.
>
>I'm assuming this has something to do with the transaction
>isolation, but I'm not really sure on why.
>Both transactions have tiConcurrency.

Yes, it has everything to do with transaction isolation. You have the
table that is logically the "master" with pending work in one
transaction. That blocks all other transactions from getting access to
that record until the transaction commits. (Because you use tiConcurrency,
in fact your other transaction won't get access to it until after *it* has
committed).

Now, moving to the other transaction, where you are trying to insert a new
record into a table which is linked to the Client table by a FOREIGN KEY
constraint. This constraint says "You can't insert a new member to the
referring table unless the engine can verify that the referenced record
exists". It can't do this verification because the referenced record is
locked. Hence, you get the lock conflict condition.

The solution is very simple: put both of these datasets in the same
transaction and link them with a master-detail structure. Adding (or
editing) a master record while operating on the detail set definitely needs
to be a single task.

Also, tiConcurrency is not necessarily the best setting if other people
need to use these structures interactively. Unless your tasks really
*need* a snapshot view, tiCommitted (especially when used with IBO's
invalidation features) keeps each user's view more up-to-date. Generally,
keep tiConcurrency for transactions in which you do reports or data exports.

regards,
Helen