Subject Lock conflict on no wait transaction
Author Antonio Sala

I work with: Firebird 1.02, FIBPlus, Delphi 6.

The transactions are: write, nowait, rec_version, read_committed

I'm developping a multi-user application.
Because of it I prohibit that two users can modify the same record of the same table at the same time. To implement this
restriction I lock the record that is desired to modify: If anywhere EDIT the record, is sent UPDATE to record in the database.
Thus, until itself not the modification be accepted (CommitRetainning) the record remains blocked for the other users. The EDIT,
the UPDATE and the COMMITRETAINING are carried out inside a same transaction.

The problem is:

I Arrange of the tables:
table STATE
- Code
- Name

Table CITY
- Code
- Name
- CodeSTATE: Foreign Key related to the field Code of the table STATE.

We suppose that a user be modifying the state of code 01. While, another user is modifying a CITY and the CodeSTATE assigns it 01.
If the 2º user accept (post) the modification of the CITY , but the first user still is modifying the STATE of code 01, the
following error message's appears:

Project PIB.exe raised exception class EFIBInterbaseError with message 'tableSTATE.UpdateQuery'.
Unsuccessful execution caused by system error that does not precluded successful execution of subsequent statements.
Lock conflict on no wait transaction.
Violation of foreign key constraint 'FK_CITY_CODESTATE' on table CITY.

Because this error appears if when the 2º user post it, is not carried out any scripture on the STATE's table?

In IB 5.6, this error exists, and the message is
Violation of foreign key constraint 'FK_CITY_CODESTATE' on table CITY. The reference to Lock is omitted.

Thanks, and sorry if my english is too bad.

Antonio Sala Roldón