At 04:13 PM 22/12/2004 +0100, you wrote:

>Hi
>
>I'm working with IBX components,C++Builder 6.0
>and Firebird-1.5.2.4731-0_RC5-Win32
>
>I have TIBSQL component connected to TIBTransaction with parameters set to
>"concurrency ,wait", there are also property IdleTimer=1 and
>DefaultAction=TARollback.
>Suppose we have a deadlock

>(or only lock, depending in witch way a will finish the first transaction)

I don't know what this means...a pessimistic lock maybe?

> - I started an "update" transaction in IBExpert (changed a value in one
> field without doing commit) and then executed "update" command using
> IBSQL (example below). I want IBSQL will rollback the transaction after 1
> second, now it waits for infinity. In my opinion it shouldn't wait for
> infinity it should be a way to set the timeout of the transaction and
> posibility to catch some exception.

A transaction that has posted work in WAIT state is not an idle
transaction. It's an active transaction waiting for something to happen.

This isn't a Delphi forum and I don't know exactly how the IdleTimer in
IBX is implemented...but it is some kind of client-side mechanism that will
kick in when the user goes out to lunch leaving unfinished transactions
that have no work pending. It has no effect on how long a WAITing
transaction will wait.

A deadlock is a transaction state that has to be resolved. WAIT tells the
transaction that, if it encounters a conflict, it is to wait until the
conflicting transaction is finished and then try again to post the
work. If the other transaction actually commits, then the outcome will be
that your concurrency transaction will fail with a locking conflict; if
the other transaction rolls back, then your transaction has some chance of
succeeding in posting its work and will complete by committing.

Under WAIT resolution, the db engine reports all conflicts as deadlocks,
even when there isn't a deadlock but just a conflict. In the case of a
true deadlock - where Trans A is waiting to update a row that Trans B has
already posted an update for and Trans B is waiting to update a row that
Trans A has already posted an update for - there is no way to resolve it
unless one transaction rolls back.

>Additionally there is probably something wrong with the firebird server
>when a large number of such transactions is started and not finished (by
>killing application process), server starts working very slow, sometimes
>it is even impossible to connect to database with IBExpert..

It's not "wrong" - it's a question of transaction management (or, rather,
lack of it). If you have deadlocked transactions sitting around waiting
forever in WAIT state, you are building up an ever-increasing transaction
state bitmap in memory. As these resources get used up, the server runs
slower and slower until, at some point, there's nothing left and the server
hangs. On a server that is not well-resourced, this will happen sooner
rather than later.

It's also a characteristic of applications written with IBX that
transactions are set by default to use CommitRetaining and a corresponding
client-side hack called RollbackRetaining. (IBX's AutoCommit property).

CommitRetaining (or "soft commit") never frees resources. This is OK (and
useful) for groups of "current row" operations being kicked off a scrolling
dataset - typically in a Delphi app, a SELECT statement populating a
DBGrid, with a Navbar enabling the user to launch single-row
edit/insert/delete operations using methods of the dataset.

The problems come if the developer never provides a mechanism to do a "hard
commit" on that transaction, or doesn't do a "hard rollback" (by explicitly
calling Commit or Rollback, respectively). To the system, these are
long-running transactions; and they prevent the transaction state bitmap
from being kept to a steady size by the continual cleanup process that goes on.

>IdleTimer property description from Borland Help is:
>
>"Specifies how long the transaction should wait before automatically
>committing or rolling back. Use IdleTimer to indicate how long a
>transaction should be allowed to remain idle before automatically
>committing or rolling back the data. Use DefaultAction to determine which
>action the transaction should take"

As you see, it tells you to use IdleTimer to indicate how long a
transaction should remain idle. With an IBX application, the typical "idle
transaction" is the one where the user came in at 9 a.m., started her
computer, opened an app with a "browsing interface" over a table that she
plans to use at various times through the day. The developer has used all
the defaults and, in IBX, is probably using a TClientDataset behind the
scrolling interface. That's a transaction that just runs forever, since
the dataset itself has no way to know whether the clientdataset has pending
work until ApplyUpdates is called. It waits eternally on the assumption
that the clientdataset has pending work and is going to be called some
time. This is the classic "gone to lunch" syndrome, built into the
application architecture itself. The IdeTimer won't help here.

To this, add your IBSQL ops that never end because they are waiting forever
for the tooth fairy to come along and break a deadlock. All these active
transactions just pile up, eating resources.

>an example of code:
>
>try {
> IBSQL->Close();
> IBSQL->Transaction->StartTransaction();
> IBSQL->SQL->Text = "UPDATE table_name SET filed_name=1 WHERE id=1";
> IBSQL->ExecQuery();
> IBSQL->Transaction->Commit();
> } catch (Exception &E) {;}

This kind of hard-committed, one-off operation is good, provided (1) you
don't use a WAIT transaction where there is a high likelihood of a conflict
; and (2) you provide explicit handling for a Commit that doesn't take
place. In WAIT resolution, your code here will simply stall forever if
there is a conflict.

>I think it is probably an academic problem but it happend to me. Now I
>changed te transaction propery to "nowait" but sometimes this is not a
>good solution.

That's indeed true where you wish to "queue" the operations of different
users on the same sets. If you need to use WAIT, write a handler with a
timer to limit how long the transaction will wait...after n ticks, roll the
transaction back and try again in the context of a new transaction.

./hb