From: Mike Matrigali <[EMAIL PROTECTED]>
Date: June 14, 2005 10:45:16 AM
PDT
To: Derby Development <[email protected]>
Subject: Re: Fwd: Auto
generation of database keys
I have not been able to reproduce this issue.
Derby uses nested internal transactions when updating the
IDENTITY
column, it does not try to guarantee that a value won't be lost. These
locks should not be held until end of user transaction, they
are
committed separate from the user transaction, so the lock is held
for
the time it takes to update the system catalog row and commit
the
internal transaction.
There are some user level things the other transaction could
be
doing to block this:
o other
transaction did ddl on this table and has not committed.
o other
transaction did some sort of metadata select either
explicitly against derby system catalog or through jdbc
metadata call.
I assume you
have not set any of the lock timeout properties, it is
definitely
possible to block on this lock for a short time so setting
locktimeout
to -1 will also cause this issue, but default timeout should
not
be happening.
The best 1st step to debugging locking issues is to use the
properties
to get the system to dump out more information about the lock table
when
you encounter the lock timeout. Try setting
derby.locks.monitor=true.
In this case the interesting information is what locks are being held
by
the other transaction
blocking this one.
On a side note, what jvm/derby version are you using. Getting stack
traces with line numbers can help out a lot in debugging this
stuff.
I don't know if I get them because I am using a development build,
or
if it is the jvm environment I have. This should have nothing to
do
with the lock timeout, just makes it easier to diagnose the
issue.
Craig Russell wrote:
Hi,
I'm running into a locking issue when using
generated keys. My primary
key column is defined as DATASTORE_IDENTITY BIGINT
NOT NULL GENERATED
ALWAYS AS IDENTITY. I don't care about the key being
transactional. That
is, if a transaction rolls back, I can live with the
key that was
allocated being permanently unused.
My application has two transactions inserting rows
into the same table,
and the threads have internal synchronization such
that I need to have
both insert statements succeed independently. The
isolation level is the
default.
When I run the transactions, I get a timeout
exception indicating that
only one of the transactions can get an
autogenerated key and the other
has to wait until the first transaction commits.
This stack trace is
from the transaction that is waiting for the first
transaction to commit.
[java] ERROR 40XL1: A lock could not be obtained within the
time
requested
[java]
at
org.apache.derby.iapi.error.StandardException.newException(StandardExcep
tion.java)
[java]
at
org.apache.derby.impl.services.locks.LockSet.lockObject(LockSet.java)
[java]
at
org.apache.derby.impl.services.locks.SinglePool.lockAnObject(SinglePool.
java)
[java]
at
org.apache.derby.impl.services.locks.SinglePool.lockObject(SinglePool.ja
va)
[java]
at
org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(RowL
ocking3.java)
[java]
at
org.apache.derby.impl.store.access.conglomerate.OpenConglomerate.lockPos
itionForWrite(OpenConglomerat
e.java)
[java]
at
org.apache.derby.impl.store.access.conglomerate.GenericConglomerateContr
oller.fetch(GenericConglomera
teController.java)
[java]
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.getSetAutoincrement
Value(DataDictionaryImpl.java
)
[java]
at
org.apache.derby.impl.sql.execute.InsertResultSet.getSetAutoincrementVal
ue(InsertResultSet.java)
[java]
at
org.apache.derby.impl.sql.execute.BaseActivation.getSetAutoincrementValu
e(BaseActivation.java)
[java]
at
org.apache.derby.exe.ac40348015x0104x675cxbca4xffffdab5f0bf0.e0(Unknown
Source)
[java]
at
org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGenerate
dClass.java)
[java]
at
org.apache.derby.impl.sql.execute.RowResultSet.getNextRowCore(RowResultS
et.java)
[java]
at
org.apache.derby.impl.sql.execute.NormalizeResultSet.getNextRowCore(Norm
alizeResultSet.java)
[java]
at
org.apache.derby.impl.sql.execute.DMLWriteResultSet.getNextRowCore(DMLWr
iteResultSet.java)
[java]
at
org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.j
ava)
[java]
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPrepar
edStatement.java)
[java]
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatemen
t.java)
[java]
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Embed
PreparedStatement.java)
[java]
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(EmbedPre
paredStatement.java)
[java]
at
org.jpox.store.rdbms.request.Request.executeUpdate(Request.java:69)
[java]
at
org.jpox.store.rdbms.request.InsertRequest.execute(InsertRequest.java:25
3)
[java]
at
org.jpox.store.rdbms.table.ClassTable.insert(ClassTable.java:1673)
[java]
at
org.jpox.store.StoreManager.insert(StoreManager.java:634)
[java]
at
org.jpox.state.StateManagerImpl.internalMakePersistent(StateManagerImpl.
java:2940)
[java]
at
org.jpox.state.StateManagerImpl.makePersistent(StateManagerImpl.java:291
3)
Am I misusing the key generation? Can I get
nontransactional key generation?
Thanks,
Craig
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
