You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Bruno René Santos <br...@holos.pt> on 2011/06/16 17:36:55 UTC
Oracle Sequences & Cache
Hello all,
We had a problem on one of our applications on Oracle because, we think,
of the cayenne cache and we wanted to have some sort of confirmation if
we are seeing this correctly...
On Oracle, in order to get the next PK we need to execute the query:
Select sequence_name.next_val from DUAL
Sometimes the query logger did not print this command before an INSERT.
After some testing we reached to the conclusion that this was a cache
issue and so we overrode CommitChanges and CommitChangesToParent in
order to clear the query cache after the commit phase, like this:
@Override
public void commitChanges() throws CayenneRuntimeException {
super.commitChanges();
getQueryCache().clear();
}
@Override
public void commitChangesToParent() {
super.commitChangesToParent();
getQueryCache().clear();
}
We think this is a bug because on the PK generation phase on cayenne no
data, always, should be ever retrieved from the cache, which would lead
to repeated primary keys...
Can this be true?
Thanx
Bruno
--
Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> |
Gestor de Projectos | Analista | Programador | Investigador
Holos - Soluções Avançadas em Tecnologias de Informação S.A.
Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they are
addressed. If you are not the intended recipient or the person
responsible for delivering the email to the intended recipient, be
advised that you have received this email in error and that any use,
dissemination, forwarding, printing, or copying of this email is
strictly prohibited. If you have received this email in error please
notify Bruno René Santos by telephone on +351 210 438 686
Re: Oracle Sequences & Cache
Posted by Andrus Adamchik <an...@objectstyle.org>.
Cayenne PK generator caches blocks of 20 keys for each entity by default to reduce the number of DB trips for such trivial
things as PKs.
> We think this is a bug because on the PK generation phase on cayenne no data, always, should be ever retrieved from the cache, which would lead to repeated primary keys...
No this is not a bug, however the trick is that the underlying sequence should be configured to increment by 20, which is the case if the sequence was generated from the Modeler. (This is a general rule if of course, so if you'd change the key cache to any value from 1 to infinity, the sequences should be updated accordingly).
> @Override
> public void commitChanges() throws CayenneRuntimeException {
> super.commitChanges();
> getQueryCache().clear();
> }
>
> @Override
> public void commitChangesToParent() {
> super.commitChangesToParent();
> getQueryCache().clear();
> }
The above won't have any effect on PK caching. However you can configure desired sequence cache in the Modeler:
http://people.apache.org/~aadamchik/sequence.png
Andrus
On Jun 16, 2011, at 6:36 PM, Bruno René Santos wrote:
> Hello all,
>
> We had a problem on one of our applications on Oracle because, we think, of the cayenne cache and we wanted to have some sort of confirmation if we are seeing this correctly...
>
> On Oracle, in order to get the next PK we need to execute the query:
>
> Select sequence_name.next_val from DUAL
>
> Sometimes the query logger did not print this command before an INSERT. After some testing we reached to the conclusion that this was a cache issue and so we overrode CommitChanges and CommitChangesToParent in order to clear the query cache after the commit phase, like this:
>
> @Override
> public void commitChanges() throws CayenneRuntimeException {
> super.commitChanges();
> getQueryCache().clear();
> }
>
> @Override
> public void commitChangesToParent() {
> super.commitChangesToParent();
> getQueryCache().clear();
> }
>
> We think this is a bug because on the PK generation phase on cayenne no data, always, should be ever retrieved from the cache, which would lead to repeated primary keys...
>
> Can this be true?
>
> Thanx
> Bruno
> --
> Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> | Gestor de Projectos | Analista | Programador | Investigador
>
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>
>
> This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient or the person responsible for delivering the email to the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify Bruno René Santos by telephone on +351 210 438 686
>
Re: Oracle Sequences & Cache
Posted by Andrus Adamchik <an...@objectstyle.org>.
There's something else at play then. As I mentioned in the earlier message, your code example does not affect PK generation at all.
Andrus
On Jun 22, 2011, at 12:27 AM, Bruno René Santos wrote:
> That did not solve the problem... Maybe the use of shared cache is a
> problem? I turn off the cache alltogether and the problem did not happen
> anymore.
>
> Regards
> Bruno
>
> On Sun, Jun 19, 2011 at 9:54 AM, Andrus Adamchik <an...@objectstyle.org>wrote:
>
>>
>> On Jun 16, 2011, at 7:49 PM, Chris Poulsen wrote:
>>
>>> <db-key-generator>
>>> <db-generator-type>ORACLE</db-generator-type>
>>>
>> <db-generator-name>PK_CONTACT_PERSON_KEY_SEQ</db-generator-name>
>>> <db-key-cache-size>1</db-key-cache-size>
>>> </db-key-generator>
>>
>> Yep, that's the trick to map a sequence with increment of 1. On the Modeler
>> side it is configured here:
>>
>> http://people.apache.org/~aadamchik/sequence.png
>>
>> Andrus
>>
>>
>>
>
>
> --
> Bruno René Santos | brunorene@holos.pt | Gestor de Projectos | Analista |
> Programador | Investigador
>
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>
> This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> If you are not the intended recipient or the person responsible for
> delivering the email to the intended recipient, be advised that you have
> received this email in error and that any use, dissemination, forwarding,
> printing, or copying of this email is strictly prohibited. If you have
> received this email in error please notify Bruno René Santos by telephone on
> +351 210 438 686
Re: Oracle Sequences & Cache
Posted by Bruno René Santos <br...@holos.pt>.
That did not solve the problem... Maybe the use of shared cache is a
problem? I turn off the cache alltogether and the problem did not happen
anymore.
Regards
Bruno
On Sun, Jun 19, 2011 at 9:54 AM, Andrus Adamchik <an...@objectstyle.org>wrote:
>
> On Jun 16, 2011, at 7:49 PM, Chris Poulsen wrote:
>
> > <db-key-generator>
> > <db-generator-type>ORACLE</db-generator-type>
> >
> <db-generator-name>PK_CONTACT_PERSON_KEY_SEQ</db-generator-name>
> > <db-key-cache-size>1</db-key-cache-size>
> > </db-key-generator>
>
> Yep, that's the trick to map a sequence with increment of 1. On the Modeler
> side it is configured here:
>
> http://people.apache.org/~aadamchik/sequence.png
>
> Andrus
>
>
>
--
Bruno René Santos | brunorene@holos.pt | Gestor de Projectos | Analista |
Programador | Investigador
Holos - Soluções Avançadas em Tecnologias de Informação S.A.
Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
If you are not the intended recipient or the person responsible for
delivering the email to the intended recipient, be advised that you have
received this email in error and that any use, dissemination, forwarding,
printing, or copying of this email is strictly prohibited. If you have
received this email in error please notify Bruno René Santos by telephone on
+351 210 438 686
Re: Oracle Sequences & Cache
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jun 16, 2011, at 7:49 PM, Chris Poulsen wrote:
> <db-key-generator>
> <db-generator-type>ORACLE</db-generator-type>
> <db-generator-name>PK_CONTACT_PERSON_KEY_SEQ</db-generator-name>
> <db-key-cache-size>1</db-key-cache-size>
> </db-key-generator>
Yep, that's the trick to map a sequence with increment of 1. On the Modeler side it is configured here:
http://people.apache.org/~aadamchik/sequence.png
Andrus
Re: Oracle Sequences & Cache
Posted by Chris Poulsen <ma...@nesluop.dk>.
Hi Bruno,
We've had issues at work related to caching of sequence values on oracle if
I recall correctly. We're using cayenne 3.
As we're not doing bulk inserts through cayenne, we decided to simply
extract one key at the time (not cache). The following xml snippet shows how
we configure the db-key-generator to do this (IIRC setting the cache-size to
0 fails, even though would I find that the intuitive way to tell cayenne not
to cache the key):
<db-entity name="PORTABLE_KIT_CONTACT_PERSON"
schema="CPC_PK_MANAGEMENT">
<db-attribute name="NAME" type="VARCHAR" isMandatory="true"
length="256"/>
<db-attribute name="PERSON_KEY" type="INTEGER" length="16"/>
<db-attribute name="P_K_CONTACT_PERSON_KEY" type="INTEGER"
isPrimaryKey="true" isMandatory="true" length="22"/>
<db-attribute name="TELEPHONE_NUMBER" type="VARCHAR"
isMandatory="true" length="64"/>
<db-key-generator>
<db-generator-type>ORACLE</db-generator-type>
<db-generator-name>PK_CONTACT_PERSON_KEY_SEQ</db-generator-name>
<db-key-cache-size>1</db-key-cache-size>
</db-key-generator>
</db-entity>
Using an insert "returning" a trigger generated key instead of the
key-generator stuff would be a nice (returning is supported since Oracle
10.2 or something like that). But I don't know if it would be a trivial
change to cayenne.
Hope this helps.
--
Regards Chris
On Thu, Jun 16, 2011 at 5:36 PM, Bruno René Santos <br...@holos.pt>wrote:
> Hello all,
>
> We had a problem on one of our applications on Oracle because, we think, of
> the cayenne cache and we wanted to have some sort of confirmation if we are
> seeing this correctly...
>
> On Oracle, in order to get the next PK we need to execute the query:
>
> Select sequence_name.next_val from DUAL
>
> Sometimes the query logger did not print this command before an INSERT.
> After some testing we reached to the conclusion that this was a cache issue
> and so we overrode CommitChanges and CommitChangesToParent in order to clear
> the query cache after the commit phase, like this:
>
> @Override
> public void commitChanges() throws CayenneRuntimeException {
> super.commitChanges();
> getQueryCache().clear();
> }
>
> @Override
> public void commitChangesToParent() {
> super.commitChangesToParent();
> getQueryCache().clear();
> }
>
> We think this is a bug because on the PK generation phase on cayenne no
> data, always, should be ever retrieved from the cache, which would lead to
> repeated primary keys...
>
> Can this be true?
>
> Thanx
> Bruno
> --
> Bruno René Santos | brunorene@holos.pt <ma...@holos.pt> |
> Gestor de Projectos | Analista | Programador | Investigador
>
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>
>
> This email and any files transmitted with it are confidential and intended
> solely for the use of the individual or entity to whom they are addressed.
> If you are not the intended recipient or the person responsible for
> delivering the email to the intended recipient, be advised that you have
> received this email in error and that any use, dissemination, forwarding,
> printing, or copying of this email is strictly prohibited. If you have
> received this email in error please notify Bruno René Santos by telephone on
> +351 210 438 686
>
>