Consolidated email processing locks up under load

Haven't seen this in quite a while – unable to reproduce

The MySQL query generated from HQL statement is very costly, though it performs well in Oracle. It's possible that moving to hibernate 3.5 or 3.6 will fix this, or that we can use batch updates (and especially StatelessSession updates if we move to 3.5 or 3.6).

http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/batch.html#batch-update
http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/batch.html#batch-statelesssession

It's also possible that the subquery could be rewritten as a join, but I have had no luck with that. All my attempts have lead to HQL errors, SQL errors, or very slow performance. Here's the one thing that worked (except for the problem of identifying the dialect):

if("mysql".equalsIgnoreCase(dialect)) {
StringBuilder sqlBuffer = new StringBuilder();
sqlBuffer.append("update EVAL_ASSIGN_USER eau, EVAL_EMAIL_PROCESSING_QUEUE epq ");
if(sendingAvailableEmails) {
sqlBuffer.append("set eau.AVAILABLE_EMAIL_SENT=:dateSent ");
} else {
sqlBuffer.append("set eau.REMINDER_EMAIL_SENT=:dateSent ");
}
sqlBuffer.append("where eau.id = epq.EAU_ID and epq.USER_ID=:userId and epq.EMAIL_TEMPLATE_ID=:emailTemplateId");

updateQuery = session.createSQLQuery(sqlBuffer.toString());

} else {
StringBuilder hqlBuffer = new StringBuilder();
hqlBuffer.append("update EvalAssignUser ");
if(sendingAvailableEmails) {
hqlBuffer.append("set availableEmailSent = :dateSent ");
} else {
hqlBuffer.append("set reminderEmailSent = :dateSent ");
}
hqlBuffer.append("where id in (select eauId from EvalEmailProcessingData where emailTemplateId = :emailTemplateId and userId = :userId)");

updateQuery = session.createQuery(hqlBuffer.toString());
}