Discussions

EJB programming & troubleshooting: JDBC call in an ongoing transaction : multiple threads testing

Bookmark and Share Get thread feed

  1. JDBC call in an ongoing transaction : multiple threads testing (2 messages)

    I am going to explain you the problem we are encountering!
    There is a transaction T1 which does the following:
    ***********************
    -Stateless Session Bean starts a transaction (Transaction Required-
    Transaction Repeatable Read)
    -T1 transaction starts (CMP bean)
    -does a search on the PersonTable to see that requested record doesn't
    exist (through a JDBC call : a select statement on PersonName Table)
    -if not found do the following{
        -adds a record into Person Table (Entity Bean)(Transaction Required-
    Transaction Repeatable Read)
        -adds a record into PersonName Table (Entiry Bean)(Transaction
    Required- Transaction Repeatable Read)
    }
    -Session Bean commits and transaction ends (
    *****************

    If there is one thread doing this it works fine. If subsequent threads
    are spawned doing the same transaction then only one gets comitted and
    the rest get a Deadlock exception on SQL
    The error is :
    [IBM][CLI Driver][DB2/6000] SQL0911N The current transaction has been
    rolled back because of a deadlock or timeout. Reason code "2".
    SQLSTATE=40001
    Reply to this

    Threaded Messages (2)

  2. JDBC call in an ongoing transaction : multiple threads testing[ Go to top ]

    I don't know DB2 well enough to be dogmatic, but I'd say that all transactions are taking a shared lock on the table, which they then want to try to upgrade to a write lock, no one can, so you get a deadlock.

    If this is the problem, you could try a 'select for update'.

    I notice that the error message says 'deadlock *or* timeout' -- if DB2 has a 'nowait' lock mode perhaps this is your problem.

    There is nothing unique to J2EE about your problem -- show the queries and the error messages to a good DB2 DBA. The solution depends on understanding the locking and isolation behaviour of DB2.

    Tom
    Reply to this Reply to original

  3. JDBC call in an ongoing transaction : multiple threads testing[ Go to top ]

    Chapter 22 of the DB2 Administration Guide contains more detail than you will ever want to know about Locking. I can't see anything in your sample that would cause deadlock other than concurrent threads attempting to insert the exact same PK values.

    Refer to the second last posting in the thread http://theserverside.com/discussion/thread.jsp?thread_id=7437 for details on how to obtains a lock trace.

    Stephen
    Reply to this Reply to original