Subject Pessimistic locking problem (possibly OT)
Author Stuart Hunt
I wonder if anyone out there can help me with a problem.
I have two tables, DOCUMENT and CHANGE. A cut down version of them is:

create table document
id integer not null,
description char (30) not null,
constraint pk_masterstuff primary key (id)

create table change
id integer not null,
documentid integer not null,
description char (30) not null,
constraint pk_change primary key (id)

alter table change
add constraint fk1_ change foreign key (documentid) references document;

I use IBO v 3.3Ba and Delphi 4 to access these tablesin IB 5.6.
Normally everything works OK. Howver, in one dialog I need to
pessimistically lock the selected row in the DOCUMENT table OK so far. Then
I need to be able to create entries in the CHANGE table that reference this
document in their DOCUMENTID field. This would be OK, but the CHANGE inserts
need to go in their own separate transaction so that cancelling the document
save doesn't cause the changes to be lost. The problem is that as soon as I
try to post the change insert I get a foreign key violation on fk1_change.
If I remove the pessimistic lock everything works fine, but this lock is
required for various reasons.
My question is: Is this a problem with the version of IBO I've got and its
implementation of pessimistic locks, or is it a problem with InterBase 5.6
that should be addressed to the IB group? If its a problem with IBO does
anyone know a way around it that keeps a pessimistic lock, please?
Stuart Hunt,
Senior Developer,
IdeaGen Software PLC

This email and any files attached is intended for the addressee only and may
contain information that is confidential and/or legally privileged.
Unauthorised use is strictly prohibited and may be unlawful. If you are not
the addressee, you should not read, copy, disclose or otherwise use this
message, including any attachment, except for the purpose of delivery to the

We make every effort to keep our network free from viruses. However, you do
need to verify this e-mail and any attachments to it to be virus free as we
can take no responsibility for any computer virus which might be transferred
by way of this e-mail.