原先使用ForUpdateSkipLocked，但直到11g，這個參數還未被正式支持，而且在此之上使用排序還存在問題，所以改用符合ANSI的ForUpdateNowait來實現。
CREATE OR REPLACE PACKAGE BODY RESB_MT_TABLE_PKG AS
-- Try to lock thw row by RowId
-- 1 Successful
-- 0 Failed
FUNCTION RESB_MT_LOCK_ROW(i_table_source in varchar2, i_rid in rowid)
RETURN NUMBER IS
o_ret_id number := 0;
BEGIN
EXECUTE IMMEDIATE ''select 1
from '' || i_table_source || ''
where rowid = :x
for update nowait''
INTO o_ret_id
USING i_rid;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -54 THEN
RETURN 0;
ELSE
RAISE;
END IF;
END;
-- Update the columns which you want.
PROCEDURE RESB_MT_UPDATE_COLUMNS(i_table_source in varchar2,
i_update_expression in varchar2) IS
BEGIN
EXECUTE IMMEDIATE ''update '' || i_table_source || '' set '' ||
i_update_expression ||
'' where rowid in (select rid from RESB_MT_TT_ROWIDS)'';
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
-- Find in all and Skip locked
-- Void
PROCEDURE RESB_MT_FIND_ROWS_VOID(i_table_source in varchar2,
i_search_condition in varchar2,
i_order_expression in varchar2,
i_update_expression in varchar2,
i_rcount in number) IS
TYPE c_type IS REF CURSOR;
resb_mt_cur c_type;
v_rowid ROWID;
v_locked_count NUMBER := 0;
v_sql VARCHAR2(4000) := ''select rowid from '' || i_table_source ||
'' where '' || i_search_condition ||
'' order by '' || i_order_expression;
BEGIN
OPEN resb_mt_cur FOR v_sql;
LOOP
FETCH resb_mt_cur
INTO v_rowid;
EXIT WHEN resb_mt_cur%NOTFOUND;
IF RESB_MT_LOCK_ROW(i_table_source, v_rowid) = 1 THEN
INSERT INTO RESB_MT_TT_ROWIDS VALUES (v_rowid);
v_locked_count := v_locked_count + 1;
END IF;
EXIT WHEN v_locked_count = i_rcount;
END LOOP;
CLOSE resb_mt_cur;
-- Update the columns which you want
IF i_update_expression IS NOT NULL THEN
RESB_MT_UPDATE_COLUMNS(i_table_source, i_update_expression);
END IF;
RETURN;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
-- Find in all and Skip locked
-- Return CURSOR
PROCEDURE RESB_MT_FIND_ROWS(i_table_source in varchar2,
i_search_condition in varchar2,
i_order_expression in varchar2,
i_update_expression in varchar2,
i_rcount in number,
o_resb_mt_cur out resb_mt_cursor_type) IS
TYPE c_type IS REF CURSOR;
resb_mt_cur c_type;
v_rowid ROWID;
v_locked_count NUMBER := 0;
v_sql VARCHAR2(4000) := ''select rowid from '' || i_table_source ||
'' where '' || i_search_condition ||
'' order by '' || i_order_expression;
v_o_sql VARCHAR2(4000) := ''select * from '' || i_table_source ||
'' where rowid in (select rid from RESB_MT_TT_ROWIDS)'' ||
'' order by '' || i_order_expression;
BEGIN
OPEN resb_mt_cur FOR v_sql;
LOOP
FETCH resb_mt_cur
INTO v_rowid;
EXIT WHEN resb_mt_cur%NOTFOUND;
IF RESB_MT_LOCK_ROW(i_table_source, v_rowid) = 1 THEN
INSERT INTO RESB_MT_TT_ROWIDS VALUES (v_rowid);
v_locked_count := v_locked_count + 1;
END IF;
EXIT WHEN v_locked_count = i_rcount;
END LOOP;
CLOSE resb_mt_cur;
-- Update the columns which you want
IF i_update_expression IS NOT NULL THEN
RESB_MT_UPDATE_COLUMNS(i_table_source, i_update_expression);
END IF;
OPEN o_resb_mt_cur FOR v_o_sql;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
END;
這個性能的關鍵是要及時回寫狀態欄位，使下一個線程不會嘗試太多的記錄。
Oracle的AQ也同樣實現，各位大蝦誰能講解一下其實現方法？
