Oracle队列实现
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编程艺术 深入理解数据库体系结构第三版
最新文章
- [转载]python property
- 数据库——DQL（语句查询）
- Adobe Flash Media Server安装
- iOS6：在你的App内使用Passbook
- MongoDB 3: 使用中的问题，及其应用场景
- WPF中使用ValueConverter来实现“范围条件触发器”
- HDU 3271-SNIBB（数位dp）
- 注解SpringMVC
- hibernate 映射<五>多对多双向映射
- HDU 1232 畅通工程（最小生成树+并查集）
- 【medium】990. Satisfiability of Equality Equations 并查集
- Redis集合操作
- c# 求第30位数的值
- Delphi - 如何执行Windows、OSX、Linux的外部程序？
- 浅谈ajax同步、异步的问题
- Pig系统分析(5)-从Logical Plan到Physical Plan
- Redis高级命令操作大全--推荐
- NGUI动态给EventDelegate加参数
- 文件IO之——阻塞和非阻塞及perror函数
- A day