Ann W. Harrison wrote:

> 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
>

Is it a bug or not a bug ?

Regards
Boguslaw