hibernate操作数据库出现死锁，获取锁超时等问题

[zhouyusunquan]

在存储数据时，出现以下问题
错误一 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
错误二 Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
错误三 Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
错误四 The last packet successfully received from the server was 720,005 milliseconds ago. The last packet sent successfully to the server was 2 milliseconds ago.


代码如下
this.mergeAll("call", calls);
this.mergeAll("manualService", manualServices);
this.mergeAll("agentService", agentServices);
this.mergeAll("queueService", queueServices);
this.mergeAll("callEp", calls);
this.mergeAll("manualServiceEp", manualServices);
this.mergeAll("agentServiceEp", agentServices);
this.mergeAll("queueServiceEp", queueServices);


dao层的方法如下
public void mergeAll(final String entityName,
final List<? extends Object> list) {
logger.info("entityName:"+entityName);
for (int i = 0; i < list.size(); i++) {
this.merge(entityName,list.get(i));
if ((i + 1) % 50 == 0) {
this.flush();
this.clear();
}
}
if (list.size()%50!= 0) {
this.flush();
this.clear();
}
logger.info("entityName:"+entityName+" flush了");
}


service只是调用了此方法



任务类如下
public class PersistenceCallCache implements StatefulJob {

private static Logger logger = U3CLogger.getLogger();
@Override
public void execute(JobExecutionContext arg0) throws JobExecutionException {
try {
long startTime = System.currentTimeMillis();
int callSize = CallCache.instance().persistence();// 呼叫流水持久化
long endTime = System.currentTimeMillis();
if ((endTime - startTime) > getSysConfig().getWarnedPerDur()) {
logger.warn("Persistence Time:" + (endTime - startTime) + ", info:\n\t {Call:" + callSize + "}.");
}
} catch (Exception e) {
logger.error("Persistence flow data error.", e);
}
}

}

以代码来看，是串行执行的，而且此表只有改程序新增或者修改，没有其他程序操作


运行过程中出现以下错误
错误一 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
错误二 Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
错误三 Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction


