----- Original Message -----
From: "ajose_s18" <ajose_s18@...>
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, March 30, 2011 1:40 PM
Subject: [firebird-support] ID duplicated


> 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
>
>
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>