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 2010/11/23 18:19:46 UTC
Updates with Delta values
Hello all,
I doing an update on a hierarchical structures with total values. In order not
to do locks on the fields I wanted to update these values like this:
UPDATE INFO SET TOTAL = TOTAL + 500
Where 500 is the value the total will be increased with. This way I can have
several people updating totals concurrently without problems. My question is how
can I put this kind of structure on a object that is called during the
commitChanges phase? Is it even possible?
Thanx & Regards
Bruno Santos
--
Bruno René Santos | Gestor de Projectos - Project Manager |
<ma...@holos.pt> brunorene@holos.pt | <http://www.holos.pt>
http://www.holos.pt
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
Phone: +351 210 438 686 . Fax: +351 210 438 687
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: Updates with Delta values
Posted by Mike Kienenberger <mk...@gmail.com>.
If your database is multithreaded, I don't know that you can assume
that adding 300 will add it to the 600. It might add it to the 100
instead if the timing is just right. Can you guarantee that "UPDATE
INFO SET TOTAL = TOTAL + 500" is an atomic operation? I strongly
suspect this will be database-dependent.
But one answer to your usage question would be to use an SQLTemplate
to create the specific SQL you need to make this happen.
http://cayenne.apache.org/doc30/sqltemplate-query.html
On Tue, Nov 23, 2010 at 8:37 PM, Bruno René Santos <br...@holos.pt> wrote:
> My objective was not to read anything. I am not trying to get a specific value
> after the addition, but just add a new value to the current value of the field.
> So If I have 100 person 1 adds 500 and we get 600, person 2 adds 300 and we get
> 900. Any order you make it you always get 900 in the end. Making everything
> consistent without locks. The trick here is not to read and just add what you
> need to the current value. This is possible because you can add/subtract values
> to the field, without actually reading it before the update. This is for a
> financial application where you have accounts with balances and movements.
>
> Either way I wanted to know if there is some mechanism in cayenne to perform
> this operation during commitChanges.
>
> Thanx
> Bruno
>
> -----Mensagem original-----
> De: Mike Kienenberger [mailto:mkienenb@gmail.com]
> Enviada: terça-feira, 23 de Novembro de 2010 17:43
> Para: user@cayenne.apache.org
> Assunto: Re: Updates with Delta values
>
> Is that actually safe? At best, it would be database-dependent.
>
> What happens if two people start the statement at the same time?
> TOTAL starts out at $100, I add 500 to get 600. Someone else adds 50
> to get 150. The value either becomes 600 or 50 rather than 650.
>
> I think the safe way to do this is to use optimistic locking instead.
>
> UPDATE INFO SET TOTAL = ? WHERE TOTAL = ?
> bind [NEW_TOTAL, OLD_TOTAL]
>
> If it fails, update NEW_TOTAL and OLD_TOTAL and retry.
>
> I suspect you will find that the reality is that you will rarely hit
> this problem.
>
> On Tue, Nov 23, 2010 at 12:19 PM, Bruno René Santos <br...@holos.pt> wrote:
>> Hello all,
>>
>>
>>
>> I doing an update on a hierarchical structures with total values. In order not
>> to do locks on the fields I wanted to update these values like this:
>>
>>
>>
>> UPDATE INFO SET TOTAL = TOTAL + 500
>>
>>
>>
>> Where 500 is the value the total will be increased with. This way I can have
>> several people updating totals concurrently without problems. My question is
> how
>> can I put this kind of structure on a object that is called during the
>> commitChanges phase? Is it even possible?
>>
>>
>>
>> Thanx & Regards
>>
>> Bruno Santos
>>
>>
>>
>> --
>>
>>
>>
>> Bruno René Santos | Gestor de Projectos - Project Manager |
>> <ma...@holos.pt> brunorene@holos.pt | <http://www.holos.pt>
>> http://www.holos.pt
>>
>>
>>
>> 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
>>
>> Phone: +351 210 438 686 . Fax: +351 210 438 687
>>
>>
>>
>> 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: Updates with Delta values
Posted by Bruno René Santos <br...@holos.pt>.
My objective was not to read anything. I am not trying to get a specific value
after the addition, but just add a new value to the current value of the field.
So If I have 100 person 1 adds 500 and we get 600, person 2 adds 300 and we get
900. Any order you make it you always get 900 in the end. Making everything
consistent without locks. The trick here is not to read and just add what you
need to the current value. This is possible because you can add/subtract values
to the field, without actually reading it before the update. This is for a
financial application where you have accounts with balances and movements.
Either way I wanted to know if there is some mechanism in cayenne to perform
this operation during commitChanges.
Thanx
Bruno
-----Mensagem original-----
De: Mike Kienenberger [mailto:mkienenb@gmail.com]
Enviada: terça-feira, 23 de Novembro de 2010 17:43
Para: user@cayenne.apache.org
Assunto: Re: Updates with Delta values
Is that actually safe? At best, it would be database-dependent.
What happens if two people start the statement at the same time?
TOTAL starts out at $100, I add 500 to get 600. Someone else adds 50
to get 150. The value either becomes 600 or 50 rather than 650.
I think the safe way to do this is to use optimistic locking instead.
UPDATE INFO SET TOTAL = ? WHERE TOTAL = ?
bind [NEW_TOTAL, OLD_TOTAL]
If it fails, update NEW_TOTAL and OLD_TOTAL and retry.
I suspect you will find that the reality is that you will rarely hit
this problem.
On Tue, Nov 23, 2010 at 12:19 PM, Bruno René Santos <br...@holos.pt> wrote:
> Hello all,
>
>
>
> I doing an update on a hierarchical structures with total values. In order not
> to do locks on the fields I wanted to update these values like this:
>
>
>
> UPDATE INFO SET TOTAL = TOTAL + 500
>
>
>
> Where 500 is the value the total will be increased with. This way I can have
> several people updating totals concurrently without problems. My question is
how
> can I put this kind of structure on a object that is called during the
> commitChanges phase? Is it even possible?
>
>
>
> Thanx & Regards
>
> Bruno Santos
>
>
>
> --
>
>
>
> Bruno René Santos | Gestor de Projectos - Project Manager |
> <ma...@holos.pt> brunorene@holos.pt | <http://www.holos.pt>
> http://www.holos.pt
>
>
>
> 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
>
> Phone: +351 210 438 686 . Fax: +351 210 438 687
>
>
>
> 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: Updates with Delta values
Posted by Mike Kienenberger <mk...@gmail.com>.
Is that actually safe? At best, it would be database-dependent.
What happens if two people start the statement at the same time?
TOTAL starts out at $100, I add 500 to get 600. Someone else adds 50
to get 150. The value either becomes 600 or 50 rather than 650.
I think the safe way to do this is to use optimistic locking instead.
UPDATE INFO SET TOTAL = ? WHERE TOTAL = ?
bind [NEW_TOTAL, OLD_TOTAL]
If it fails, update NEW_TOTAL and OLD_TOTAL and retry.
I suspect you will find that the reality is that you will rarely hit
this problem.
On Tue, Nov 23, 2010 at 12:19 PM, Bruno René Santos <br...@holos.pt> wrote:
> Hello all,
>
>
>
> I doing an update on a hierarchical structures with total values. In order not
> to do locks on the fields I wanted to update these values like this:
>
>
>
> UPDATE INFO SET TOTAL = TOTAL + 500
>
>
>
> Where 500 is the value the total will be increased with. This way I can have
> several people updating totals concurrently without problems. My question is how
> can I put this kind of structure on a object that is called during the
> commitChanges phase? Is it even possible?
>
>
>
> Thanx & Regards
>
> Bruno Santos
>
>
>
> --
>
>
>
> Bruno René Santos | Gestor de Projectos - Project Manager |
> <ma...@holos.pt> brunorene@holos.pt | <http://www.holos.pt>
> http://www.holos.pt
>
>
>
> 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
>
> Phone: +351 210 438 686 . Fax: +351 210 438 687
>
>
>
> 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
>
>
>
>