在mysql的错误日志中这几天并没有死锁等日志，我搜索其中的死锁，只发现了
171025 11:25:30 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_TRIGGERS SET TRIGGER_STATE = 'WAITING' WHERE TRIGGER_NAME = 'synDataTrigger' AND TRIGGER_GROUP = 'DEFAULT' AND TRIGGER_STATE = 'ACQUIRED'', Error_code: 1213
171025 11:25:30 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010914' at position 55458027
171027 15:18:04 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010914' at position 55458027, relay log './mysql_001-relay-bin.006134' position: 17227960
171027 15:22:50 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_TRIGGERS SET JOB_NAME = 'synDatajobDetail', JOB_GROUP = 'DEFAULT', IS_VOLATILE = 0, DESCRIPTION = null, NEXT_FIRE_TIME = 1508902250709, PREV_FIRE_TIME = 1508902230709, TRIGGER_STATE = 'WAITING', TRIGGER_TYPE = 'BLOB', START_TIME = 1467612450709, END_TIME = 0, CALENDAR_NAME = null, MISFIRE_INSTR = 0, PRIORITY = 5 WHERE TRIGGER_NAME = 'synDataTrigger' AND TRIGGER_GROUP = 'DEFAULT'', Error_code: 1213
171027 15:22:50 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010914' at position 68914418
171027 15:54:32 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010914' at position 68914418, relay log './mysql_001-relay-bin.006134' position: 25600170
171027 15:58:09 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_TRIGGERS SET JOB_NAME = 'agentJobDetail', JOB_GROUP = 'DEFAULT', IS_VOLATILE = 0, DESCRIPTION = null, NEXT_FIRE_TIME = 1508903510710, PREV_FIRE_TIME = 1508903490710, TRIGGER_STATE = 'WAITING', TRIGGER_TYPE = 'BLOB', START_TIME = 1467612450710, END_TIME = 0, CALENDAR_NAME = null, MISFIRE_INSTR = 0, PRIORITY = 5 WHERE TRIGGER_NAME = 'agentTrigger' AND TRIGGER_GROUP = 'DEFAULT'', Error_code: 1213
171027 15:58:09 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010914' at position 105796881
171027 16:02:06 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010914' at position 105796881, relay log './mysql_001-relay-bin.006134' position: 44632236
171027 16:08:09 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_TRIGGERS SET JOB_NAME = 'agentJobDetail', JOB_GROUP = 'DEFAULT', IS_VOLATILE = 0, DESCRIPTION = null, NEXT_FIRE_TIME = 1508906290710, PREV_FIRE_TIME = 1508906270710, TRIGGER_STATE = 'WAITING', TRIGGER_TYPE = 'BLOB', START_TIME = 1467612450710, END_TIME = 0, CALENDAR_NAME = null, MISFIRE_INSTR = 0, PRIORITY = 5 WHERE TRIGGER_NAME = 'agentTrigger' AND TRIGGER_GROUP = 'DEFAULT'', Error_code: 1213
171027 16:08:09 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010915' at position 16666488
171027 16:10:59 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010915' at position 16666488, relay log './mysql_001-relay-bin.006137' position: 16666590
171027 16:13:09 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_SIMPLE_TRIGGERS SET REPEAT_COUNT = 0, REPEAT_INTERVAL = 0, TIMES_TRIGGERED = 0 WHERE TRIGGER_NAME = 'recover_utry2211501171946021_1508907051046' AND TRIGGER_GROUP = 'RECOVERING_JOBS'', Error_code: 1213
171027 16:13:09 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010915' at position 33259018
171027 16:20:42 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010915' at position 33259018, relay log './mysql_001-relay-bin.006137' position: 33259120
171027 16:22:09 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_SIMPLE_TRIGGERS SET REPEAT_COUNT = 0, REPEAT_INTERVAL = 0, TIMES_TRIGGERED = 0 WHERE TRIGGER_NAME = 'recover_utry2211501171946021_1508907831475' AND TRIGGER_GROUP = 'RECOVERING_JOBS'', Error_code: 1213
171027 16:22:09 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010916' at position 1210487
171027 16:53:41 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010916' at position 1210487, relay log './mysql_001-relay-bin.006140' position: 1210632
171027 17:00:04 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'insert into u3c_flow_agent_service (deptId, callId, manualServiceId, agentNo, errorCode, errorInfo, serviceType, startTime, agentAnswerTime, clientAnswerTime, agentSpeakTime, hangupTime, endTime, hangupType, callType, mediaType, fromNo, agentServiceId) values ('5980', '10.168.241.176_20c17aa2-35e4-417a-8a41-979cd8f74389', '1efc33b527114d6397ea4c4518fe1e68', 'sip:[e-mail]', '400', '其他', 0, '2017-10-25 13:50:43', '2017-10-25 13:50:44', '2010-01-01 00:00:00', '2010-01-01 00:00:00', '2017-10-25 13:50:52', '2017-10-25 13:50:52', 1, 7, 1, ' ', '01ecde6d744d4955aa986b86d757b5fb')', Error_code: 1213
171027 17:00:04 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010916' at position 43183480
171027 17:17:11 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010916' at position 43183480, relay log './mysql_001-relay-bin.006140' position: 43183625
171027 17:21:29 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_TRIGGERS SET JOB_NAME = 'synDatajobDetail', JOB_GROUP = 'DEFAULT', IS_VOLATILE = 0, DESCRIPTION = null, NEXT_FIRE_TIME = 1508913890709, PREV_FIRE_TIME = 1508913870709, TRIGGER_STATE = 'WAITING', TRIGGER_TYPE = 'BLOB', START_TIME = 1467612450709, END_TIME = 0, CALENDAR_NAME = null, MISFIRE_INSTR = 0, PRIORITY = 5 WHERE TRIGGER_NAME = 'synDataTrigger' AND TRIGGER_GROUP = 'DEFAULT'', Error_code: 1213
171027 17:21:29 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010917' at position 23479745
171027 17:21:52 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010917' at position 23479745, relay log './mysql_001-relay-bin.006143' position: 23479890
171027 17:22:50 [ERROR] Slave SQL: Error 'Deadlock found when trying to get lock; try restarting transaction' on query. Default database: 'u3cdb'. Query: 'UPDATE U3C_P_QRTZ_TRIGGERS SET JOB_NAME = 'synDatajobDetail', JOB_GROUP = 'DEFAULT', IS_VOLATILE = 0, DESCRIPTION = null, NEXT_FIRE_TIME = 1508914790709, PREV_FIRE_TIME = 1508914770709, TRIGGER_STATE = 'WAITING', TRIGGER_TYPE = 'BLOB', START_TIME = 1467612450709, END_TIME = 0, CALENDAR_NAME = null, MISFIRE_INSTR = 0, PRIORITY = 5 WHERE TRIGGER_NAME = 'synDataTrigger' AND TRIGGER_GROUP = 'DEFAULT'', Error_code: 1213
171027 17:22:50 [Note] Slave SQL thread exiting, replication stopped in log 'mysql-bin.010918' at position 4711424
171027 17:23:03 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.010918' at position 4711424, relay log './mysql_001-relay-bin.006146' position: 4711569

并未发现我操作的表的死锁日志，现在无法出现的问题

[牵着蚂蚁散步]

没有高手哦