We are using jboss 3.0.2 and oracle 8.1.7. We have one CMP entity bean deployed.
Jboss throws an 'ORA-01002: fetch out of sequence' exception when retrieving the bean from the database. After investigation we found out the problem:
Our bean uses pessimistic locking via 'row locking' and the findByXXX methods transaction attribute is 'Supports'. When jboss tries to synchronize the bean state with oracle, it uses a 'select for update' statement, which is executed outside a container-managed transaction. Due to the JCA auto commit contract, the auto commit status of the connection is set to true.
This generates the above exception since 'for update' statement must be executed with auto commit set to false in Oracle. BTW, anybody knows if it is true for other database?
The correction is obvious, remove the 'row locking' or set transaction attribute to 'required' but this is a trade off.
This sounds strange to me, and perhaps somebody can explain this. Using row locking or 'Supports' attribute are legal settings, this should not generate any exception. Row locking is used as pessimistic locking to prevent external processes to update the row and there is no need for transaction when running a 'select' statement.
Is it to jboss to solve this issue? Or row locking should not be used with ejb?
Manu
