NutzCN Logo
问答 nutzwk定时任务集群后,出现deadlock
发布于 204天前 作者 sunhai1988 337 次浏览 复制 上一个帖子 下一个帖子
标签: nutzwk
Job 5d1f1558cd2a472cbfbd6f92a9970424.5d1f1558cd2a472cbfbd6f92a9970424 execution complete at  17:44:05 03/30/2017 and reports: null
2017-03-30 17:44:05,065 org.quartz.core.ErrorLogger.schedulerError(QuartzScheduler.java:2425) ERROR - An error occurred while scanning for the next triggers to fire.
org.quartz.JobPersistenceException: Couldn't acquire next trigger: Deadlock found when trying to get lock; try restarting transaction [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction]
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2864)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport$40.execute(JobStoreSupport.java:2759)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport$40.execute(JobStoreSupport.java:2757)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3803)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTriggers(JobStoreSupport.java:2756)
        at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:272)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at sun.reflect.GeneratedConstructorAccessor96.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
        at com.mysql.jdbc.Util.getInstance(Util.java:387)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:950)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
        at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2723)
        at com.alibaba.druid.filter.FilterAdapter.preparedStatement_executeUpdate(FilterAdapter.java:1069)
        at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeUpdate(FilterEventAdapter.java:491)
        at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:2721)
        at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeUpdate(PreparedStatementProxyImpl.java:158)
        at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeUpdate(DruidPooledPreparedStatement.java:253)
        at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.updateTriggerStateFromOtherState(StdJDBCDelegate.java:1439)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2842)
        ... 5 more
10 回复

我修改过 varchar(200)-- varchar(128)
200 提示太长了

