At 12:29 PM 22/11/2004 +0000, you wrote:

>FB 1.5, IBOQuery
>
>I use a SP to get data for report (Report Builder). It does updates
>too before it select data.

This is NOT a good idea. Your report will represent uncommitted data.

>Generally I use Autocommit and pessimisticlocking in my application
>for user interface and explicit transactions when write record(s) in
>background or in special reports that do updates.
>If I use Commit after run my query it always locks record(s) until I
>close report,

That is why you should not use a select query to change records.

>instead with CommitRetaign or ServerAutocommit it
>releases ones record(s).

ServerAutocommit on a select procedure that performs updates still won't
commit any row until all of the rows have been output. As long as the
report is still asking for records and receiving them, any work in the SP
remains uncommitted.

>
> try
> IBTransaction2.StartTransaction;

I hope not. IBTransaction is part of IBX, not IBO. You should use
TIBOTransaction or, if you have a very old version of IBO, use TIB_Transaction.

>
> IBOQuery1.Open;
>
> IBTransaction2.Commit;
> except
> IBTransaction2.Rollback;
> raise;
> end;
>
>When I use commit or autocommit and an other client run the same
>report it get a deadlock error and if it try again it is as suspend
>to waiting and I am contrained to stop Firebird Service because I
>can't connect or run every query also in other program as IBExpert.

Exactly. You are in "double jeopardy" - not only do you have the report
using the output of SP and preventing the updates from being committed, but
you are using pessimistic locking. With two transactions running the same
SP, you have a true deadlock. If you also have this task inside a
ReadCommitted transaction, you have an extra risk here: the rows that are
output at the beginning of the report could well be inconsistent with those
at the end.

The way to do this is to have a Concurrency transaction that is set to
Autocommit on the client side. This is important, because you want to
retain the transaction context through a commit.
DON'T SET SERVER AUTOCOMMIT TRUE !!!
Set its LockWait property false. This will mean that any conflict will
cause the entire update part of the task to fail. You want this to be the
case, since the task must be atomic in order to produce a valid report
afterwards (in the revised workflow, below).

This task must be accomplished in two steps.

Write an executable SP that performs your updates.
Write a selectable SP that *only* outputs the rows for your report. Use
the same input parameters for both procedures.

Check that the transaction is not active - if it is, roll it back.
Start the transaction.
Execute the procedure.
If it succeeds, the transaction will be autocommitted at the client's
request (not the same thing as ServerAutocommit!!) and your transaction is
restarted with the same context. It can't see anything that was committed
by any other transaction, but it can see the work that it committed
itself. This is exactly what you want for the report.

Now, IF the update procedure succeeded, run your (rewritten) select
procedure (no updating in this one) to get the records for the report; and
run the report.
After running the report, commit the transaction, by calling Commit.

With this workflow, another user can run the same task on the same
rows. Any conflicts, the attempt to run the update procedure will
fail. In this case, the application simply rolls back the transaction
(calls Rollback specifically) and the user can try again later. The
"deadlock" reported in this case is not a deadlock, it's a lock conflict.

The hard rollback in case of failure is essential, since the "retained"
transaction context in Concurrency isolation would otherwise continue to
encounter a lock conflict.

Likewise, the hard commit once the report is done is essential to enable
the application to pick up the latest database state.

Helen