Hello, Nitin,

>> This behaviour is AS DESIGNED. Firebird doesn't take implicit
>> pessimistic locks for the period of physical record update for
>> various reasons (mainly performance-related in clustered
>> environment, AFAIU).

> I do not have any issue with the optimistic locking mechanism.

> I am questioning why I have to commit or rollback the transaction
> after Firebird reports an update conflict? Should a client
> application not be allowed to execute the same sql on the existing
> transaction without having to commit/rollback?

It should be allowed. If this is not the case then it is bug either in
your application logic or Jaybird driver. The latter is unlikely.

>> Use explicit pessimistic locks and
>> isc_tpb_read_committed+isc_tpb_wait+<whatever>
>> isolation mode to avoid
>> the error or handle this error yourself via retring
>> statement
>> (isc_tpb_read_committed modes) or transation
>> (isc_tpb_concurrency).

> Even if I specify tpb_wait, If I understand correctly,
> Firebird will report an update failure once the first
> update commits. So wait/nowait only differ in when
> Firebird reports a conflict, right?

wait/nowait mode specify if Firebird is allowed to wait on locks.
Normal updates are not protected from update conflicts in any TPB
mode. You can have some guaranties only if you precede them with
appropriate explicit lock statement. You can read
README.explicit_locks for explanation of some points.

BTW, if you handle update conflicts without rolling back full
transaction your application may also benefit from using of
savepoints.

> Thanks for the help.

> Nitin

--
Nickolay Samofatov