|Subject||Re: [firebird-support] 2 identical record inserts not stopped by UNIQUE constraint|
Ann W. Harrison wrote:
> Stan wrote:Is it a bug or not a bug ?
>> 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 )
>> SELECT id FROM strings WHERE string = :string INTO :string_id;
>> IF( :string_id IS NULL ) THEN
>> string_id = GEN_ID (strings_generator, 1);
>> INSERT INTO strings VALUES( :string_id, :string );
> 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
> 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.