Subject Re: [ib-support] wait transactions and rollback
Author Helen Borrie
At 12:09 PM 25-06-02 +0200, you wrote:
>Hi All,
>I'm having a conflict with a wait transaction (free ib components). In the
>client front i have a exec query asociated with a wait trasaction. This
>query execute a SP from the database.
>create procedure getcounter(type char(3)) returns (counterValue smallint)
>declare variable tmpCounter smallint;
>select counter from counters
>where (type = :type)
>into :tmpCounter
>if (tmpCounter is not null) then
> begin
> tmpCounter = tmpCounter + 1;
> update counters
> set counter = tmpCounter
> where (type = :type);
> end
>When two or more client's front call SP only one of them get the correct
>values of counter, the other client frontl look like nothing was happen (is
>comming back to the last state before start de trasaction). I ´m sure the
>trasaction is comming back without wait. Can any body say me where could be
>the problem? Maybe inside Free-IB Transaction component, or maybe a
>conceptual problem of mine.

Sadly, it's a conceptual problem. This approach to serial number
generation works great on a desktop database with a single user. You will
need to abandon it for mult-user purposes as it's unsafe; and the lock
conflicts protect you from doing it.

Don't set this transaction to WAIT. Make it NO WAIT and force your
application to roll back all transacs that encounter a lock conflict.

But don't persevere with this approach. Find another way to generate your
counters. The obvious one is to use a generator, which will perform the
incrementing for you and always guarantee that the number will get
incremented in strict sequence.


All for Open and Open for All
Firebird Open SQL Database · ·