我在使用quartz + mysql + Tomcat7时遇到了Lock wait timeout exceeded的问题。当我在开发和测试环境中使用一个服务器时，没有出现这个问题，但是在部署后，在生产环境中（有两台服务器）出现了问题。
org.quartz.JobPersistenceException: Couldn't store trigger 'trigger1355274000000' for '1355274000000' job:Lock wait timeout exceeded; try restarting transaction [See nested exception: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeTrigger(JobStoreSupport.java:1270)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.triggerFired(JobStoreSupport.java:2961)
at org.quartz.impl.jdbcjobstore.JobStoreSupport$38.execute(JobStoreSupport.java:2871)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3788)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.triggerFired(JobStoreSupport.java:2865)
at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:319)
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2046)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1964)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1949)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.updateTrigger(StdJDBCDelegate.java:1295)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeTrigger(JobStoreSupport.java:1256)
这是我的quartz配置。
org.quartz.scheduler.instanceName = ApplicationDemoScheduler
org.quartz.scheduler.instanceId = AUTO
org.quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount=10
org.quartz.threadPool.threadPriority = 5
org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread=true
org.quartz.scheduler.threadsInheritContextClassLoaderOfInitializer=true
org.quartz.jobStore.misfireThreshold=180000
org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.useProperties=false
org.quartz.jobStore.dataSource=qzDS
org.quartz.jobStore.tablePrefix=QRTZ_
org.quartz.jobStore.isClustered=true
org.quartz.jobStore.selectWithLockSQL=select LOCK_NAME from QRTZ_LOCKS where LOCK_NAME=?
org.quartz.jobStore.clusterCheckinInterval = 20000
org.quartz.scheduler.dbFailureRetryInterval=15000
org.quartz.dataSource.qzDS.jndiURL=java:comp/env/jdbc/appDemoJndi
这个数据源引用了在Tomcat中配置的数据源。
<Resource name="jdbc/appDemoJndi" auth="Container" type="javax.sql.DataSource"
username="testuser" password="test_pswd"
url="jdbc:mysql://localhost/testuser"
driverClassName="com.mysql.jdbc.Driver"
initialSize="20" maxWait="5000"
maxActive="40" maxIdle="5"
testWhileIdle="true"
testOnBorrow="true"
defaultAutoCommit="false"
maxOpenPreparedStatements="20"
validationQuery="select 1"
poolPreparedStatements="true"/>
我不依赖于任何Hibernate或其他技术。我不确定这个错误是否与任何quartz配置或MYSQL配置相关。
有没有人遇到过相同的问题并解决了？请帮忙……
谢谢。