Now, it is clear.

I have this setting:
Isolation= tiConcurrency
RecVersion=True;

Following there is new code;

if IB_Transaction2.TransactionIsActive then
IB_Transaction2.Rollback
else
begin
try
IB_Transaction2.StartTransaction;
try
IB_StoredProcedureUpdateData.ExecProc;
except
IB_Transaction2.Rollback;
end;

try
IBOQueryReport.Prepare;
IBOQueryReport.Open;
except
IB_Transaction2.Rollback;
end;

IB_Transaction2.Commit;
except
IB_Transaction2.Rollback;
end;
end;

Thank You

----- Original Message -----
From: "Helen Borrie" <helebor@...>
To: <IBObjects@yahoogroups.com>
Sent: Monday, November 22, 2004 2:10 PM
Subject: Re: [IBO] Problem with Stored Procedure - Server AutoCommit -
Commit - CommitRetaign


>
> 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
>
>
>
>
>
> ___________________________________________________________________________
> IB Objects - direct, complete, custom connectivity to Firebird or
> InterBase
> without the need for BDE, ODBC or any other layer.
> ___________________________________________________________________________
> http://www.ibobjects.com - your IBO community resource for Tech Info
> papers,
> keyword-searchable FAQ, community code contributions and more !
> Yahoo! Groups Links
>
>
>
>
>
>
>



--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f

Sponsor:
Rivoluzione tecnologica e il fax va in soffitta...oggi i tuoi fax li ricevi sul PC! Scopri come cliccando qui
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=1628&d=22-11