/*QUARTZ_01*/
DROP TABLE IF EXISTS SYS_QRTZ_FIRED_TRIGGERS;
/*QUARTZ_02*/
DROP TABLE IF EXISTS SYS_QRTZ_PAUSED_TRIGGER_GRPS;
/*QUARTZ_03*/
DROP TABLE IF EXISTS SYS_QRTZ_SCHEDULER_STATE;
/*QUARTZ_04*/
DROP TABLE IF EXISTS SYS_QRTZ_LOCKS;
/*QUARTZ_05*/
DROP TABLE IF EXISTS SYS_QRTZ_SIMPLE_TRIGGERS;
/*QUARTZ_06*/
DROP TABLE IF EXISTS SYS_QRTZ_SIMPROP_TRIGGERS;
/*QUARTZ_07*/
DROP TABLE IF EXISTS SYS_QRTZ_CRON_TRIGGERS;
/*QUARTZ_08*/
DROP TABLE IF EXISTS SYS_QRTZ_BLOB_TRIGGERS;
/*QUARTZ_09*/
DROP TABLE IF EXISTS SYS_QRTZ_TRIGGERS;
/*QUARTZ_10*/
DROP TABLE IF EXISTS SYS_QRTZ_JOB_DETAILS;
/*QUARTZ_11*/
DROP TABLE IF EXISTS SYS_QRTZ_CALENDARS;
/*QUARTZ_12*/
CREATE TABLE SYS_QRTZ_JOB_DETAILS(SCHED_NAME VARCHAR(120) NOT NULL,JOB_NAME VARCHAR(128) NOT NULL,JOB_GROUP VARCHAR(128) NOT NULL,DESCRIPTION VARCHAR(250) NULL,JOB_CLASS_NAME VARCHAR(250) NOT NULL,IS_DURABLE VARCHAR(1) NOT NULL,IS_NONCONCURRENT VARCHAR(1) NOT NULL,IS_UPDATE_DATA VARCHAR(1) NOT NULL,REQUESTS_RECOVERY VARCHAR(1) NOT NULL,JOB_DATA BLOB NULL,PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP))ENGINE=InnoDB;
/*QUARTZ_13*/
CREATE TABLE SYS_QRTZ_TRIGGERS(SCHED_NAME VARCHAR(120) NOT NULL,TRIGGER_NAME VARCHAR(128) NOT NULL,TRIGGER_GROUP VARCHAR(128) NOT NULL,JOB_NAME VARCHAR(128) NOT NULL,JOB_GROUP VARCHAR(128) NOT NULL,DESCRIPTION VARCHAR(250) NULL,NEXT_FIRE_TIME BIGINT(13) NULL,PREV_FIRE_TIME BIGINT(13) NULL,PRIORITY INTEGER NULL,TRIGGER_STATE VARCHAR(16) NOT NULL,TRIGGER_TYPE VARCHAR(8) NOT NULL,START_TIME BIGINT(13) NOT NULL,END_TIME BIGINT(13) NULL,CALENDAR_NAME VARCHAR(200) NULL,MISFIRE_INSTR SMALLINT(2) NULL,JOB_DATA BLOB NULL,PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)REFERENCES SYS_QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP))ENGINE=InnoDB;
/*QUARTZ_14*/
CREATE TABLE SYS_QRTZ_SIMPLE_TRIGGERS(SCHED_NAME VARCHAR(120) NOT NULL,TRIGGER_NAME VARCHAR(128) NOT NULL,TRIGGER_GROUP VARCHAR(128) NOT NULL,REPEAT_COUNT BIGINT(7) NOT NULL,REPEAT_INTERVAL BIGINT(12) NOT NULL,TIMES_TRIGGERED BIGINT(10) NOT NULL,PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)REFERENCES SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))ENGINE=InnoDB;
/*QUARTZ_15*/
CREATE TABLE SYS_QRTZ_CRON_TRIGGERS(SCHED_NAME VARCHAR(120) NOT NULL,TRIGGER_NAME VARCHAR(128) NOT NULL,TRIGGER_GROUP VARCHAR(128) NOT NULL,CRON_EXPRESSION VARCHAR(120) NOT NULL,TIME_ZONE_ID VARCHAR(80),PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)REFERENCES SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))ENGINE=InnoDB;
/*QUARTZ_16*/
CREATE TABLE SYS_QRTZ_SIMPROP_TRIGGERS(SCHED_NAME VARCHAR(120) NOT NULL,TRIGGER_NAME VARCHAR(128) NOT NULL,TRIGGER_GROUP VARCHAR(128) NOT NULL,STR_PROP_1 VARCHAR(512) NULL,STR_PROP_2 VARCHAR(512) NULL,STR_PROP_3 VARCHAR(512) NULL,INT_PROP_1 INT NULL,INT_PROP_2 INT NULL,LONG_PROP_1 BIGINT NULL,LONG_PROP_2 BIGINT NULL,DEC_PROP_1 NUMERIC(13,4) NULL,DEC_PROP_2 NUMERIC(13,4) NULL,BOOL_PROP_1 VARCHAR(1) NULL,BOOL_PROP_2 VARCHAR(1) NULL,PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))ENGINE=InnoDB;
/*QUARTZ_17*/
CREATE TABLE SYS_QRTZ_BLOB_TRIGGERS (SCHED_NAME VARCHAR(120) NOT NULL,TRIGGER_NAME VARCHAR(128) NOT NULL,TRIGGER_GROUP VARCHAR(128) NOT NULL,BLOB_DATA BLOB NULL,PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP),INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP),FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)REFERENCES SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP))ENGINE=InnoDB;
/*QUARTZ_18*/
CREATE TABLE SYS_QRTZ_CALENDARS (SCHED_NAME VARCHAR(120) NOT NULL,CALENDAR_NAME VARCHAR(128) NOT NULL,CALENDAR BLOB NOT NULL,PRIMARY KEY (SCHED_NAME,CALENDAR_NAME))ENGINE=InnoDB;
/*QUARTZ_19*/
CREATE TABLE SYS_QRTZ_PAUSED_TRIGGER_GRPS (SCHED_NAME VARCHAR(120) NOT NULL,TRIGGER_GROUP VARCHAR(128) NOT NULL,PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP))ENGINE=InnoDB;
/*QUARTZ_20*/
CREATE TABLE SYS_QRTZ_FIRED_TRIGGERS (SCHED_NAME VARCHAR(120) NOT NULL,ENTRY_ID VARCHAR(95) NOT NULL,TRIGGER_NAME VARCHAR(128) NOT NULL,TRIGGER_GROUP VARCHAR(128) NOT NULL,INSTANCE_NAME VARCHAR(200) NOT NULL,FIRED_TIME BIGINT(13) NOT NULL,SCHED_TIME BIGINT(13) NOT NULL,PRIORITY INTEGER NOT NULL,STATE VARCHAR(16) NOT NULL,JOB_NAME VARCHAR(128) NULL,JOB_GROUP VARCHAR(128) NULL,IS_NONCONCURRENT VARCHAR(1) NULL,REQUESTS_RECOVERY VARCHAR(1) NULL,PRIMARY KEY (SCHED_NAME,ENTRY_ID))ENGINE=InnoDB;
/*QUARTZ_21*/
CREATE TABLE SYS_QRTZ_SCHEDULER_STATE (SCHED_NAME VARCHAR(120) NOT NULL,INSTANCE_NAME VARCHAR(128) NOT NULL,LAST_CHECKIN_TIME BIGINT(13) NOT NULL,CHECKIN_INTERVAL BIGINT(13) NOT NULL,PRIMARY KEY (SCHED_NAME,INSTANCE_NAME))ENGINE=InnoDB;
/*QUARTZ_22*/
CREATE TABLE SYS_QRTZ_LOCKS (SCHED_NAME VARCHAR(120) NOT NULL,LOCK_NAME VARCHAR(40) NOT NULL,PRIMARY KEY (SCHED_NAME,LOCK_NAME))ENGINE=InnoDB;
/*QUARTZ_23*/
CREATE INDEX IDX_SYS_QRTZ_J_REQ_RECOVERY ON SYS_QRTZ_JOB_DETAILS(SCHED_NAME,REQUESTS_RECOVERY);
/*QUARTZ_24*/
CREATE INDEX IDX_SYS_QRTZ_J_GRP ON SYS_QRTZ_JOB_DETAILS(SCHED_NAME,JOB_GROUP);
/*QUARTZ_25*/
CREATE INDEX IDX_SYS_QRTZ_T_J ON SYS_QRTZ_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
/*QUARTZ_26*/
CREATE INDEX IDX_SYS_QRTZ_T_JG ON SYS_QRTZ_TRIGGERS(SCHED_NAME,JOB_GROUP);
/*QUARTZ_27*/
CREATE INDEX IDX_SYS_QRTZ_T_C ON SYS_QRTZ_TRIGGERS(SCHED_NAME,CALENDAR_NAME);
/*QUARTZ_28*/
CREATE INDEX IDX_SYS_QRTZ_T_G ON SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);
/*QUARTZ_29*/
CREATE INDEX IDX_SYS_QRTZ_T_STATE ON SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE);
/*QUARTZ_30*/
CREATE INDEX IDX_SYS_QRTZ_T_N_STATE ON SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE);
/*QUARTZ_31*/
CREATE INDEX IDX_SYS_QRTZ_T_N_G_STATE ON SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE);
/*QUARTZ_32*/
CREATE INDEX IDX_SYS_QRTZ_T_NEXT_FIRE_TIME ON SYS_QRTZ_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME);
/*QUARTZ_33*/
CREATE INDEX IDX_SYS_QRTZ_T_NFT_ST ON SYS_QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME);
/*QUARTZ_34*/
CREATE INDEX IDX_SYS_QRTZ_T_NFT_MISFIRE ON SYS_QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME);
/*QUARTZ_35*/
CREATE INDEX IDX_SYS_QRTZ_T_NFT_ST_MISFIRE ON SYS_QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE);
/*QUARTZ_36*/
CREATE INDEX IDX_SYS_QRTZ_T_NFT_ST_MISFIRE_GRP ON SYS_QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE);
/*QUARTZ_37*/
CREATE INDEX IDX_SYS_QRTZ_FT_TRIG_INST_NAME ON SYS_QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME);
/*QUARTZ_38*/
CREATE INDEX IDX_SYS_QRTZ_FT_INST_JOB_REQ_RCVRY ON SYS_QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY);
/*QUARTZ_39*/
CREATE INDEX IDX_SYS_QRTZ_FT_J_G ON SYS_QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP);
/*QUARTZ_40*/
CREATE INDEX IDX_SYS_QRTZ_FT_JG ON SYS_QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP);
/*QUARTZ_41*/
CREATE INDEX IDX_SYS_QRTZ_FT_T_G ON SYS_QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP);
/*QUARTZ_42*/
CREATE INDEX IDX_SYS_QRTZ_FT_TG ON SYS_QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP);
  <dependency>
            <groupId>org.quartz-scheduler</groupId>
            <artifactId>quartz</artifactId>
            <version>2.2.3</version>
        </dependency>

不知道你的问题解决了没,我也是纠结了两天了,和同事一同商量找到了最终的问题解决方案,分享给你一下,
quartz文档提到,如果在集群环境下,最好将配置项org.quartz.jobStore.txIsolationLevelSerializable设置为true
这个选项在mysql下会非常容易出现死锁问题。
quartz需要提升隔离级别来保障自己的运作,而不同类型的数据对隔离的实现不同,所以quartz提供一个设置序列化的隔离级别,由于mysql默认的是可重复读,比提交读高了一个级别,所以已经可以满足quartz集群的正常运行。
所以将quartz.properties org.quartz.jobStore.txIsolationLevelSerializable = false 即可解决此问题。 from oschina

数据库url里的编码,要和你的数据库编码一致。

Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir

?useUnicode=true&characterEncoding=utf8

不是已经很明显了么……

Deadlock found when trying to get lock; try restarting transaction

只会在第二个tomcat启动的时候报一次而已,不影响运行。

话说,这是quartz的问题,你可以到其官网找下答案~~~

哦哦,,才看到是分享,2333~

先有这个问题,我提了一个问题,最后找到了个答案,也就贴出来了,估计其他人也可能遇到

添加回复
请先登陆
回到顶部