db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Abhirama <abhyr...@gmail.com>
Subject Re: Identity column and 40XL1 error
Date Fri, 19 May 2017 04:12:25 GMT
Rick,

My code is not explicitly firing IDENTITY_VAL_LOCAL() call, my best guess
is hibernate, but I can confirm this by enabling hibernate logging. Will do
that and confirm.

I assume hibernate is issuing this to get the id of the last inserted row
so that it can hydrate the ORM model with this data. As per your
recommendation, if IDENTITY_VAL_LOCAL is not used, how do I get the last
inserted id? I read about SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY() and it says
it will give the next value assigned to an identity column, not the last
generated one. Are you saying something along the lines of subtract 1 from
this value and use that or am I missing something?

On Fri, May 19, 2017 at 5:18 AM, Rick Hillegas <rick.hillegas@gmail.com>
wrote:

> 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>
> 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/
>
>
>


-- 
Cheers,
Abhi
https://getkwery.com/

Mime
View raw message