Rick,
Thanks for your help in debugging the problem.
I run in memory only while testing, actual application does use persistent
file system backed database. I did try to use different preallocator ranges
(100, 1000 etc), it did not make any difference.
On Sun, May 21, 2017 at 12:20 AM, Rick Hillegas <rick.hille...@gmail.com>
wrote:
> Hi Abhi,
>
> Here is one more idea. I believe that you said that you are running an
> in-memory database. That means that you do not need to worry about leaking
> pre-allocated sequence numbers when your application exits. You can try
> setting the pre-allocation range to the maximum number with the following
> system property:
>
> -Dderby.language.sequence.preallocator=2147483647 <(214)%20748-3647>
>
> When I try that setting, my repro program runs without any lock contention.
>
> Hope this helps,
> -Rick
>
> On 5/20/17 5:34 AM, Abhirama wrote:
>
> Hello Rick,
>
> With your guidance I was able to dig more into the problem.
>
> IDENTITY_VAL_LOCAL() is not being issued by hibernate but by Derby itself.
> Hibernate does use getGeneratedKeys. When it issues getGeneratedKeys(),
> the call is being directed to
>
> public final java.sql.ResultSet getGeneratedKeys() throws SQLException {
> checkStatus();
> if (autoGeneratedKeysResultSet == null)
> return null;
> else {
> execute("VALUES IDENTITY_VAL_LOCAL()", true, false,
> Statement.NO_GENERATED_KEYS, null, null);
> return results;
> }
> }
>
> present in org.apache.derby.impl.jdbc.EmbedStatement class. As you can
> see, this executes "VALUES IDENTITY_VAL_LOCAL()" SQL statement.
>
> Is getGeneratedKeys internally supported by Derby using IDENTITY_VAL_LOCAL()?
> What am I missing here? Is there a way around this?
>
> On Sat, May 20, 2017 at 5:49 AM, Rick Hillegas <rick.hille...@gmail.com>
> wrote:
>
>> On 5/18/17 9:12 PM, Abhirama wrote:
>>
>> 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?
>>
>> Hi Abhi,
>>
>> Yes, that should work. It will be slightly different than
>> IDENTITY_VAL_LOCAL() if an identity-generating statement encounters an
>> error which rolls back its updates. In that case, there will be holes in
>> the sequence. But that may be good enough for Hibernate's purposes. I can't
>> say.
>>
>> Again, I wonder about the semantics of IDENTITY_VAL_LOCAL() in a highly
>> concurrent, INSERT-intensive application. It is not clear to me what a
>> given session expects from this function. The JDBC approach to retrieving
>> the keys generated by the current session is to use
>> java.sql.Statement.getGeneratedKeys().
>>
>>
>> Hope this helps,
>> -Rick
>>
>>
>> On Fri, May 19, 2017 at 5:18 AM, Rick Hillegas <rick.hille...@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.fore...@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/
>>
>>
>>
>
>
> --
> Cheers,
> Abhi
> https://getkwery.com/
>
>
>
--
Cheers,
Abhi
https://getkwery.com/
