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 AS 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编程艺术 深入理解数据库体系结构第三版