Hi,

Recently I've been having this issue, I found that I have some ID's repeated in a table that must be unique, my table is something like this:

create table corte
{
date integer,
number integer,
product varchar(20),
somedata integer,
constraint pk_corte primary key(date, number, product)
};

So I need to have a sequence by date of the field number, like, each day I start with the number 1, everything is fine, it works, but it started to duplicate some numbers in the field number. To get the next number I do for example "select max(number) from corte where date = 20110101", next I sum 1 to this number and then I do and insert into the table. The problem raises when I have multiple clients, and for some reason the transaction reads the same max(number) from the table. I don't lose data because of the field product, always is diferent.

For example
|Status |Transaction 1 |Transaction 2|Transaction 3|
|Time Start trans |9:00 |9:04 | 9:09 |
|Time commit trans |9:05 |9:09 | 9:13 |
|Result Max(number)|20 |20 | 21 |
|Result Inserted |21 |21 | 22 |

I put random time to see the effect, the problem I think resides in the Select.

Here is the sequence and the querys I'm using(pseudocode):

commit;
select max(number) as lastNumber from corte where date = 20110101;
lastNumber++;
insert into corte (date, number, product, somedata) values (20110101, lastNumber, 'some', 123);
commit;

Right now I can't modified the structure of the table, because I will start loosing data, and is an automated process.

How can I manage this situation?

I'm using Firebird 1.5, C++Builder from Embarcadero