You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Craig Russell <Cr...@Sun.COM> on 2005/06/14 01:42:35 UTC
Fwd: Auto generation of database keys
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:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Auto generation of database keys
Posted by TomohitoNakayama <to...@basil.ocn.ne.jp>.
Hello.
I meant "nontransactional" as that generation of key is not rollbacked....
Well , it was better to say partially nontransactioinal .....
Best regards.
/*
Tomohito Nakayama
tomonaka@basil.ocn.ne.jp
tomohito@rose.zero.ad.jp
Naka
http://www5.ocn.ne.jp/~tomohito/TopPage.html
*/
----- Original Message -----
From: "Mike Matrigali" <mi...@sbcglobal.net>
To: "Derby Development" <de...@db.apache.org>
Sent: Wednesday, June 15, 2005 2:26 AM
Subject: Re: Auto generation of database keys
> The implementation of identity values is transactional in that it
> uses transactions to guarantee no 2 rows are assigned the same
> number. It does not guarantee that there won't be lost values,
> nor does it guarantee any particular order (though the implementation
> does allocated the numbers in order - I believe the only thing an
> application can assume is some sort of unique value.
>
> TomohitoNakayama wrote:
>
>> Hello.
>>
>> I experimented ....
>>
>> ij> create table test(value int generated always as identity);
>> 0 rows inserted/updated/deleted
>> ij> insert into test(value) values(default);
>> 1 row inserted/updated/deleted
>> ij> select * from test;
>> VALUE
>> -----------
>> 1
>>
>> 1 row selected
>> ij> rollback;
>> ij> select * from test;
>> VALUE
>> -----------
>>
>> 0 rows selected
>> ij> insert into test(value) values(default);
>> 1 row inserted/updated/deleted
>> ij> select * from test;
>> VALUE
>> -----------
>> 2
>>
>>
>> Well ...
>> Key generation is nontransactional already ;)
>>
>> Taking aside my joke,
>> I think there exists room to optimize ...
>> I will file this into JIRA ...
>>
>>
>> Best regards.
>>
>>
>> /*
>>
>> Tomohito Nakayama
>> tomonaka@basil.ocn.ne.jp <ma...@basil.ocn.ne.jp>
>> tomohito@rose.zero.ad.jp <ma...@rose.zero.ad.jp>
>>
>> Naka
>> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>>
>> */
>>
>> ----- Original Message -----
>> *From:* Craig Russell <ma...@Sun.COM>
>> *To:* derby-dev@db.apache.org <ma...@db.apache.org>
>> *Sent:* Tuesday, June 14, 2005 8:42 AM
>> *Subject:* Fwd: Auto generation of database keys
>>
>> 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:Craig.Russell@sun.com
>> P.S. A good JDO? O, Gasp!
>>
>>
>> Craig Russell
>>
>> Architect, Sun Java Enterprise System
>> http://java.sun.com/products/jdo
>>
>> 408 276-5638 mailto:Craig.Russell@sun.com
>>
>> P.S. A good JDO? O, Gasp!
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> No virus found in this outgoing message.
>> Checked by AVG Anti-Virus.
>> Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005/06/11
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005/06/11
>
>
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.3/15 - Release Date: 2005/06/14
Re: Auto generation of database keys
Posted by Mike Matrigali <mi...@sbcglobal.net>.
The implementation of identity values is transactional in that it
uses transactions to guarantee no 2 rows are assigned the same
number. It does not guarantee that there won't be lost values,
nor does it guarantee any particular order (though the implementation
does allocated the numbers in order - I believe the only thing an
application can assume is some sort of unique value.
TomohitoNakayama wrote:
> Hello.
>
> I experimented ....
>
> ij> create table test(value int generated always as identity);
> 0 rows inserted/updated/deleted
> ij> insert into test(value) values(default);
> 1 row inserted/updated/deleted
> ij> select * from test;
> VALUE
> -----------
> 1
>
> 1 row selected
> ij> rollback;
> ij> select * from test;
> VALUE
> -----------
>
> 0 rows selected
> ij> insert into test(value) values(default);
> 1 row inserted/updated/deleted
> ij> select * from test;
> VALUE
> -----------
> 2
>
>
> Well ...
> Key generation is nontransactional already ;)
>
> Taking aside my joke,
> I think there exists room to optimize ...
> I will file this into JIRA ...
>
>
> Best regards.
>
>
> /*
>
> Tomohito Nakayama
> tomonaka@basil.ocn.ne.jp <ma...@basil.ocn.ne.jp>
> tomohito@rose.zero.ad.jp <ma...@rose.zero.ad.jp>
>
> Naka
> http://www5.ocn.ne.jp/~tomohito/TopPage.html
>
> */
>
> ----- Original Message -----
> *From:* Craig Russell <ma...@Sun.COM>
> *To:* derby-dev@db.apache.org <ma...@db.apache.org>
> *Sent:* Tuesday, June 14, 2005 8:42 AM
> *Subject:* Fwd: Auto generation of database keys
>
> 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:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>
> Craig Russell
>
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>
> 408 276-5638 mailto:Craig.Russell@sun.com
>
> P.S. A good JDO? O, Gasp!
>
>
>
> ------------------------------------------------------------------------
>
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005/06/11
Re: Auto generation of database keys
Posted by TomohitoNakayama <to...@basil.ocn.ne.jp>.
Hello.
I experimented ....
ij> create table test(value int generated always as identity);
0 rows inserted/updated/deleted
ij> insert into test(value) values(default);
1 row inserted/updated/deleted
ij> select * from test;
VALUE
-----------
1
1 row selected
ij> rollback;
ij> select * from test;
VALUE
-----------
0 rows selected
ij> insert into test(value) values(default);
1 row inserted/updated/deleted
ij> select * from test;
VALUE
-----------
2
Well ...
Key generation is nontransactional already ;)
Taking aside my joke,
I think there exists room to optimize ...
I will file this into JIRA ...
Best regards.
/*
Tomohito Nakayama
tomonaka@basil.ocn.ne.jp
tomohito@rose.zero.ad.jp
Naka
http://www5.ocn.ne.jp/~tomohito/TopPage.html
*/
----- Original Message -----
From: Craig Russell
To: derby-dev@db.apache.org
Sent: Tuesday, June 14, 2005 8:42 AM
Subject: Fwd: Auto generation of database keys
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:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: Fwd: Auto generation of database keys
Posted by Sunitha Kambhampati <ks...@gmail.com>.
To help debug this, try setting both derby.locks.monitor=true and
derby.locks.deadlockTrace=true . This will print the lock table dump
when the timeout or deadlock happens to derby.log. The lock table will
give information on what locks are being held etc..
Sunitha.
Mike Matrigali wrote:
>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:Craig.Russell@sun.com
>>P.S. A good JDO? O, Gasp!
>>
>>
>>Craig Russell
>>
>>Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>>
>>408 276-5638 mailto:Craig.Russell@sun.com
>>
>>P.S. A good JDO? O, Gasp!
>>
>>
>>
>>
>
>
>
Re: Fwd: Auto generation of database keys
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>
> Craig Russell
>
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>
> 408 276-5638 mailto:Craig.Russell@sun.com
>
> P.S. A good JDO? O, Gasp!
>
>
Re: Fwd: Auto generation of database keys
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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.
Is there any chance one of your transactions executed the ddl to
create the table or do any ddl and has not committed? 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:Craig.Russell@sun.com
> P.S. A good JDO? O, Gasp!
>
>
> Craig Russell
>
> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>
> 408 276-5638 mailto:Craig.Russell@sun.com
>
> P.S. A good JDO? O, Gasp!
>
>