Unable to replicate java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

Question

Environment: WildFly10.1, EJB3.1, JPA,Oracle

This question look similar to questions which were asked before, but I was not able to get answers.

I have a multi threading batch processing application that process payments: An EJB method (triggered by Timer Bean) retrieves unprocessed payments from the DB, splits the payments into chunks, then send those chunks to another EJB method(Async) for processing. Sample code provided below:

@Stateless
@LocalBean
public class PaymentProcessor {

@EJB
private PaymentFacade paymentFacade;
@EJB
private PaymentExecuter paymentExecuter;

public void processNegativeTrackerBatch() {

    List<Payment> paymentList = paymentFacade.findPendingPaymentEntries();

    // Each PaymentBlock represent a thread
    List<PaymentBlock> paymentBlockList = splitPaymentsIntoBlocks(paymentList, NUMBER_OF_THREADS);

    for (PaymentBlock block : paymentBlockList) {

        paymentExecuter.processPayment(block.paymentList());
        }
    }
}


@Stateless
@LocalBean
public class PaymentExecuter {

    @EJB
    private PaymentFacade paymentFacade;

    @Asynchronous
    public Future<PaymentResults> processPayment(List<Payment> paymentList) {
        PaymentResults paymentResults = new PaymentResults("SUCCESS");

        for (Payment payment : paymentList) {

            try {

                //update acount balances (code removed)
                //The following code cause oracle to threw ORA-00060: deadlock detected while waiting for resource
                payment.setLoaded((short) 1);
                payment.setDateLoaded(new Date());
                paymentFacade.edit(payment);

            } catch (Exception ex) {
                paymentResults.setResponseCode("PARTIAL FAIL");
                //log exception
            }

        }
        return new AsyncResult<PaymentResults>(paymentResults);

    }
}

The above code throws the following exception in production environment when JTA is set to true in wildfly :

javax.ejb.EJBTransactionRolledbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.v20160218-180e602): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource

Error Code: 60
Call: UPDATE PAYMENT SET DATE_LOADED = ?, LOADED = ? WHERE (ID = ?)
        bind => [2018-07-05 01:07:22.225, 1, 650133]
Query: UpdateObjectQuery(za.co.company.persistence.entities.Payment[ id=650133 ])
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleInCallerTx(CMTTxInterceptor.java:159)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:256)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:329)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:239)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:47)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ejb3.deployment.processors.StartupAwaitInterceptor.processInvocation(StartupAwaitInterceptor.java:22)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:67)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:54)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:340)
        at org.jboss.invocation.ContextClassLoaderInterce

... However I was not successful in replicating the above events in pre-production environment even with the exact same data.

My questions:

1. If the payments are unique and I am certain that non of the payment records ends up in multiple threads, why did the dead lock occur?

2. How can I replicate the dead lock

3. How can I uncover what really happened so that I can fix it?

Please assist. Any help or referral to material is appreciated.

Here is some information about the oracle trace:

2018-07-05 01:02:39.569*:ksq.c@12954:ksqdld_hdr_dump(): DEADLOCK DETECTED ( ORA-00060 ) See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock:

Deadlock graph: ------------Blocker(s)----------- ------------Waiter(s)------------ Resource Name process session holds waits serial process session holds waits serial TX-00290020-0016FF24-00000000-00000000 161 463 X 908 173 467 S 29672 TX-0027000C-001C7991-00000000-00000000 173 467 X 29672 161 463 S 908

*** 2018-07-05T01:02:39.575960+02:00 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=1, mask=0x0) ----- Error Stack Dump ----- ----- Current SQL Statement for this session (sql_id=6aju12kbrg657) ----- UPDATE PAYMENT SET LOADED = :1 , DATE_LOADED = :2 WHERE (ID = :3 )

Oracle SQL Bind capture details

Answer 1

This error is not related to the Java code that you posted here.

Verify in your Payment and PaymentFacade metods which database tables or other resources are they updating. There could be more than one table/resource participating in the same update request (methods calls).

This kind of conflict typically hapens when two database sessions (connection from thread, process or another computer) modify two or more resources in different order. For example:

• session 1 updates row 1 - update and lock row 1 for consistency until the transaction completes
• session 2 updates row 2 - update and lock row 2 for consistency until the transaction completes
• session 1 updates row 2 - wait until row 2 is unlocked then update
• session 2 updates row 1 - wait until row 1 is unlocked then update

Both sessions are now blocking each other while waiting for resources/rows. This could never end. But the database detects this situation and after fiew seconds (depending on the database setup) an deadlock excepion is thrown. Only one session gets this exception. The other continues the transaction and performs the update on the unlocked record.

You can work around this by rolling back and then redoing the whole transaction. For example repeating sql statements or by restarting the thread. But first check that if the conflict hapens betwenn your threads. In this situation you can fix it. If the update affects more than one table, split the yob between your threads keeping in mind also that tables. Perform the update always in the same order. For example sort the updates to execute in order by primary key of the table. If there is a cause of conflict with other applications then you should implement an transaction redo or thread restart.

all the best in finding a solution

Answer 2

A "lock conflict" is where two processes attempt to update the same row at the same time.

However, a "deadlock" is a different animal. Deadlocks occur when two processes attempt to update the same resources, but in a different order. A common situation is that process A attempts to update table Y, then table Z. Simultaneously, process B attempts to update table Z, then table Y. Process A has table Y locked, and needs to lock Z - but process B has table Z locked and needs to lock Y. Neither can proceed because the other process has the resource that they need locked, so they'll wait until doomsday - or until the database decides to fail one of their transactions, thus releasing the locks they hold and allowing the other blocked process to proceed.

Thus, apparently there's a case in your codebase where this can happen - tables being updated in a different order. Not sure why the JTA setting might affect things, but that's what's going on.

Best of luck.