多台服务器共享一个数据库(oracle数据库)，防止同时访问一批数据

 

在处理数据时，经常会用定时任务间隔去跑一批数据。如果一台服务器去查询执行倒也没事，如果出于负载均衡考虑，多台服务器同时去跑这个数据库的一张表的数据时，就很有可能会取到相同数据，出现执行数据重复的问题。

 

贴出我的做法。

 

--创建表定时任务取模求余表记录表
create table T_TASKMODREMAINDER
(
 TASKNAME  varchar2(100) ,
 MODREMAINDER number(3) ,
 UPDATESEQ number(20) default 0,
 TASKIP  VARCHAR2(32),
 UPDATETIME date
)
/

-- Add comments to the columns 
comment on column T_TASKMODREMAINDER.TASKNAME
  is '定时任务类名称'
/
comment on column T_TASKMODREMAINDER.MODREMAINDER
  is '定时任务执行时使用取模余数。必须从0开始，按1递增并保持连续。备注：每台机器执行定时任务时，首先要获取updatetime时间最早的该余数并对该记录上锁（如果获取失败则获取下updatetime次早的余数，直如果所有余数都获取失败，定时任务返回），然后使用该余数从数据库获取数据，获取完毕提交或失败回滚时自动释放记录锁。'
/
comment on column T_TASKMODREMAINDER.UPDATESEQ
  is '按updateseq从小到大的顺序获取取模余数。获取余数上锁后，先更新此字段，然后再获取业务数据。'
/
comment on column T_TASKMODREMAINDER.TASKIP
  is '当前使用该余数的机器IP，如：10.10.10.10。备注：1.并不是只有该IP才可以使用该余数，而是谁获取到该余数则在此登记。2.IP在使用余数时自动更新，初始化脚本不需要填写'
/
comment on column T_TASKMODREMAINDER.UPDATETIME
  is '使用该余数的机器最近执行任务的时间。'
/

---初始化表插入数据，有三台服务器，则插入三条数据，MODREMAINDER从0开始







insert into T_TASKMODREMAINDER (TASKNAME, MODREMAINDER, UPDATESEQ, TASKIP, UPDATETIME)
values ('BatchOperateTask', 0, 0, '', sysdate)
/
insert into T_TASKMODREMAINDER (TASKNAME, MODREMAINDER, UPDATESEQ, TASKIP, UPDATETIME)
values ('BatchOperateTask', 1, 0, '', sysdate)
/
insert into T_TASKMODREMAINDER (TASKNAME, MODREMAINDER, UPDATESEQ, TASKIP, UPDATETIME)
values ('BatchOperateTask', 2, 0, '', sysdate)
/
commit
/
--创建T_TASKMODREMAINDER 表的序列
create sequence seq_TASKMODREMAINDER start with 1 increment by 1
/

 

--创建取模函数
create or replace function f_func_gettaskmodremainder
(
    str_in_taskname    in     T_TASKMODREMAINDER.TASKNAME%type,     --定时任务名称
    i_o_modebase       out    number,                               --取模基数
    i_o_remainder      out    number                                --取模余数
) return integer
--返回值：
--  0：异常 
--  1：成功 
--  2：取模余数全部被其他机器锁定
--  3：取模余数记录和取模基数不匹配 
--  4：无定时任务配置数据

------=========        注意       ================
--1、本存储过程执行过后，则会锁定某条取模余数记录（行锁），
--   调用程序在退出之前请一定要提交或回滚事务时以释放该行锁。
--2、调用方存储过程内只要一提交或回滚则会立即释放该行锁，
--   所以，调用方获取数据的模式为：获取锁、更新数据、提交，不允许出现获取锁、更新数据、提交、再获取数据的情况
--3、取数据过程中必须更新数据状态，防止多个定时任务重复获取数据或因竞争数据引发死锁
--4、调用方在所有更新相同表数据的地方都必须采用取模方式更新数据。

is
    --自定义异常，用于标志因资源忙导致锁定记录不成功
    resource_busy    exception;

    --将锁定记录异常和名字resource_busy绑定
    pragma exception_init(resource_busy, -54);

    i_l_remaindercount   number;
    i_l_locksuccess      number := 0;
    dt_l_nowtime         date := sysdate;
    i_l_return           number;
    i_l_updateseq        number;

