Subject Re: [IBO] Strange Transaction behaviour
Author Helen Borrie
At 09:28 PM 4/08/2004 +1000, you wrote:
> I'm trying to the following scenario to work, but I'm finding that
>the second transaction magically causes the first transaction to rollback!
>I have 2 queries that are under the control of Transaction A. 1 query
>for obtaining a record to be edited (SQL_A) and a second query to
>perform a lookup on one of those fields (SQL_B). When the user selects
>an option in the lookup list (ie when an OnAfterScroll event is
>triggered), I start Transaction B and open a 3rd query (SQL_C) that
>locks the selected record in the lookup table, using an " update lookup
>set columnA=columnA where columnA=:columnA", parameterized query.
>On the first selection, the record is correctly locked and no
>Transaction is rolled back. On the second OnAfterScroll event, rolling
>back Transaction B causes Transaction A to be rolled back as well.
>The code for locking the selected record, which is in the OnAfterScroll
>event, looks like this:
> sqlLockSs.Close;
> sqlLockSs.UnPrepare;
> if tranLock.InTransaction then
> tranLock.Rollback; // This causes Transaction A to also
> if not sqlLockSs.Prepared then
> sqlLockSs.Prepare;
> sqlLockSs.ParamByName('COLUMNA').AsString :=
> tranLock.StartTransaction;
> sqlLockSs.Open;
>Any ideas as to why it's actually happening? I thought having a separate
>Transaction would/should not affect the first?
>If I'm doing it wrong, then is there a correct way to lock the scrolled
>Info: Delphi 5, IBObject 4.3.Aa, Win2k, Firebird 1.5.1

Hmm, the first question I have to ask is why you think you have to lock the
lookup record? (After that, there would be more questions and some comments!!)