db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mike matrigali <mikema...@gmail.com>
Subject Re: Primary key auto increment sometimes fails
Date Mon, 15 Dec 2014 18:59:01 GMT
you should include exact ddl you use to create the table's primary key, 
and the
version of derby you used to create the table.

I believe that this is expected behavior but will be sure if you post
ddl.   I think all derby guarantees is that the primary keys will
be unique, not that they will have no "holes".  I believe this is SQL
standard compliant.

The underlying reason for the implementation is multi-user performance.
If the system needed to guarantee no holes, then it would logically
have to have lock all inserts by all other transactions while a single
transaction had inserts outstanding to make sure those inserts did not
abort, thus stopping all work by other threads on the table by all
other transactions until this transaction committed or aborted.

/mikem

On 12/13/2014 10:24 AM, pzsolt wrote:
> Hi!
>
> I have noticed that when there is a table with an auto generated primary
> key, the auto incrementation by INSERT fails. Instead of increment the value
> by 1, sometimes Derby increments the primary key with 100 or 1000 or other
> random value.
>
> I can't reproduce it, because it is random.
>
> For example, I have a table named 'INVOICE', and i have inserted 4 rows, and
> I get the following the auto generated keys:
>
> 1. INSERT: auto generated primary key: 806
> 2. INSERT: auto generated primary key: 807
> 3. INSERT: auto generated primary key: *904*
> 4. INSERT: auto generated primary key: *1004*
> 5. INSERT: auto generated primary key: 1005
>
> It should be incremented by 1. The expected sequence should be:
>
> 1. INSERT: auto generated primary key: 806
> 2. INSERT: auto generated primary key: 807
> 3. INSERT: auto generated primary key: 808
> 4. INSERT: auto generated primary key: 809
> 5. INSERT: auto generated primary key: 810
>
> C. a. 188 companies are using my Derby based software and I don't know what
> to do with this random error. And I know nobody who could help me.
>
> Has anybody met with this strange error? Do you have any suggestions, how to
> start to debug it? I can't reproduce it.
>
> Best regards,
> Zsolt Pocze
>
>
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/Primary-key-auto-increment-sometimes-fails-tp143465.html
> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>


Mime
View raw message