cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Kienenberger <mkien...@gmail.com>
Subject Re: Updates with Delta values
Date Wed, 24 Nov 2010 02:06:08 GMT
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 <brunorene@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 <brunorene@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 |
>> <mailto:brunorene@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
>>
>>
>>
>>
>
>

Mime
View raw message