db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Identity column and 40XL1 error
Date Thu, 18 May 2017 23:48:33 GMT
Hi Abhi,

You may have tripped across a problem with the IDENTITY_VAL_LOCAL() 
function. When identity columns were re-worked to use sequence 
generators, concurrency tests were run which involved many writers, that 
is, many sessions which concurrently issued INSERT statements. I don't 
recall much testing done with competing sessions which issued 
IDENTITY_VAL_LOCAL() calls.

In the case when you have multiple concurrent writers, what is the 
meaning you expect from IDENTITY_VAL_LOCAL()? It is possible that the 
SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY() system function may give you a 
result you can work with. It is likely that 
SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY() will play better with the underlying 
sequence generator.

If you can script the problem, please open a bug report.

Hope this helps,
-Rick



On 5/18/17 2:20 AM, Abhirama wrote:
> As you can see from my post, lock is denied because of "values 
> identity_val_local()" issued by a competing insert on the same table. 
> This is also asserted by the the fact that, in application, if I 
> synchronise all the offending inserts(only inserts, not selects), I do 
> not get a lock exception. I find it really hard to believe that derby 
> locks out on a couple of concurrent inserts.
>
> On Thu, May 18, 2017 at 1:03 PM, John English <john.foreign@gmail.com 
> <mailto:john.foreign@gmail.com>> wrote:
>
>     On 18/05/2017 08:29, Abhirama wrote:
>
>         Hello,
>
>         I am facing 40XL1 error when I try to insert rows into a table
>         with an
>         identity column. Identity column has been created using "id
>         integer
>         generated by default as identity (START WITH 100, INCREMENT BY
>         1)". This
>         is also the primary key for the table. Start with 100 is used
>         because I
>         use 1 to 99 range to insert deterministic values for test cases.
>
>
>     Usual reason is some other query has a lock on the table -- maybe
>     you did a SELECT involving that table and forgot to close the
>     ResultSet?
>     -- 
>     John English
>
>
>
>
> -- 
> Cheers,
> Abhi
> https://getkwery.com/



Mime
View raw message