Re: Updates with Delta values

From: Mike Kienenberger (mkienen..mail.com)
Date: Tue Nov 23 2010 - 17:42:40 UTC

  • Next message: bogdan_voloshincu: "cdataport Error porting data: null"

    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 <brunoren..olos.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:brunoren..olos.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
    >
    >
    >
    >

    This archive was generated by hypermail 2.0.0 : Tue Nov 23 2010 - 17:43:30 UTC