Probably a trivial question, but I want to get the best possible solution.
Problem:
I have two or more workers who insert keys into one or more tables. A problem occurs when two or more employees try to insert the same key into one of these key tables at the same time. A typical problem.
- Worker A reads the table if a key (SELECT) exists. There is no key.
- Worker B reads the table if a key exists (SELECT). There is no key.
- Worker A inserts a key.
- Worker B inserts a key.
- Running employee A.
- Worker B commits. An exception is thrown when a unique constraint is violated.
Key tables are simple pairs. The first column is an auto-increment integer, and the second is the varchar key.
What is the best solution for such a concurrency problem. I think this is a common problem. One way is to handle exceptions, but for some reason I don't think this is the best way to handle this.
The database that I use if Firebird 2.5
EDIT
Additional information to make everything clear.
- Client-side synchronization is not a good approach, since inserts come from different processes (workers). And someday I could have workers on different machines, so even mutexes are not-go.
- The primary key and the first columns of such a table are the auto-increment field. No problems. The varchar field is a problem as this is what the client is inserting.
A typical table is the user table. For instance:
1 2056
2 1044
3 1896
4 5966
...
, "xxxx", .
2:
, - . IB/FB ( InterBase Express). :
except
on E: EIBInterBaseError do
begin
if (E.SQLCode = -803) and (E.IBErrorCode = 335544349) then
begin
FKeysConnection.IBT.Rollback;
EnteredKeys := False;
end;
end;
end;