Stan wrote:

>
> I have a table strings, and a stored procedure get_string_id
> that inserts data into this table:
> CREATE OR ALTER PROCEDURE get_strings_id( string VARCHAR(252))
> RETURNS( string_id BIGINT )
> AS
> BEGIN
> SELECT id FROM strings WHERE string = :string INTO :string_id;
> IF( :string_id IS NULL ) THEN
> BEGIN
> string_id = GEN_ID (strings_generator, 1);
> INSERT INTO strings VALUES( :string_id, :string );
> END
> SUSPEND;
> END!!

As an aside, you should probably set string_id to null before the
select. I believe that its contents aren't guaranteed when the
select returns no results.

I did run your query single user and got the expected results -
each string went in exactly once. There is an obscure condition
that occurs in Firebird (and probably InterBase as well) where
a concurrency transaction sees apparent duplicates in a unique
or primary key index. It happens this way.

Transaction 1 starts
Transaction 1 stores the value 'abc' in a field with a unique index
Transaction 1 commits

Transaction 2 starts

Transaction 3 starts
Transaction 3 deletes the record containing the value 'abc'
Transaction 3 commits


Transaction 2 stores the value 'abc'
Transaction 2 reads the table and sees

field
------
'abc'
'abc'

The first entry is the old version created by Transaction 1 which
is visible to Transaction 2, although it is actually deleted and
the delete is committed. The second entry is the row stored by
Transaction 2. In fact, the database is correct, but Transaction 2
has an incorrect view.


Regards,


Ann