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 Sat, 20 May 2017 17:40:26 GMT
Hi Abhi,

Thanks for investigating further. You have uncovered a defect in Derby's 
implementation of Statement.getGeneratedKeys(). The defect must have 
been introduced when we re-implemented identity columns on top of 
sequence generators. I have reproduced the problem which you are seeing 
and have opened the following bug report: 
https://issues.apache.org/jira/browse/DERBY-6934. I have attached a 
repro to that report.

I don't have any clever workaround to recommend. It sounds as though 
your application does not need to call Statement.getGeneratedKeys(). I 
don't know if there is a way to prevent Hibernate from calling that method.

Thanks for reporting this problem. I will give some thought to how we 
can fix it.

Thanks,
-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.hillegas@gmail.com <mailto:rick.hillegas@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.hillegas@gmail.com <mailto: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 <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/
>>
>>
>>
>>
>>
>>     -- 
>>     Cheers,
>>     Abhi
>>     https://getkwery.com/
>
>
>
>
>
> -- 
> Cheers,
> Abhi
> https://getkwery.com/



Mime
View raw message