mysql单表控制服务器集群job重复执行

项目中有用到quartz定时任务处理某一业务，但生产环境上是Tomcat集群，为了控制job重复执行，设计了一种处理方案，记录一下。
1.首先在数据库中新建一张表:
– 创建表
drop table if exists config_log_quartz;
create table config_log_quartz(
id_key varchar(128) not null comment ’ 主键id’,
target_type varchar(32) comment ‘加锁目标类型’,
target_id varchar(32) comment ‘加锁目标ID值’,
lock_date TIMESTAMP comment ‘加锁时间’,
expire_date TIMESTAMP comment ‘锁过期时间’,
created_by varchar(32) comment ‘创建人’,
created_date TIMESTAMP comment ‘创建时间’,
updated_by varchar(32) comment ‘更新人’,
updated_date TIMESTAMP comment ‘更新时间’
) comment=‘对象锁记录表’;

2.创建存储过程对上表进行操作

drop procedure if exists myTest.proc_dm_lock;
create PROCEDURE myTest.proc_dm_lock(
IN p_target_type VARCHAR(32),
IN p_target_id VARCHAR(32),
IN p_lock_mins NUMERIC,
OUT p_expire_date TIMESTAMP
)
BEGIN
set @v_lock_date = SYSDATE();
set @v_type_count = 0;
set @v_lock_mins = P_lock_mins;
set @p_expire_date = null;
set p_expire_date := NULL;
IF p_lock_mins is null or p_lock_mins <= 0 THEN
set @v_lock_mins := 30;
end if;
set @v_expire_date := DATE_ADD(@v_lock_date,INTERVAL @v_lock_mins MINUTE);
UPDATE config_log_quartz l
SET l.lock_date = @v_lock_date,
l.expire_date = @v_expire_date,
l.updated_by = ‘sys’,
l.updated_date = SYSDATE()
where l.expire_date < SYSDATE()
and l.target_id = p_target_id
and l.target_type = p_target_type;
if ROW_COUNT()>0 THEN
set p_expire_date := @v_expire_date;
else
begin
select count(1)
into @v_type_count
from config_log_quartz sbq
where sbq.target_type = p_target_type
and sbq.target_id = p_target_id;
if @v_type_count = 0 then
insert into config_log_quartz(id_key,target_type,target_id,lock_date,expire_date,created_by,created_date,updated_by,updated_date)
values(UUID(),p_target_type,p_target_id,@v_lock_date,@v_expire_date,‘sys’,SYSDATE(),‘sys’,SYSDATE());
set p_expire_date = @v_expire_date;
end if;
if @v_type_count>0 then
set p_expire_date := null;
end if;
end;
end if;
END;

3.当某一个job执行业务前 会先调用该存储过程 返回值是一个锁过期时间，如果返回值为null 则加锁失败 业务不执行。
加锁:
call myTest.proc_dm_lock(‘sys’,‘sys’,1,@val1);
select @val1;

再次调用：
call myTest.proc_dm_lock(‘sys’,‘sys’,1,@val1);
select @val1;

4.加锁成功的job执行业务成功后 解锁
– 解锁
update config_log_quartz q
set q.expire_date = SYSDATE()-0.0001,
q.updated_by = ‘sys’,
q.updated_date = SYSDATE()
where
q.expire_date>SYSDATE()
AND q.target_type = ‘tyzhen’
AND q.target_id = ‘sys’;

当然项目中在job具体业务类上实现加锁解锁是通过切面来实现的，这里不在赘述。