Hi Adam, this is only guesswork from my part, someone more competent will hopefully answer.

I find what you report sensible. Think about the scenario if transaction 2 had been allowed to update, and then transaction 1 would continue and update emp_no on the record it already has updated. Should it get an error then? It already has uncommitted changes to that very record. I find it a bit strange if what I'm allowed to do to a record would change between one update of the record and the next within the same transaction, but I'm no 'transaction expert' and could well be wrong.

In the Employee database, I would consider adding another table called something like EMPLOYEE_DETAILS with a foreign key to EMPLOYEE (1:1 relationship) if I were in a situation like you describe. I haven't tested, but then I would expect being able to update EMPLOYEE_DETAILS.SALARY in one transaction and simultaneously add a record to EMPLOYEE_PROJECT for the same employee in another transaction. And I wouldn't expect either of these transactions to be able to modify EMPLOYEE when the other transaction were still uncommitted.

Just my NOK 0.50,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Adam
Sent: 20. november 2007 01:32
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Foreign Key implementation

Hello Group,

My question relates to Foreign Keys and their implementation in
Firebird. My observations relate to 1.5, so please let me know if this
is a resolved issue in a later version.

I will use the Employee database as an example. Consider the tables
Employee and Employee_Project. The Employee_Project table has a
foreign key Emp_No.

If the salary field in Employee is updated in transaction 1 and you
attempt to insert a record into Employee_Project for that employee in
transaction 2.

If transaction 2 is a WAIT transaction, the insert will hang until
transaction 1 commits.
If transaction 2 is a NO WAIT transaction, the insert will fail with a
foreign key violation immediately.

To duplicate in iSQL, open two windows.

Window 1:
update employee set salary=salary+10 where emp_no=4;
[do not commit yet]

Switch to Window 2:
SET TRANSACTION READ WRITE NO WAIT SNAPSHOT;
insert into employee_project (emp_no, proj_id) values (4, 'GUIDE');

Returns:
Statement failed, SQLCODE = -901

lock conflict on no wait transaction
-violation of FOREIGN KEY constraint "INTEG_40" on table
"EMPLOYEE_PROJECT"

I can perfectly understand this behaviour if transaction 1 modified
the emp_no field, but I can not see why it is necessary for an update
of an unrelated field. Is this an implementation limitation in
Firebird or a deliberate design choice? If it is a design choice, then
what is the reason? Why would an update on the child table ok but an
insert disallowed? Why would it be OK if the child row was updated
before the insert is run? If it is an implementation limitation, is it
fixed, planned to be fixed, or should I add it to the tracker?

A WAIT transaction is inappropriate in my particular case (well at
least out of the question until I can assign a timeout value and the
monitoring tables are at a release version so we can see what is
holding a lock). At the moment it looks like I am left to drop the
foreign key declarations and rewrite them as insert and triggers in
the child and delete triggers in the parent. I would prefer not to
have to do this because foreign keys also serve as a self documenting
relationship.

How do other Firebird users deal with this scenario?

TIA
Adam