benno wrote:

> Hi,
>
> This problem has nothing to do with IB or FB. You use an SP to update the
> number. Transactions can not "see" each others transactions until commit, so
> you'll be wrong in your number here (at least in a multiuser environment).
>
> Use a generator to generate your billing numbers, they work outside
> transaction scope and are garanteed unique.

Generators are outside of transaction control, so if the transaction is
rolled back, the numbers will still go up. You need some safer way to do
this.

I have table counter with rows:
record_id
table_name

When I insert new row, I begin the transaction and do:
select record_id
from counter
where table_name = 'table I'm updating'
into :new_id_value;

new_id_value = new_id_value + 1;

update counter set record_id = :new_id_value;

This locks the record in table counter for other users, and they cannot
get a new number until transaction is commited or rolled back. If
transaction is rolled back, the update statement's effect are gone, and
the next user can get that unused number.

This is just one of the ways to do it.

If you want more help, please post more detailed explanation of your
system (what exactly do does SP's do? how do you get the numbers? do you
use generators? etc...)

--

Milan Babuskov
http://fbexport.sourceforge.net