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

posted @ 2019-05-19 17:59  明月心~  阅读(1927)  评论(0编辑  收藏  举报