16

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?

nolaspeaker
  • 1,868
  • 19
  • 38
  • Have not used FirebirdSQL in a while, but remember there was a SELECT FOR UPDATE WITH LOCK functionality that could be used at SQL level instead. Checkout this: http://www.firebirdsql.org/refdocs/langrefupd25-notes-withlock.html – quasoft May 21 '15 at 17:30
  • The default value for the IsolationLevel for a DBExpress connection to Interbase is `ReadCommitted`, which is the equivalent of `ilCursorstability`. The `CommandTImeout` doesn't exist, but there is `WaitOnLocks`, which defaults to `True` and means *Specifies that a transaction wait for access if it encounters a lock conflict with another transaction* (according to the docs). Both of those are set in the connection's parameters. – Ken White May 21 '15 at 17:32
  • Despite all my attempts to configure the client differently, it seems to default to `nowait`. See my question to @TOndrej below. – nolaspeaker May 22 '15 at 05:44

3 Answers3

7

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.

Ondrej Kelle
  • 36,175
  • 2
  • 60
  • 122
  • Can you be more specific please? How do you specify lock timeout value at the client for a specific application? – nolaspeaker May 22 '15 at 05:43
  • @nolaspeaker It depends on the client library of your choice and how it exposes the transaction parameter block (TPB) which needs to be set up at the Firebird client API level. – Ondrej Kelle May 22 '15 at 08:22
  • I see, you're using DBX. Which version/Delphi version? – Ondrej Kelle May 22 '15 at 08:23
  • Delphi 7. I see the client libary is named dbxup_fb.dll, from Upscene productions version 2.3.0.2 2010-03-8. Could it be that this combination (with fbclient.dll) doesn't support the lock timeout value? – nolaspeaker May 22 '15 at 08:35
  • @nolaspeaker It could be. I have no experience with this Firebird DBX driver (in fact, with no Firebird DBX driver at all). I've only used IBObjects and FreeIB libraries so far with Firebird. – Ondrej Kelle May 22 '15 at 08:38
  • Thanks for your input. – nolaspeaker May 22 '15 at 10:03
3

Firebird transaction can be configured to be either nowait or wait (with or without a specific timeout). How this can be configured depends on the driver, and as I am not familiar with Delphi I can't comment on that. Nowait is usually the default as in most cases waiting will only delay the inevitable.

The "deadlock" error is a bit of a misnomer as it is not a deadlock in the normal concurrency vernacular. The second part of the error is usually more descriptive, eg Update conflicts with concurrent update., it is a historic artifact that it is grouped under "deadlock" (although in most cases this errors means that you need to restart your transaction, so in that sense it is "dead").

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
0

I am making use of the "answer your own question" button. I have discovered a solution.

  1. Install IBPhoenix opensource ODBC driver for Firebird/Interbase
  2. Configure ODBC DSN to connect to Firebird.fdb with nowait checked off, and LockTimeout set as required in seconds. I chose 15 seconds.
  3. Use Delphi 7 ADO (dbGo) TADOConnection configured to use Microsoft OLE DB Provider for ODBC drivers.
  4. This is the important bit: Set the ADOConnection.TransactionIsolation to either ilReadUncommited or ilDirtyRead.

What this does is causes the TADOQuery.ExecSQL to actually wait (for up to the specified 15 seconds) if it finds that the record has already been updated in a transaction that hasn't yet committed or rolled back!

This is different than the DBX driver which immediately raises a so-called "deadlock" exception in this situation. (as we discussed above)

So, if both queries do this

Update MYTABLE set NUM = NUM + 1 where keyvalue = 99;

and the starting value (before any update) is 0, the value of NUM after both transactions have committed is 2, as expected.

Starting again with NUM = 0. If the first transaction rollsback, the 2nd transaction can commit (or rollback). And the value after the second update has committed, is just 1.

I don't know how or why this works so well, expecially since Firebird isn't supposed to support ReadUnComitted or DirtyRead, but I'm just happy it works the way I want it to.

nolaspeaker
  • 1,868
  • 19
  • 38