Oracle队列实现
-- 核心技术点：for update
创建测试表
create table t
( id number primary key,
processed_flag varchar2(1),
payload varchar2(20)
);
创建函数索引
create index
t_idx on
t( decode( processed_flag, 'N', 'N' ) );
插入几条测试数据
insert into t
select r,
case when mod(r,2) = 0 then 'N' else 'Y' end,
'payload ' || r
from (select level r
from dual
connect by level <= 5)
/
方式一，通过函数返回未锁定行
创建队列获取一行数据的函数
支持Oracle8.0及以后的版本
create or replace
function get_first_unlocked_row
return t%rowtype
as
resource_busy exception;
pragma exception_init( resource_busy, -54 );
l_rec t%rowtype;
begin
for x in ( select rowid rid
from t
where decode(processed_flag,'N','N') = 'N')
loop
begin
select * into l_rec
from t
where rowid = x.rid and processed_flag='N'
for update nowait;
return l_rec;
exception
when resource_busy then null;
when no_data_found then null;
end;
end loop;
return null;
end;
/
获取未加锁的第一行数据
declare
l_rec t%rowtype;
begin
l_rec := get_first_unlocked_row;
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end;
/
eoda/muphy> I got row 2, payload 2
获取未加锁的第二行数据
declare
pragma autonomous_transaction;
l_rec t%rowtype;
begin
l_rec := get_first_unlocked_row;
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
commit;
end;
/
eoda/muphy> I got row 4, payload 4
方式二，直接通过skip locked实现
获取未加锁的第一行数据
declare
l_rec t%rowtype;
cursor c
is
select *
from t
where decode(processed_flag,'N','N') = 'N'
FOR UPDATE
SKIP LOCKED;
begin
open c;
fetch c into l_rec;
if ( c%found )
then
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end if;
close c;
end;
/
eoda/muphy> I got row 2, payload 2
获取未加锁的第二行数据
declare
pragma autonomous_transaction;
l_rec t%rowtype;
cursor c
is
select *
from t
where decode(processed_flag,'N','N') = 'N'
FOR UPDATE
SKIP LOCKED;
begin
open c;
fetch c into l_rec;
if ( c%found )
then
dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end if;
close c;
commit;
end;
/
eoda/muphy> I got row 4, payload 4
--参考自Oracle编程艺术 深入理解数据库体系结构第三版