Subject Re: INSERT or Update and conflict
Author Adam
> so some time i get some violation of PRIMARY or UNIQUE KEY when 2
> client do an INSERT because at the same time these 2 clients do the
> same insert and unfortunatly one of them go in the violation of primary
> key.... I just want to know the best way to handle such situation ?

What do you mean by the "same" insert? Do you mean they literally
attempt to insert records with the same values, or do you mean that
two clients attempt to insert *different* records but using the same
primary key?

If the first, then presumably you can safely ignore the error. If the
second, your method of generating a primary key is suboptimal. There
are plenty of ways of ensuring unique primary keys (generators or GUID
for example). If you are using a query like select max(ID)+1 ..., then
this is a design flaw.

> now in the same kind, sometime i get 'Update Error - lock conflict on
> no wait transaction'. it's when 2 clients try to update the same row at
> the same time. here also how to handle such situation ? is it possible
> to force the update of the row by the second request instead of raising
> a lock conflict?

In other DBMS yes but not Firebird. It is almost universally
considered an undesirable consequence of a particular trade-off in
order to maximise performance.

Imagine client 1 updated a customers phone number, and at the same
time client 2 updated some other detail, both actions requiring the
update of a particular record. Under your proposed "force the update
of the row by the second request", the new phone number would be
overridden by the older cache at the second client!

The most common reaction to such a lock conflict is to get client 2 to
synchronise any changed fields with their own changes, then retry the
update in a new transaction.

There are certain things you can do to minimise the chance of a
problem; most of it is about being careful about minimising the amount
of time you have a writing transaction open for.