Discussions

EJB programming & troubleshooting: WAS4, JDBC: ORA-8177 error with session bean

  1. WAS4, JDBC: ORA-8177 error with session bean (7 messages)

    Hi!
    [My environment:
      - WinNT 4.0 service pack 6
      - Websphere single server 4.0 fixpack 3
      - Oracle 9.0.1]
    I have written a stateless session bean that performs some JDBC actions on a database. One method perfoms an insert to a master table, inserts some detail records and then performs and update to the newly inserted record in the master table (all within that one method). When I call the method remotely from a client I sometimes get a database error: ORA-8177. The message says that the transaction could not be serialized. But I am the only client during my tests and I call only the described method. In my opinion there IS only one single transaction during the test (can I check somehow the creation of a J2EE transaction?).
    I have tried different transaction isolation levels: "repeatable read" -> crash; "read commited" -> ok; "read uncommitted" -> ok; "serializable" -> crash.
    If I set the transaction attribute to "never" or "not supported" the method always performs well without a crash.

    My question: when does the J2EE container start new transactions? It seems to me as if there are multiple parallel transactions running and causing my problem. Could it be possible that the connection pool causes any trouble?

    I hope that someone out there can help me.


    Marc
  2. WAS4, JDBC: ORA-8177 error with session bean[ Go to top ]

    Marc,

    In a Stateless SessionBean called directly from a client, the container will demarcate the transaction on every method call (assuming the bean's transactions are set to REQUIRED). So if you call m1(), then m2(), there will be a tx started for the call to m1() which will commit, then a second transaction during the call to m2().

    So let me ask a few questions.

    1) What tx property is set for the bean?
    2) How many methods, and does the SB call another SB?
    3) Does the client start a client transaction using the UserTransaction object?

    Dave Wolf
    Personified Technologies LLC
  3. WAS4, JDBC: ORA-8177 error with session bean[ Go to top ]

    Hi Dave,

    thanks for your explanations. Here the answers to your questions:

    1) tx property is "required"
    2) just one method, the SB does *not* call another SB
    3) no, the client does not start a client transaction

    The database actions I described for the SB method are running in a loop. The insert/update happens about 1200 times. The SB manages to run the loop a few times without error, then it "crashes" with the ORA-8177 error.


    Marc
  4. There are known issues with Oracle's implementation of TX_SERIALIZABLE. Why do you need this isolation level? Typically TX_READ_COMMITTED would suffice.

    Tim...
  5. Hi Tim!

    Thanks for your information. I have switched to isolation level "read commited".
    Can you tell me where I can find details about the "known issues with Oracle's implementation of TX_SERIALIZABLE"? A link or a document would be very nice.

    Marc
  6. Tim,
    a detailed description of Oracle locking can be found here:
    http://technet.oracle.com/doc/server.815/a67781/c23cnsis.htm

    I can't explain why your error is occuring but the standard way of dealing with an 8177 exception is to catch it and retry. Reducing the isolation level to READ_COMMITTED can hide the symptoms but may result in lost updates occuring.

    You're not trying to use a table row to keep the next key are you? This common pattern can lead to the problem you are describing.

    Cheers,
    Stephen
  7. When using tjdo in combination with jboss, it runs as a service, i.e. the PersistanceManagerFactory is created by jboss and can be found using jndi. This is configured by a file tjdo-ds.xml in the deploy-directory. Obviously username, password and the usual stuff required for any jdbc-connection are configured in this file in addition to the jndi-name of the PMF. But I did not find out how to set the transaction-isolation-level.

    Best regards,

    Karl
  8. this can be achieved by using
    <config-property name="TransactionIsolation" type="int">2</config-property>