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/
