MySQL Bugs: #43619: com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when

Bug #43619	com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when
Submitted:	13 Mar 2009 6:14	Modified:	13 Sep 2012 13:46
Reporter:	rajesh goswami	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	server version: 4.1.20-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when

Description:
I have create one insert statement for preparing daily MIS report and It is executing very smoothly on mysql prompt. But when I am going SET it in scheduling by JAVA program with JDBC connecter. Its taking a lots time for executing one insert statement. actully I have SET the java program in crontab and its executing every night. but when I checked the log in the morning Its shown Error like 

"com.mysql.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when"

Java program code:
====================

PreparedStatement statement = null;
statement = connection.prepareStatement(query);
statement.executeUpdate();

SQL statement :
=========================================

INSERT INTO HUTCH_MIS_REPORT(REQUEST_DATE,SERVICE_TYPE,HITS,UNIQUE_NO,CIRCLE)
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) DATE,'RAILWAY',COUNT(MOBILE_NUMBER) HITS, 
COUNT(DISTINCT(MOBILE_NUMBER)) UNIQUE_NO,'GJ'
FROM REQUEST_LOG          
WHERE SERVICE_TYPE LIKE 'RAI%'
AND request_id BETWEEN (select min(request_id) 
FROM REQUEST_LOG WHERE REQUEST_DATE>DATE_SUB(CURDATE(),INTERVAL 3 DAY))
AND  (SELECT MIN(REQUEST_ID) FROM REQUEST_LOG WHERE REQUEST_DATE>CURDATE()-2)
AND SUBSTR(MOBILE_NUMBER,1,6) IN (SELECT SERIES FROM idea.HUTCH_SERIES_MST WHERE CIRCLE='GJ')
UNION 
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) DATE,'TVSCHEDULE',COUNT(MOBILE_NUMBER) HITS, COUNT(DISTINCT(MOBILE_NUMBER)) UNIQUE_NO,'GJ'
FROM REQUEST_LOG  
WHERE SERVICE_TYPE LIKE 'TV%'
AND request_id BETWEEN (select min(request_id) from REQUEST_LOG 
WHERE REQUEST_DATE>DATE_SUB(CURDATE(), INTERVAL 3 DAY))
AND  (SELECT MIN(REQUEST_ID) FROM REQUEST_LOG WHERE REQUEST_DATE>CURDATE()-2)
AND SUBSTR(MOBILE_NUMBER,1,6) IN (SELECT SERIES FROM idea.HUTCH_SERIES_MST WHERE CIRCLE='GJ');
 

How to repeat:
Java program code:
====================

PreparedStatement statement = null;
statement = connection.prepareStatement(query);
statement.executeUpdate();

SQL statement :
=========================================

INSERT INTO HUTCH_MIS_REPORT(REQUEST_DATE,SERVICE_TYPE,HITS,UNIQUE_NO,CIRCLE)
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) DATE,'RAILWAY',COUNT(MOBILE_NUMBER) HITS, 
COUNT(DISTINCT(MOBILE_NUMBER)) UNIQUE_NO,'GJ'
FROM REQUEST_LOG          
WHERE SERVICE_TYPE LIKE 'RAI%'
AND request_id BETWEEN (select min(request_id) 
FROM REQUEST_LOG WHERE REQUEST_DATE>DATE_SUB(CURDATE(),INTERVAL 3 DAY))
AND  (SELECT MIN(REQUEST_ID) FROM REQUEST_LOG WHERE REQUEST_DATE>CURDATE()-2)
AND SUBSTR(MOBILE_NUMBER,1,6) IN (SELECT SERIES FROM idea.HUTCH_SERIES_MST WHERE CIRCLE='GJ')
UNION 
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) DATE,'TVSCHEDULE',COUNT(MOBILE_NUMBER) HITS, COUNT(DISTINCT(MOBILE_NUMBER)) UNIQUE_NO,'GJ'
FROM REQUEST_LOG  
WHERE SERVICE_TYPE LIKE 'TV%'
AND request_id BETWEEN (select min(request_id) from REQUEST_LOG 
WHERE REQUEST_DATE>DATE_SUB(CURDATE(), INTERVAL 3 DAY))
AND  (SELECT MIN(REQUEST_ID) FROM REQUEST_LOG WHERE REQUEST_DATE>CURDATE()-2)
AND SUBSTR(MOBILE_NUMBER,1,6) IN (SELECT SERIES FROM idea.HUTCH_SERIES_MST WHERE CIRCLE='GJ');

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is probably not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Explanation:
Hi Rajesh and thanks for your report.

Unfortunately, it does not contain anything useful for verification...

  o MySQL server 4.1.20 is rather old, please try with current GA and see if the error persists.
  o There is no mention of c/J version and JDK version used.
  o There is no mention if you *prepared* statement for execution in command line client or just run as normal statement.
  o Did you checked MySQL server error log and made sure server is not crashing?
  o And finally, there is no complete test case attached...

Now you tell me how can this be S1 with so little data provided? What am I to do now? Invent your environment, put some arbitrary data in arbitrary tables and test with arbitrary code I wrote? This would probably lead to "Can't repeat" ruling and I don't think that is what you want. The devil is usually in details; table types, SQL_MODE, some little setting in connection string and so on.

So please make an effort to complete this bug report in the sense that your test and info can easily be transferred to my boxes for testing. Or buy a support contract.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".