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)
>as
>declare variable tmpCounter smallint;
>begin
>
>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
>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.

heLen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________