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
<[email protected] <mailto:[email protected]>> 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
<[email protected] <mailto:[email protected]>> 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
<[email protected] <mailto:[email protected]>> 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/
