This is not just happening in one transaction,

I open a new isql session and I see the 2 records.

I run: gfix -commit all

and opened a new isql and I still see the 2 record.

This means that the database actually has the 2 records, correct?

thanks,

stan


--- In firebird-support@yahoogroups.com, "Ann W. Harrison"
<aharrison@...> 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 have not had issues with this yet, but I will take your word for it
and change my procedures.

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