You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by David Norwood <da...@virtuant.com> on 2006/12/07 15:45:15 UTC

Optimistic lock failure

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.HeirarchicalLoaderRepository3@1c1f2,
cl=org.jboss.mx.loading.UnifiedClassLoader3@6ec21a{ 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)

RE: Optimistic lock failure

Posted by David Norwood <da...@virtuant.com>.
OK; I'll check for that. Thanks again.

-----Original Message-----
From: Mike Kienenberger [mailto:mkienenb@gmail.com] 
Sent: Thursday, December 07, 2006 4:28 PM
To: cayenne-user@incubator.apache.org; davidn@virtuant.com
Subject: Re: Optimistic lock failure

One other thing to note.  If you can't fix the database/driver, what
you can do is to write your own converter for this data type.   I
don't remember the details, but it's probably a matter of extending
the current db adapter class, and implementing an extended type for it
that rounds/truncates appropriately.   I think you can take a look at
Oracle Date handling for one such example.

On 12/7/06, David Norwood <da...@virtuant.com> wrote:
> Thank you, sir Mike. I believe that answers my question.
>
> -----Original Message-----
> From: Mike Kienenberger [mailto:mkienenb@gmail.com]
> Sent: Thursday, December 07, 2006 4:19 PM
> To: cayenne-user@incubator.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 <da...@virtuant.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.HeirarchicalLoaderRepository3@1c1f2,
> > cl=org.jboss.mx.loading.UnifiedClassLoader3@6ec21a{ 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)
> >
> >
> >
> >
> >
>
>

Re: Optimistic lock failure

Posted by Mike Kienenberger <mk...@gmail.com>.
One other thing to note.  If you can't fix the database/driver, what
you can do is to write your own converter for this data type.   I
don't remember the details, but it's probably a matter of extending
the current db adapter class, and implementing an extended type for it
that rounds/truncates appropriately.   I think you can take a look at
Oracle Date handling for one such example.

On 12/7/06, David Norwood <da...@virtuant.com> wrote:
> Thank you, sir Mike. I believe that answers my question.
>
> -----Original Message-----
> From: Mike Kienenberger [mailto:mkienenb@gmail.com]
> Sent: Thursday, December 07, 2006 4:19 PM
> To: cayenne-user@incubator.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 <da...@virtuant.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.HeirarchicalLoaderRepository3@1c1f2,
> > cl=org.jboss.mx.loading.UnifiedClassLoader3@6ec21a{ 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)
> >
> >
> >
> >
> >
>
>

Re: Optimistic lock failure

Posted by Mike Kienenberger <mk...@gmail.com>.
On 12/7/06, Mike Kienenberger <mk...@gmail.com> wrote:
> 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.

Note also that this is really a jdbc driver or database problem.  The
driver or database should perform the same rounding for the update set
value and the qualifier value so that the two appear equal.   There's
no reasonable way for the application to know how the database
internally converts values.

RE: Optimistic lock failure

Posted by David Norwood <da...@virtuant.com>.
Thank you, sir Mike. I believe that answers my question.

-----Original Message-----
From: Mike Kienenberger [mailto:mkienenb@gmail.com] 
Sent: Thursday, December 07, 2006 4:19 PM
To: cayenne-user@incubator.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 <da...@virtuant.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.HeirarchicalLoaderRepository3@1c1f2,
> cl=org.jboss.mx.loading.UnifiedClassLoader3@6ec21a{ 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)
>
>
>
>
>

Re: Optimistic lock failure

Posted by Mike Kienenberger <mk...@gmail.com>.
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 <da...@virtuant.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.HeirarchicalLoaderRepository3@1c1f2,
> cl=org.jboss.mx.loading.UnifiedClassLoader3@6ec21a{ 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)
>
>
>
>
>