Hi Abhi,
Here is one more idea. I believe that you said that you are running an
in-memory database. That means that you do not need to worry about
leaking pre-allocated sequence numbers when your application exits. You
can try setting the pre-allocation range to the maximum number with the
following system property:
-Dderby.language.sequence.preallocator=2147483647
When I try that setting, my repro program runs without any lock contention.
Hope this helps,
-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/
