RE: Optimistic lock failure

From: David Norwood (david..irtuant.com)
Date: Thu Dec 07 2006 - 10:23:58 EST

  • Next message: Mike Kienenberger: "Re: Optimistic lock failure"

    Thank you, sir Mike. I believe that answers my question.

    -----Original Message-----
    From: Mike Kienenberger [mailto:mkienen..mail.com]
    Sent: Thursday, December 07, 2006 4:19 PM
    To: cayenne-use..ncubator.apache.org; davidn@virtuant.com
    Subject: Re: Optimistic lock failure

    Certain combinations of databases and field types don't work for
    optimistic locking if the application and the database don't store the
    data using the equivalent representations.

    I'm guessing the problem is this:

    DATA1=2.229999999999999982236431605997495353221893310546875

    For optimistic locking to work, the value specified with SET (in
    UPDATE) and the value specified later in a qualifier must be equal in
    the eyes of the database.

    Ie, if you specify

    UPDATE
    SET DATA1=2.229999999999999982236431605997495353221893310546875

    And then in the next statement use

    ....WHERE DATA1=2.229999999999999982236431605997495353221893310546875

    and your database has truncated or rounded the value of DATA1 in the
    first update, then the equality statement will fail. For example, the
    database might have DATA1 = 2.223 at this point.

    I've had similar problems with certain versions of Oracle, Oracle
    drivers and Timestamp fields.

    On 12/7/06, David Norwood <david..irtuant.com> wrote:
    > Hi,
    >
    >
    >
    > I the following transaction I insert a new record into a table, then
    > directly run rules on the data inserted and call an update to set specific
    > columns after running the rules. See the following error; isn't it OK for
    > the same user to update the table after inserting a row? Why am I getting
    > this error??
    >
    >
    >
    > TIA,
    >
    >
    >
    > -david
    >
    > ---------------- error output -----------------------
    >
    > 2006-12-06 09:50:52,406 DEBUG [org.jboss.mx.loading.RepositoryClassLoader]
    > setRepository,
    > repository=org.jboss.mx.loading.HeirarchicalLoaderRepository..c1f2,
    > cl=org.jboss.mx.loading.UnifiedClassLoader..ec21a{ url=null
    ,addedOrder=0}
    >
    > 2006-12-06 09:50:52,421 INFO [org.objectstyle.cayenne.access.QueryLogger]
    > --- will run 1 query.
    >
    > 2006-12-06 09:50:52,421 INFO [org.objectstyle.cayenne.access.QueryLogger]
    > UPDATE dbo.dochistory SET AVGREADING = ? WHERE ID = ? AND AUDITOR IS NULL
    > AND AVGREADING IS NULL AND DATA1 = ? AND DATA10 IS NULL AND DATA2 IS NULL
    > AND DATA3 IS NULL AND DATA4 IS NULL AND DATA5 IS NULL AND DATA6 IS NULL
    AND
    > DATA7 IS NULL AND DATA8 IS NULL AND DATA9 IS NULL AND DATETIME = ? AND
    INFO1
    > IS NULL AND INFO10 IS NULL AND INFO2 IS NULL AND INFO3 IS NULL AND INFO4
    IS
    > NULL AND INFO5 IS NULL AND INFO6 IS NULL AND INFO7 IS NULL AND INFO8 IS
    NULL
    > AND INFO9 IS NULL AND ISDELETED = ? AND ISFAILURE = ? AND ISRECHECK = ?
    AND
    > PERSONNAME = ? AND PRESHIPPER IS NULL AND PRODUCTCODE IS NULL AND VERIFIER
    > IS NULL AND LOCATIONID = ? AND ORG_ID = ? AND PACKAGEID = ? AND USR_ID = ?
    >
    > 2006-12-06 09:50:52,421 INFO [org.objectstyle.cayenne.access.QueryLogger]
    > [bind: 2.229999999999999982236431605997495353221893310546875, 4557, NULL,
    > NULL, 2.229999999999999982236431605997495353221893310546875, NULL, NULL,
    > NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2006-12-06 09:50:52.078', NULL,
    > NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'false', 'false',
    > 'false', 'Tom Johnson', NULL, NULL, NULL, 196, 34, 1179, 9]
    >
    > 2006-12-06 09:50:52,468 INFO [org.objectstyle.cayenne.access.QueryLogger]
    > *** error.
    >
    > org.objectstyle.cayenne.access.OptimisticLockException: [v.1.2.1 August 30
    > 2006] Optimistic Lock Failure, SQL: [UPDATE dbo.dochistory SET AVGREADING
    =
    > ? WHERE ID = ? AND AUDITOR IS NULL AND AVGREADING IS NULL AND DATA1 = ?
    AND
    > DATA10 IS NULL AND DATA2 IS NULL AND DATA3 IS NULL AND DATA4 IS NULL AND
    > DATA5 IS NULL AND DATA6 IS NULL AND DATA7 IS NULL AND DATA8 IS NULL AND
    > DATA9 IS NULL AND DATETIME = ? AND INFO1 IS NULL AND INFO10 IS NULL AND
    > INFO2 IS NULL AND INFO3 IS NULL AND INFO4 IS NULL AND INFO5 IS NULL AND
    > INFO6 IS NULL AND INFO7 IS NULL AND INFO8 IS NULL AND INFO9 IS NULL AND
    > ISDELETED = ? AND ISFAILURE = ? AND ISRECHECK = ? AND PERSONNAME = ? AND
    > PRESHIPPER IS NULL AND PRODUCTCODE IS NULL AND VERIFIER IS NULL AND
    > LOCATIONID = ? AND ORG_ID = ? AND PACKAGEID = ? AND USR_ID = ?], WHERE
    > clause bindings: [USR_ID=9, DATA6=NULL, ISRECHECK='false',
    > ISDELETED='false', INFO1=NULL, LOCATIONID=196, DATA5=NULL,
    AVGREADING=NULL,
    > AUDITOR=NULL, INFO8=NULL, DATA2=NULL, INFO2=NULL, DATETIME='2006-12-06
    > 09:50:52.078', VERIFIER=NULL, DATA9=NULL, INFO4=NULL, PACKAGEID=1179,
    > ID=4557, INFO9=NULL, INFO3=NULL, DATA8=NULL, INFO6=NULL, DATA7=NULL,
    > INFO7=NULL, PERSONNAME='Tom Johnson', PRESHIPPER=NULL, DATA4=NULL,
    > DATA10=NULL, ORG_ID=34, DATA3=NULL, INFO10=NULL, INFO5=NULL,
    > PRODUCTCODE=NULL,
    > DATA1=2.229999999999999982236431605997495353221893310546875,
    > ISFAILURE='false']
    >
    > at
    >
    org.objectstyle.cayenne.access.jdbc.BatchAction.runAsIndividualQueries(Batch
    > Action.java:235)
    >
    > at
    >
    org.objectstyle.cayenne.access.jdbc.BatchAction.performAction(BatchAction.ja
    > va:117)
    >
    >
    >
    >
    >



    This archive was generated by hypermail 2.0.0 : Thu Dec 07 2006 - 10:24:44 EST