db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: Primary key auto increment sometimes fails
Date Mon, 15 Dec 2014 20:53:58 GMT
More info on this can be found in the Reference manual. One relevant
section in "CREATE TABLE statement" has following under it.

Derby keeps track of the last increment value for a column in a cache. It
also stores the value of what the next increment value will be for the
column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS
system table. Rolling back a transaction does not undo this value, and thus
rolled-back transactions can leave "gaps" in the values automatically
inserted into an identity column. Derby behaves this way to avoid locking a
row in SYS.SYSCOLUMNS for the duration of a transaction and keeping
concurrency high.


On Mon, Dec 15, 2014 at 10:59 AM, mike matrigali <mikemapp1@gmail.com>
wrote:
>
> 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