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 00:19:08 GMT
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/



Mime
View raw message