At 05:59 PM 12/11/2004 +1000, you wrote:

>Hi all,
>
>I have a database that has one group of static tables another group of
>dynamic tables.
>
>The dynamic data is being saved while the static data is being updated.
> The dynamic data has foreign keys that is related to the static data.
>
>My problem is that I am getting a lock conflict when I am trying to save
>the dynamic data, and it says there is a foreign key violation on the
>static data (which there isn't).

Unfortunately, there is. I think you are fooling yourself by thinking of
these tables as "dynamic" and "static". If you update a table, it's not
static!!

Whenever you have an uncommitted update occurring to the referenced
(master) record, any rows dependent on that record in your referencing
tables will be locked for update and another transaction won't be able to
insert any new rows into the table. This, of course, is to protect
referential integrity - hence the Foreign Key violation error.

>Here's the sequence of events:
>1. Start static transaction (Commited isolation, RecVersion true,
>LockWait false)
>2. Update all records in table A.

Forget "static". The only sort of transaction that can be regarded as
"static" is a read-only one in Concurrency isolation.

>3. Start dynamic transaction (Commited isolation, RecVersion True,
>LockWait false)
>4. Insert dynamic data (with foreign key to table A) - this blows up

For the reason described above. Remember, all updates are at row
level. The second transaction can't insert because of the pending updates
in the first. "Read Committed isolation" doesn't mean "Read Uncommitted" -
that's Dirty Read, and IB doesn't support it.

>5. Commit dynamic transaction
>6. Commit static transaction
>
>I suspect I may not be using the correct transaction settings?


>This is probably not the right place to post, but I don't know enough
>about transactions to determine if this is an interbase problem, an ibo
>problem, or my problem...

It's your problem, really. It's trap to think that RI won't get in the way
if you're not updating the key field of the master. Because updates are at
row level, one transaction has no way to tell whether another transaction
is trying to update the key; and one transaction has no way to tell
whether the new rows coming in from the other transaction will comply with
RI or not. So, from the engine's POV, any insert attempt that jeopardises
RI is a foreign key violation.

About the only way you will get this to work will be to put both operations
inside a single transaction, in Concurrency isolation to protect it from
ReadCommitted RECORD_VERSION "marauders" from outside. If that's not
practicable, because the two operations involve different users, then set
up the two transactions like this, with NO Autocommit!!

If you want a "selector" for the master table, e.g. in a grid, put the
SELECT query in a read-only ReadCommitted transaction.

Use a TIB_DSQL to hold an update statement for the master
table. Parameterise all of the fields that the user is allowed to "edit"
and link each of these updatable fields to a TIB_ParamEdit hooked to this
statement, in the "editing area" of your form. Use data-aware controls
bound to the selector dataset to display the current row fields you don't
want the user to update.

Even if you are using the TIBOQuery for the selector, it's fine to use the
TIB*_ native components (TIB_DSQL and TIB_ParamEdit) this way, since there
is no datasource involved.

Place the TIB_DSQL in its own Concurrency WAIT transaction and do the
complete task in one hit, from a buttonclick or other user-driven event, viz.

if any paramedits are empty
except and make the user fill them **
start transaction
if not statement.prepared then prepare
assign any params needed by the WHERE clause
try
try
statement.execute
except
transaction.rollback
end
transaction.commit
except
transaction.rollback
end

** you could pick up "default" values for the paramedits from the grid
dataset if the user leaves them empty. Then you would need to compare
values, since you won't want to "update" a row that hasn't actually been
changed.

Put the Insert statement in the other application in a ReadCommitted
transaction if you like, but use NO RECORD_VERSION if you do. Use WAIT for
lock resolution, so you can take advantage of the "windows of opportunity"
between the updates to the master table.

To reiterate: don't make any of these transactions Autocommit.

Helen