有一个表中的数据需要每月更新,数据量近百万,考虑由10台服务器分摊并分批进行更新
这就需要能够查询一批记录(如100行)并同时修改这批记录的更新状态,以免被正在更新的数据被其他服务器取到
加行级锁,查询到记录后再遍历结果集更新状态?
for update wait 10;
这样如果某台服务器锁定记录,直到更新状态并提交这段时间,其他所有服务器都得等待,只有这台服务器释放后其他服务器才能加锁进行更新操作,10台服务器同步操作,争抢的机率岂不是很大
而且测试了一下,1个会话锁定了记录后,其他会话在第1个会话锁释放后得到的结果集与第1个会话相同!
有没有办法使查询时对已经锁定的记录视而不见?
Google之...
果然有:
for update nowait skip locked;
for update skip locked选项是oracle的一个未公开的特性,它的含义是select时跳过被锁的记录
那么接下来遍历结果集更新状态
好像不对,结果集打开后再返回,就不能再打开了
而且测试了一下,skip locked选项指的是跳过锁定的记录不报错而已,1个会话锁定了记录后,其他会话在此期间返回的都是0行的结果集
看起来不是那么简单
在论坛上发了一个帖子,牛人codearts给出了完美的解决办法:
修改100行记录的状态为1(正在更新),并以returning,bulk collect into记录下ID
然后根据ID查询结果集并返回
----------------------------------------
完整描述如下:
-- 建表
create table t_musicinfo
(
id number(20) not null,
code varchar2(40) not null,
updatetime date default sysdate not null,
updatestatus number(3) default 0 not null
);
-- 测试数据
begin
for i in 1001..5000 loop
insert into t_musicinfo values(i,'88880000'||i,
to_date('20100401','yyyymmdd') + 1/96*i, 0);
end loop;
commit;
end;
/
-- REF游标
create or replace package pkgcur is
type refcur is ref cursor;
end pkgcur;
/
-- 创建数字数组类型
create or replace type arynum is table of number(20);
-- 获取需要更新的数据, 并将更新标志设为正在更新
create or replace function f_musicinfo_updatestatus
(
i_howlong in integer, -- 多久前的记录
o_result out integer -- 返回值: 0.成功, 11.未知异常, 21.参数为空
)
return pkgcur.refcur -- 返回结果集
as
v_idlist arynum;
v_result pkgcur.refcur;
begin
-- 修改100行记录的状态为正在更新, 并记录下ID
update t_musicinfo
set updatestatus = 1
where updatetime < sysdate - i_howlong
and updatestatus = 0
and rownum <= 100
returning id
bulk collect into v_idlist;
commit;
-- 返回结果集
open v_result for
select /*+ cardinality(b 100) */
id, code, updatetime, updatestatus
from t_musicinfo a inner join
table(cast(v_idlist as arynum)) b
on a.id = b.column_value;
o_result := 0;
return v_result;
exception
when others then -- 数据库异常
-- dbms_output.put_line(sqlcode||': '||sqlerrm);
o_result := 11;
if not v_result%isopen then
open v_result for select 'X' from dual where 1 = 0;
end if;
return v_result;
end f_musicinfo_updatestatus;
/
说来惭愧,returning,bulk collect into,这些用法从未接触过...
感谢codearts!