My familiarity is with the Microsoft SQL server world using ADO (dbGo), and I have written many applications for that environment. Now I have a legacy Delphi 7 application with a Firebird 2.5 database that I must maintain.
BUT I am finding is that if 2 client applications execute this:
SQLQuery.SQL.Text := 'Update mytable set field1 = 11 where keyfield = 99'
SQLQuery.Execute;
at nearly exactly the same time, the 2nd application gets a "deadlock" error immediately. In SQL Server, there would be a wait period
ADOConnection.Isolationlevel = ilCursorstability;
ADOConnection.CommandTimeout := 5;
before any exception is raised in the second client app. The exception handling might involve a rollback in what would be deemed as a very unusual situation within a batch process. This is reasonable. 5 seconds is an awfully long time in computer processing time.
Now my attempts at using the same methodology at the Firebird Client have been fruitless because the "deadlock" (actually, a record in use) occurs immediately.
If the database engine can't be configured to wait a little for conditions to improve (record locks to be released), the responsibility must now rest with the client application developer who must write insanely slow code to overcome what appears to me to be major failing of Firebird.
Once the "deadlock" has been detected, the condition doesn't clear except by disconnecting the connection component
while rowsupdated = 0 and counter < 5 do
begin
try
rowsupdated := SQLQuery.Execute;
except
SQLConnection.Connected := False;
SQLConnection.Connected := True;
end;
Inc(Counter)
end;
How do you make robust multi-user table-update clients when you don't have any substantial lock tolerance in Firebird, using DBX in Delphi?
The client can specify if the transaction should wait for deadlock resolution. If in your case the deadlock happens immediately it's probably because of your configuration (using nowait transaction parameter on the client). Not using nowait will cause the server side to detect a deadlock and (after a configurable timeout) raise an exception on the client.
Since Firebird 2.0 you can also specify a lock timeout on a transaction from the client, overriding the server-configured timeout value.