IDS Forum

Problem with sequences

Posted By: Marcus Haarmann
Date: Wednesday, 7 June 2006, at 5:37 a.m.

Hi,

we have a weird problem on our development server, which is not
reconstructable. Maybe anybody here encountered a similar problem. Any hint
could help us (though it is not really critical, but a design decision could
be dependent on the solution):

We have a simple table, where we create users and their ids in (integer
column, varchaar column).
The integer col is the primary key, no other constraints.
We set the primary key using a sequence (cause this is relatively compatible
with other db implementations such as Oracle, DB2 etc).
In our program we call "select seq_user_repository.nextval from systables
where tabid = 1". This gives us the next value for out primary key.
Today, we got an unique key violation, cause the sequence value was lower
than the last value in the table !
There was definitely no alter sequence done since the last insert.

As a speciality, we access the db in a distributed transaction (XA from
Java), which spans over 2 databases (on the same instance in this case, but
not necessarily).

The last record had the id 118 and was inserted correctly (the transaction
was committed in both databases successfully). Since the value is always
generated by the sequence, the program must have read the sequence and the
return was 118. I would expect the sequence to give 119 as next value now,
but it gave 116 as output today.

We are using IDS 10.00FC4 on Sun Solaris 10 (Sparc architecture).

The only thing I can think of is that the reason is connected to a power
failure (the machine has no UPS, since it is only a development server).
This was the case some days ago and could be the reason for this problem.
During recovery the sequence was maybe reset to a wrong value, because the
value was only changed in buffer, not on disk and a flush did not include
the last sequence value.
This is extremely problematic, if the recovery threads do not fix the
sequence values. This might be the case, since sequences are in my
understanding something outside the transaction scope (the value is
increased also in rollback case).

I remember, the first time we had such a problem (on another table, but
situation is the same), we had a crash before, due to a long transaction,
which was aborted.

Any ideas, or did anybody encounter a similar situation ? Maybe this is a
bug in Informix 10 ?

Marcus

IDS Forum is maintained by Administrator with WebBBS 5.12.