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/