My application is receiving JMSTextMessages from IBM Mq in bulk. For each message I am calling a REST API to process these messages. Somewhere when the API is processing these messages I am getting following exception logs, intermittently but very frequently:
java.lang.Exception: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement
2018-04-03 17:54:10.614 ERROR 5259 --- [.0-8083-exec-59] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00060: deadlock detected while waiting for resource
It neither prints the table nor the query which it is trying to execute. To resolve this issue I tried pushing all Database processing code, in my REST API service code, inside a synchronized block, but still the same issue.Due to this issue every 2nd message fails to get processed.
Basis on a lot of material available online, it appears that each message received, triggers a new thread (at REST API service method end) and thus causing some deadlock kind of behavior in my app. Sadly, I am unable to figure out which piece of code exactly is causing this and we have a single thread when it comes to service application startup.
So now, I have decided to find out if I can introduce a delay before I start processing another message at the listener end. Again read a lot and everywhere I see XML property changes to handle this, but nothing comes handy if I want to do it via Spring Annotations. My app does not have any XML configs.
Here is my pseudo code:
@JmsListener(destination="queue_name")
public void receiveMessage(Object message){
myRestService.processMessage(message);
}
ServiceImpl code
@Transactional(propagation = Propagation.REQUIRES_NEW)
public void processMessage(Object message){
Long id = getIdFromMessage(message);
X x = readFromDB(id);
updateDataInX(x, message);
savex(x);
}
I would also like to highlight that I have a parent-child relationship in the database. Where PRIMARY_KEY of parent table is PRIMARY_KEY (as well as FK) of child table and it is indexed as well. While I am updating values in above method (updateDataInX), some columns are being updated in child table row and some columns are being updated in parent table row (mapped to child row). The instance x is of CHILD class.
Apart from introducing a delay, how can I fix this? The producer is a separate application, and within a second they are sending multiple messages - which the consumer is clearly failing to process. This entirely is back-end update so I dont mind introducing a delay to have a fail-safe mechanism but I did not find any proper solution with spring annotations. Also if you can tell what exactly happens with threads at receiver side and rest API side. I am thoroughly confused reading about it since last 3 weeks.
My code also involved adding AUDIT logging data in one of the other CHILD tables, which was referring to a NON-PRIMARY key of the same PARENT table (Hierarchy of X).
While inserting a new row in CHILD table oracle was taking a full table lock which was resulting all subsequent inserts to fail with a deadlock issue.
Reason for taking a full table lock on CHILD table was that it had a foreign key column REFERENCE_NO which refers to the PARENT table.
When SQL statements from multiple sessions involve parent/child tables, a deadlock may occur with oracle default locking mechanism if the foreign key column in the child table is not indexed. Also Oracle takes a full table lock on the child table when primary key value is changed in the parent table.
In our case the PARENT.REFERENCE_NO is a non-primary key which is referred as foreign key in CHILD table. Every time we do a status update on PARENT table as mentioned in above pseudo code, hibernate fires update on REFERENCE_NO which results in locking the entire CHILD table, making it unavailable for AUDIT logging insertions.
So they Key Is : Oracle recommends to INDEX such foreign key columns due to which oracle does not take full table lock.