begin

    --获取取模基数（获取数据定时任务总共有几个定时任务）
    begin         
         select configvalue into i_o_modebase from t_config where configname = 'portal_task_count'; --可以写死总共定时任务，为了使用方便可配置
    exception
        when no_data_found then
            --无定时任务配置数据
            return 4;
    end;

    --检查取模余数记录是否正常
    select count(*)
      into i_l_remaindercount
      from t_taskmodremainder
     where taskname = str_in_taskname
       and modremainder >=0
       and modremainder < i_o_modebase;
       
    --如果取模余数记录和取模基数不匹配，异常退出
    if ( i_l_remaindercount <> i_o_modebase ) then
       return 3;   
    end if;
    
    --获取获取数据序列
    select SEQ_TASKMODREMAINDER.nextval
      into i_l_updateseq
      from dual;
      
    --对于序列用完又重头开始的情况，需要将大于当前序列的记录更新为1，每次仅处理一条
    begin
        select modremainder
          into i_o_remainder
          from t_taskmodremainder
         where taskname = str_in_taskname
           and updateseq > i_l_updateseq
           and rownum = 1
           for update nowait;
        
           --获取成功，更新
           update t_taskmodremainder
              set updateseq = 1
            where taskname = str_in_taskname
              and modremainder = i_o_remainder;
                
    exception
        --如果没有从头开始的情况，不处理
        when no_data_found then
            null;
        --锁定不成功，不处理
        when resource_busy then
            null;
    end;  
      
    --按updateseq从小到大的顺序获取取模余数记录并上锁
    for i in 1..i_o_modebase loop
        begin
          select modremainder
            into i_o_remainder
            from t_taskmodremainder
           where taskname = str_in_taskname
             and modremainder = ( select modremainder
                                      from ( select modremainder, rownum rn
                                               from ( select modremainder from t_taskmodremainder
                                                       where taskname = str_in_taskname
                                                         and modremainder < i_o_modebase
                                                       order by updateseq
                                                    )
                                           )
                                     where rn = i
                                  )
             for update nowait;

             --锁定记录成功，更新
             update t_taskmodremainder
                set updatetime = dt_l_nowtime,
                    taskip = sys_context ('userenv', 'ip_address'),
                    updateseq = i_l_updateseq
              where taskname = str_in_taskname
                and modremainder = i_o_remainder;
             
             --设置锁定成功标志，跳出循环
             i_l_locksuccess := 1;
             exit;

        exception
            --锁定不成功，继续锁定updatetime次旧的一个
            when resource_busy then
                null;
        end;

    end loop;

    --如果锁定取模余数记录不成功，返回2，成功返回1
    return case i_l_locksuccess
               when 1 then 1
               else 2
           end;

exception
    when others then
         return 0;
end f_func_gettaskmodremainder;
/

 

=========================================

 

 

--创建存储数据表
create table t_batchoperatedata
(
  id            number primary key,
  username      varchar2(20) ,
  userid        varchar2(20) ,
  insertTime   date default sysdate,
  status        number ,
  roleCode      varchar2(20) ,
  batchid       number default -1,
  operatertime date
)
/
-- Add comments to the table 
comment on table t_batchoperatedata
  is '存储数据表'
/
comment on column t_batchoperatedata.username
  is '用户名'
/
comment on column t_batchoperatedata.userid
  is '用户id'
/
comment on column t_batchoperatedata.insertTime
  is '入库时间'
/
comment on column t_batchoperatedata.status
  is '当前数据状态 1.未处理；2.已处理；3已获取'
/

comment on column t_batchoperatedata.roleCode
  is '角色编号'
/


--获取批量执行数据
create or replace function f_getoperatedata
(
str_taskname in T_TASKMODREMAINDER.taskname%type, --定时任务名称
i_o_result   out integer
)

 return pkg_type.refcur --定义返回类型
 is
  results pkg_type.refcur; --声明游标

  get_batch_execute_maxs varchar2(20); --最大获取条数

  i_o_modebase  number; --模基数
  i_o_remainder number; --模余数
  i_funcresult  integer; --函数结果输出参数
  temp_id number;
  t_batchid     number;

begin

      --获取模基数与本次抢占到的模基数
    i_funcresult := f_func_gettaskmodremainder(  str_taskname,
                                                 i_o_modebase,
                                                 i_o_remainder
                                              );

    --对返回码处理
    begin
        case i_funcresult
            when 1 then
                i_o_result := 1;
            when 2 then
                i_o_result := 1;
            when 3 then
                i_o_result := 2;
            when 4 then
                i_o_result := 3;
            else
                i_o_result := 0;
         end case;
    exception
        when others then
        i_o_result := 0;
    end;

       if i_funcresult != 1 then
        rollback;
        open results for select * from dual where 2=1;
        return results;
    end if;

  --获取每次最大处理条数
  select configvalue
    into get_batch_execute_maxs
    from t_config
   where configname =
         'get_batch_execute_maxs';

   select seq_batchid.nextval into  t_batchid from dual;

   update t_batchoperatedata b
      set batchid = t_batchid, status = 3,operatertime=sysdate
    where rownum <= get_batch_execute_maxs
      and b.status = 1
	order by b.insertTime;

  open results for

  --查询出要处理的没个号码
    select b.id,                   --id
           b.username,           --用户名
           b.userid,            --用户id
           b.insertTime,           --入库时间
           b.roleCode,        --操作者角色账号
      from t_batchoperatedata b
     where b.batchid=t_batchid;
  commit;
  return results;
exception
  when others then
    return results;
end f_getoperatedata;
/

 

在类中调用f_getoperatedata函数来获取要操作的数据，每次在函数f_getoperatedata中都会调用f_func_gettaskmodremainder函数获取模基数与本次抢占到的模基数，并且锁定t_taskmodremainder
表，防止其他服务器定时任务使用，确保每台服务器定时任务每次获取的数据不是同一批。

 

如果有某台服务器的定时任务获取到数据后，并且以修改表t_batchoperatedata中status = 3时，服务器濡染荡掉。那么可以再加一个定时任务来定时判断如果operatertime时间大于你认为超时的时间，则去修改t_batchoperatedata中status = 3的数据，全部修改状态为1，等待下次继续执行。

或者可直接在数据库中加个job任务来执行，认为数据库job较方便些，还省去写java代码。