> -----Original Message-----
> From: Antonio Sala [mailto:antonio@...]
> Sent: Monday, March 31, 2003 11:40 AM
> To: ib-support@yahoogroups.com
> Subject: [ib-support] Lock conflict on no wait transaction
>
> I work with: Firebird 1.02, FIBPlus, Delphi 6.
>
> The transactions are: write, nowait, rec_version, read_committed
>
> 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.

> 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:
> Lock conflict on no wait transaction.
> Violation of foreign key constraint 'FK_CITY_CODESTATE' on table CITY.

Yes, that's normal behaviour. I don't know of any workaround except to
remove the foreign key... But anyway, trying to use pessimistic locking on a
client/server RDBMS usually gives bad results and brings more problems than
it solves.

That's why the usual practice is to use other means to ensure that users get
warned if a record has been modified while they were editing it. That's
called optimistic locking. It can be implemented with a timestamp, or with
reselecting the edited data and comparing it with the one orginally
retrieved (ie. before the edits took place) and verify that it's the same...

Berenger