Ok. I've managed to find a reproducable case to work with.
My code creates a payment history record with the status set to unknown,
processes a payment, then updates the existing payment history record with
the results of the payment processing.
This update consistantly fails optimistic locking under Oracle.
This also is a case where no data is read from the database, and only two
write-to-database operations occur.
The data being used, if the bind output can be trusted, is identical (which
makes sense if it's the same object).
So I'm confused how this set of statements can fail other the data passed to
the JDBC driver is not the same data written into the database.
Unfortunately, I've been unable to figure out how to print out the
fractional seconds part of an Oracle DATE using SQL-PLUS, so I can't tell if
the date in the database is the same as the date sent by the application.
-Mike
INSERT INTO PAYMENT_HISTORY (ACCOUNT_NUMBER, PAYMENT_AMOUNT, PAYMENT_DATE,
PAYMENT_ID, PAYMENT_METHOD_ID, REMOTE_AUTHORIZATION_CODE, REMOTE_AVS_CODE,
REMOTE_RESPONSE_CODE, REMOTE_RESPONSE_REASON_CODE, REMOTE_RESPONSE_SUBCODE,
REMOTE_TRANSACTION_ID, SCHEDULED_PAYMENT_ID, STATUS, USER_ID) VALUES (?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[batch bind: 9, 8, '2004-02-12 21:13:05.062', 1991, 1, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, '?', 56]
=== updated 1 row.
UPDATE PAYMENT_HISTORY SET REMOTE_AUTHORIZATION_CODE = ?,
REMOTE_RESPONSE_SUBCODE = ?, STATUS = ?, REMOTE_RESPONSE_REASON_CODE = ?,
REMOTE_AVS_CODE = ?, REMOTE_RESPONSE_CODE = ?, REMOTE_TRANSACTION_ID = ?
WHERE PAYMENT_AMOUNT = ? AND PAYMENT_DATE = ? AND PAYMENT_ID = ? AND
REMOTE_AUTHORIZATION_CODE IS NULL AND REMOTE_AVS_CODE IS NULL AND
REMOTE_RESPONSE_CODE IS NULL AND REMOTE_RESPONSE_REASON_CODE IS NULL AND
REMOTE_RESPONSE_SUBCODE IS NULL AND REMOTE_TRANSACTION_ID IS NULL AND STATUS
= ?
[bind: '000000', '1', 'S', '1', 'P', '1', 0, 8, '2004-02-12 21:13:05.062',
1991, '?']
binding set parameter=1 to name=REMOTE_AUTHORIZATION_CODE value='000000'
type=12
binding set parameter=2 to name=REMOTE_RESPONSE_SUBCODE value='1' type=12
binding set parameter=3 to name=STATUS value='S' type=12
binding set parameter=4 to name=REMOTE_RESPONSE_REASON_CODE value='1'
type=12
binding set parameter=5 to name=REMOTE_AVS_CODE value='P' type=12
binding set parameter=6 to name=REMOTE_RESPONSE_CODE value='1' type=12
binding set parameter=7 to name=REMOTE_TRANSACTION_ID value=0 type=4
binding id parameter=8 to name=PAYMENT_AMOUNT value=8 type=3
binding id parameter=9 to name=PAYMENT_DATE value='2004-02-12 21:13:05.062'
type=93
binding id parameter=10 to name=PAYMENT_ID value=1991 type=4
binding id parameter=11 to name=STATUS value='?' type=12
*** error.
org.objectstyle.cayenne.CayenneException: [v.1.1-dev February 12 2004]
Optimistic lock failure on sql 'UPDATE PAYMENT_HISTORY SET
REMOTE_AUTHORIZATION_CODE = ?, REMOTE_RESPONSE_SUBCODE = ?, STATUS = ?,
REMOTE_RESPONSE_REASON_CODE = ?, REMOTE_AVS_CODE = ?, REMOTE_RESPONSE_CODE =
?, REMOTE_TRANSACTION_ID = ? WHERE PAYMENT_AMOUNT = ? AND PAYMENT_DATE = ?
AND PAYMENT_ID = ? AND REMOTE_AUTHORIZATION_CODE IS NULL AND REMOTE_AVS_CODE
IS NULL AND REMOTE_RESPONSE_CODE IS NULL AND REMOTE_RESPONSE_REASON_CODE IS
NULL AND REMOTE_RESPONSE_SUBCODE IS NULL AND REMOTE_TRANSACTION_ID IS NULL
AND STATUS = ?' for query bindings=['000000', '1', 'S', '1', 'P', '1', 0, 8,
'2004-02-12 21:13:05.062', 1991, '?']
This archive was generated by hypermail 2.0.0 : Thu Feb 12 2004 - 21:39:20 EST