John Hopfield wrote:
Quote:
Originally Posted by
On 3 Lug, 03:19, "Dave Hughes" <d...@waveform.plus.comwrote:
Quote:
Originally Posted by
You don't need a COMMIT in thestoredproc definition - you need to
commit the transaction in your client's code.
My Delphi knowledge is rather rusty (v6), but you should find a
Commit method on the connection object (depending on the connection
architecture you're using ... I think the ADO components used
something different like EndTran, but all the rest used methods
named Commit and Rollback). Anyway - that's what you'll need to
call to commit the transaction.
Cheers,
Dave.
|
>
Thank you Dave.
But when i call TDataBase.Commit method it say that there is not an
active transaction.
So i think that is not a delphi problem.
|
Ah, you'll need to "start" the transaction with the StartTransaction
method first. In the case of DB2 this is essentially a no-op (you're
always "in a transaction" if you're connected to a DB2 database), but
the BDE connection architecture needs it anyway.
So, the code for calling your SP should look something like the
following (assuming no silly errors:
{ DB is a connected TDatabase component, SP is a TStoredProc component
with the various properties (StoredProcName, Params, etc.) already
filled out }
DB.StartTransaction;
try
SP.ExecProc;
DB.Commit;
except
on E: Exception do begin
// Something's gone horribly wrong - rollback the
// transaction and report the error
DB.Rollback;
MessageDlg(E.Message, mtError, [mbOk], 0);
end;
end;
Quote:
Originally Posted by
Yesterday i found this...
In my Stored Procedure there are some "select into".
When i put the "WITH UR" clause at the end of these "select"
the lock on my table disappears...so the problem is resolved...
>
(no COMMIT at the end of my SP)
>
But there is another problem...
>
I don't have a good knowledge of transactions and isolation
levels...but...
i think that a select "WITH UR" it's a risk...because maybe its
possible
to retrieve "dirty" data not committed by another client...
and i don't want so...
|
That's correct. WITH UR is "with uncommitted read"; in other words it
permits the query to read changed rows which haven't yet been committed
by other transactions. If you want to ensure that a particular
transaction isolation level is used (other than whatever the client
stipulates), I'd suggest WITH CS.
Speaking of which, what's the TransIsolation property of your TDatabase
component set to? If its set to tiRepeatableRead, then that's your
problem - set it to tiReadCommitted (the default) instead (I /think/
tiReadCommitted translates to CS in the case of DB2 - I'm not sure
though - I'll try and do a bit of digging later).
Anyway - the main problem here is long running transactions. If you're
not explicitly committing the transaction, then its remaining active
(and potentially holding locks) for as long as you're connected to the
database. Using something like the code above to ensure that the stored
proc execution is committed (or rolled back) ASAP should fix any
contention issues you're having. Still, check the TransIsolation
property too.
Cheers,
Dave.
