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 <mailto: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 <mailto: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/
