强制 Oracle 以 SKIP LOCKED 返回前 N 行

Force Oracle to return TOP N rows with SKIP LOCKED

提问by Travis

There are a fewquestionson how to implement a queue-like table (lock specific rows, selecting a certain number of them, and skipping currently locked rows) in Oracle and SQL Server.

关于如何在 Oracle 和 SQL Server 中实现类队列表（锁定特定行，选择其中一定数量，并跳过当前锁定的行）有几个问题。

How can I guarantee that I retrieve a certain number (N) rows, assuming there are at least Nrows eligible?

N假设至少有N符合条件的行，我如何保证检索到一定数量的 ( ) 行？

From what I have seen, Oracle applies the WHEREpredicate before determining what rows to skip. This means that if I want to pull one row from a table, and two threads concurrently execute the same SQL, one will receive the row and the other an empty result set (even if there are more eligible rows).

据我所知，OracleWHERE在确定要跳过的行之前应用谓词。这意味着如果我想从表中拉出一行，并且两个线程同时执行相同的 SQL，一个将收到该行，另一个将收到一个空结果集（即使有更多符合条件的行）。

This is contrary to how SQL Server appears to handle the UPDLOCK, ROWLOCKand READPASTlock hints. In SQL Server, TOPmagically appears to limit the number of records aftersuccessfully attaining locks.

这与 SQL Server 处理UPDLOCK,ROWLOCK和READPASTlock 提示的方式相反。在 SQL Server 中，成功获得锁后，TOP似乎神奇地限制了记录数。

Note, two interesting articles hereand here.

请注意，这里和这里有两篇有趣的文章。

ORACLE

甲骨文

CREATE TABLE QueueTest (
    ID NUMBER(10) NOT NULL,
    Locked NUMBER(1) NULL,
    Priority NUMBER(10) NOT NULL
);

ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY (ID);

CREATE INDEX IX_QueuePriority ON QueueTest(Priority);

INSERT INTO QueueTest (ID, Locked, Priority) VALUES (1, NULL, 4);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (2, NULL, 3);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (3, NULL, 2);
INSERT INTO QueueTest (ID, Locked, Priority) VALUES (4, NULL, 1);

In two separate sessions, execute:

在两个单独的会话中，执行：

SELECT qt.ID
FROM QueueTest qt
WHERE qt.ID IN (
    SELECT ID
    FROM
        (SELECT ID FROM QueueTest WHERE Locked IS NULL ORDER BY Priority)
    WHERE ROWNUM = 1)
FOR UPDATE SKIP LOCKED

Note that the first returns a row, and the second session does not return a row:

请注意，第一个返回一行，第二个会话不返回一行：

Session 1

第一节

 ID
----
  4

Session 2

第二节

 ID
----

---

---

SQL SERVER

SQL服务器

CREATE TABLE QueueTest (
    ID INT IDENTITY NOT NULL,
    Locked TINYINT NULL,
    Priority INT NOT NULL
);

ALTER TABLE QueueTest ADD CONSTRAINT PK_QueueTest PRIMARY KEY NONCLUSTERED (ID);

CREATE INDEX IX_QueuePriority ON QueueTest(Priority);

INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 4);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 3);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 2);
INSERT INTO QueueTest (Locked, Priority) VALUES (NULL, 1);

In two separate sessions, execute:

在两个单独的会话中，执行：

BEGIN TRANSACTION
SELECT TOP 1 qt.ID
FROM QueueTest qt
WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE Locked IS NULL
ORDER BY Priority;

Note that both sessions return a different row.

请注意，两个会话都返回不同的行。

Session 1

第一节

 ID
----
  4

Session 2

第二节

 ID
----
  3

How can I get similar behavior in Oracle?

如何在 Oracle 中获得类似的行为？

采纳答案by Gary Myers

"From what I have seen, Oracle applies the WHERE predicate before determining what rows to skip."

“据我所知，Oracle 在确定要跳过的行之前应用 WHERE 谓词。”

Yup. It is the only possible way. You can't skip a row from a resultset until you have determined the resultset.

是的。这是唯一可能的方法。在确定结果集之前，您不能从结果集中跳过一行。

The answer is simply not to limit the number of rows returned by the SELECT statement. You can still use the FIRST_ROWS_n hints to direct the optimizer that you won't be grabbing the full data set.

答案只是不限制 SELECT 语句返回的行数。您仍然可以使用 FIRST_ROWS_n 提示来指示优化器您不会获取完整的数据集。

The software calling the SELECT should only select the first n rows. In PL/SQL, it would be

调用 SELECT 的软件应该只选择前 n 行。在 PL/SQL 中，它将是

DECLARE
  CURSOR c_1 IS  
    SELECT /*+FIRST_ROWS_1*/ qt.ID
    FROM QueueTest qt
    WHERE Locked IS NULL
    ORDER BY PRIORITY
    FOR UPDATE SKIP LOCKED;
BEGIN
  OPEN c_1;
  FETCH c_1 into ....
  IF c_1%FOUND THEN
     ...
  END IF;
  CLOSE c_1;
END;

回答by Stephen ODonnell

The solution Gary Meyers posted is about all I can think of, short of using AQ, which does all this for you and much more.

Gary Meyers 发布的解决方案是关于我能想到的所有解决方案，除了使用 AQ，它为您完成所有这些以及更多。

If you really want to avoid the PLSQL, you should be able to translate the PLSQL into Java JDBC calls. All you need to do is prepare the same SQL statement, execute it and then keep doing single row fetches on it (or N row fetches).

如果您真的想避免使用 PLSQL，您应该能够将 PLSQL 转换为 Java JDBC 调用。您需要做的就是准备相同的 SQL 语句，执行它，然后继续对其进行单行提取（或 N 行提取）。

The Oracle documentation at http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm#1023642gives some clue how to do this at the statement level:

http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm#1023642 上的 Oracle 文档提供了一些如何在语句级别执行此操作的线索：

To set the fetch size for a query, call setFetchSize() on the statement object prior to executing the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

要设置查询的提取大小，请在执行查询之前对语句对象调用 setFetchSize()。如果将提取大小设置为 N，则每次访问数据库时都会提取 N 行。

So you could code up something in Java that looks something like (in Pseudo code):

所以你可以用 Java 编写一些看起来像（在伪代码中）的东西：

stmt = Prepare('SELECT /*+FIRST_ROWS_1*/ qt.ID
FROM QueueTest qt
WHERE Locked IS NULL
ORDER BY PRIORITY
FOR UPDATE SKIP LOCKED');

stmt.setFetchSize(10);
stmt.execute();

batch := stmt.fetch();
foreach row in batch {
  -- process row
}
commit (to free the locks from the update)
stmt.close;

UPDATE

更新

Based on the comments below, a suggestion was made to use ROWNUM to limit the results received, but that won't work in this case. Consider the example:

根据下面的评论，有人建议使用 ROWNUM 来限制收到的结果，但这在这种情况下不起作用。考虑这个例子：

create table lock_test (c1 integer);

begin
  for i in 1..10 loop
    insert into lock_test values (11 - i);
  end loop;
  commit;
end;
/

Now we have a table with 10 rows. Note that I have carefully inserted the rows in reverse order, the row containing 10 is first, then 9 etc.

现在我们有一个有 10 行的表格。请注意，我已经小心地以相反的顺序插入了行，首先是包含 10 的行，然后是 9 等。

Say you want the first 5 rows, ordered ascending - ie 1 to 5. Your first try is this:

假设您想要前 5 行，按升序排列 - 即 1 到 5。您的第一次尝试是这样的：

select *
from lock_test
where rownum <= 5
order by c1 asc;

Which gives the results:

这给出了结果：

C1
--
6
7
8
9 
10

That is clearly wrong, and is a mistake almost everyone makes! Look at the explain plan for the query:

这显然是错误的，而且几乎是每个人都会犯的错误！查看查询的解释计划：

---

---

| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |     5 |    65 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |           |     5 |    65 |     4  (25)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |           |       |       |            |          |
|   3 |    TABLE ACCESS FULL| LOCK_TEST |    10 |   130 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=5)

Oracle executes the plan from the bottom up - notice that the filter on rownum is carried out before the sort, Oracle takes the rows in the order it finds them (order they were inserted here { 10, 9, 8, 7, 6}), stops after it gets 5 rows, and then sorts that set.

Oracle 自下而上执行计划 - 注意 rownum 上的过滤器是在排序之前执行的，Oracle 按照它找到的顺序获取行（它们插入这里的顺序 { 10, 9, 8, 7, 6}） , 在它获得 5 行后停止，然后对该集合进行排序。

So, to get the correct first 5 you need to do the sort first and then the order by using an inline view:

因此，要获得正确的前 5 个，您需要先进行排序，然后使用内联视图进行排序：

select * from
(
  select *
  from lock_test
  order by c1 asc
)
where rownum <= 5;

C1
--
1
2
3
4
5

Now, to finally get to the point - can you put a for update skip locked in the correct place?

现在，终于说到点子上了 - 你能把一个 for update skip 锁定在正确的地方吗？

select * from
(
  select *
  from lock_test
  order by c1 asc
)
where rownum <= 5
for update skip locked;

This gives an error:

这给出了一个错误：

ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc

Trying to move the for update into the view gives a syntax error:

尝试将 for update 移动到视图中会出现语法错误：

select * from
(
  select *
  from lock_test
  order by c1 asc
  for update skip locked
)
where rownum <= 5;

The only thing that will work is the following, which GIVES THE WRONG RESULT:

唯一可行的是以下内容，它给出了错误的结果：

  select *
  from lock_test
  where rownum <= 5
  order by c1 asc
  for update skip locked;

Infact, if you run this query in session 1, and then run it again in session two, session two will give zero rows, which is really really wrong!

事实上，如果您在会话 1 中运行此查询，然后在会话 2 中再次运行它，会话 2 将给出零行，这真的是错误的！

So what can you do? Open the cursor and fetch how many rows you want from it:

所以，你可以做什么？打开游标并从中获取您想要的行数：

set serveroutput on

declare
  v_row lock_test%rowtype;
  cursor c_lock_test
  is
  select c1
  from lock_test
  order by c1
  for update skip locked;
begin
  open c_lock_test;
  fetch c_lock_test into v_row;
  dbms_output.put_line(v_row.c1);
  close c_lock_test;
end;
/    

If you run that block in session 1, it will print out '1' as it got a locked the first row. Then run it again in session 2, and it will print '2' as it skipped row 1 and got the next free one.

如果您在会话 1 中运行该块，它将打印出“1”，因为它锁定了第一行。然后在会话 2 中再次运行它，它将打印“2”，因为它跳过了第 1 行并获得了下一个可用的行。

This example is in PLSQL, but using the setFetchSize in Java you should be able get the exact same behaviour.

这个例子是在 PLSQL 中，但是在 Java 中使用 setFetchSize 你应该能够得到完全相同的行为。

回答by tipame

My solution - is to write stored procedure like this:

我的解决方案 - 是编写这样的存储过程：

CREATE OR REPLACE FUNCTION selectQueue 
RETURN SYS_REFCURSOR
AS
  st_cursor SYS_REFCURSOR;
  rt_cursor SYS_REFCURSOR;
  i number(19, 0);

BEGIN

  open st_cursor for
  select id
  from my_queue_table
  for update skip locked;

  fetch st_cursor into i;
  close st_cursor;

  open rt_cursor for select i as id from dual;
  return  rt_cursor;

 END;

This is simple example - returning TOP FIRST non blocked row. To retrieve TOP N rows - replace single fetch into local variable ("i") with loop fetch into temp table.

这是一个简单的例子 - 返回 TOP FIRST 非阻塞行。要检索 TOP N 行 - 将单次提取替换为局部变量（“i”）与循环提取到临时表中。

PS: returning cursor - is for hibernate friendship.

PS：返回光标 - 用于休眠友谊。

回答by lizhipeng

I met this problem, we spend a lot of time to solve it. Some use for updatefor update skip locked, in oracle 12c, a new method is to use fetch first n rows only. But we use oracle 11g.

我遇到了这个问题，我们花了很多时间来解决它。一些使用for updatefor update skip locked，在oracle 12c中，一种新的方法是使用fetch first n rows only. 但是我们使用oracle 11g。

Finally, we try this method, and found works well.

最后，我们尝试了这个方法，发现效果很好。

CURSOR c_1 IS  
   SELECT *
     FROM QueueTest qt
     WHERE Locked IS NULL
     ORDER BY PRIORITY;
   myRow c_1%rowtype;
   i number（5）:=0;
   returnNum := 10;
BEGIN
  OPEN c_1;
  loop 
    FETCH c_1 into myRow 
    exit when c_1%notFOUND 
    exit when i>=returnNum;
    update QueueTest set Locked='myLock' where id=myrow.id and locked is null;
    i := i + sql%rowcount;
  END
  CLOSE c_1;
  commit;
END;

I wirte it in notepad, so something maybe wrong, you can modify it as a procedure or else.

我用记事本写的，所以可能有问题，您可以将其修改为程序或其他。

回答by tbone

In your first session, when you execute:

在您的第一个会话中，当您执行：

SELECT qt.ID
FROM QueueTest qt
WHERE qt.ID IN (
    SELECT ID
    FROM
        (SELECT ID FROM QueueTest WHERE Locked IS NULL ORDER BY Priority)
    WHERE ROWNUM = 1)
FOR UPDATE SKIP LOCKED

Your inner select attempt to grab only id=4 and lock it. This is successful because this single row is not yet locked.

您的内部选择尝试仅抓取 id=4 并将其锁定。这是成功的，因为这一行尚未锁定。

In second session, your inner select STILL tries to grab ONLY id=4and lock it. This is not successful because that single row is still locked by first session.

在第二个会话中，您的内部选择仍然尝试仅获取id=4并锁定它。这不成功，因为该单行仍被第一个会话锁定。

Now, if you updated the "locked" field in first session, the next session to run that select will grab id=3.

现在，如果您在第一个会话中更新了“锁定”字段，则运行该选择的下一个会话将获取 id=3。

Basically, in your example you are depending on a flag that isn't being set. To use your locked flag, you probably mean to do something like:

基本上，在您的示例中，您依赖于未设置的标志。要使用锁定标志，您可能打算执行以下操作：

1. select IDs you want based on some criteria.
2. Immediate update the locked flag = 1 for these IDs (if resource busy, another session beat you to this step for 1 or more IDs, goto 1 again)
3. Do whatever on these IDs
4. update the locked flag back to null

1. 根据某些条件选择您想要的 ID。
2. 立即更新这些 ID 的锁定标志 = 1（如果资源繁忙，另一个会话将您赶到这一步以获得 1 个或多个 ID，再次转到 1）
3. 对这些 ID 执行任何操作
4. 将锁定标志更新回空

You can then use your select for update skip locked statement since your locked flag is being maintained.

然后您可以使用您的选择更新跳过锁定语句，因为您的锁定标志正在维护。

Personally, I don't like all the updates to flags (your solution may require them for whatever reason), so I'd probably just tryto select the IDs I want to update (by whatever criteria) in each session:

就个人而言，我不喜欢标志的所有更新（您的解决方案可能出于任何原因需要它们），所以我可能只是尝试在每个会话中选择我想要更新的 ID（无论标准）：

select * from queuetest where ... for update skip locked;

select * from queuetest where ... for update skip locked;

For example (in reality, my criteria wouldn't be based on a list of ids, but queuetest table is overly simplistic):

例如（实际上，我的标准不会基于 id 列表，但 queuetest 表过于简单）：

• sess 1: select * from queuetest where id in (4,3) for update skip locked;

• sess 2: select * from queuetest where id in (4,3,2) for update skip locked;

• sess 1: select * from queuetest where id in (4,3) for update skip locked;

• sess 2: select * from queuetest where id in (4,3,2) for update skip locked;

Here sess1 would lock 4,3 and sess2 would lock only 2.

这里 sess1 将锁定 4,3，而 sess2 将仅锁定 2。

You cannot to my knowledge do a top-n or use group_by/order_by etc in the select for update statement, you'll get a ORA-02014.

据我所知，您不能在 select for update 语句中执行 top-n 或使用 group_by/order_by 等，您将得到 ORA-02014。

回答by Rajesh Dharmana

Firstly thanks for top 2 answers ..Learnt a lot from them.I have tested the following code and after running Practicedontdel.java main method ,I found that the two classes prints different rows every time. Please let me know if in any case this code might fail.(P.S : thanks to stack overflow)

首先感谢前 2 个答案..从他们那里学到了很多东西。我测试了以下代码，在运行 Practicedontdel.java main 方法后，我发现这两个类每次打印不同的行。如果在任何情况下此代码可能会失败，请告诉我。（PS：感谢堆栈溢出）

Practicedontdel.java:

实践dontdel.java：

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs =null;
    String val="";
    int count =0;

        conn = getOracleConnection();
        conn.setAutoCommit(false);
        ps = prepareStatement(conn,"SELECT /*+FIRST_ROWS_3*/ t.* from 
        REPROCESS_QUEUE t FOR UPDATE SKIP LOCKED");
        ps.setFetchSize(3);
        boolean rss = ps.execute();
        rs = ps.getResultSet();
        new Practisethread().start();
        while(count<3 && rs.next())
        {
            val = rs.getString(1);
            System.out.println(val);
            count++;
            Thread.sleep(10000);
        }
       conn.commit();
            System.out.println("end of main program");

Practisethread.java: in run():

Practisethread.java: 在 run():

            conn = getOracleConnection();
            conn.setAutoCommit(false);
            ps = prepareStatement(conn,"SELECT /*+FIRST_ROWS_3*/ t.* from REPROCESS_QUEUE t FOR UPDATE SKIP LOCKED");
            ps.setFetchSize(3);
            boolean rss = ps.execute();
            rs = ps.getResultSet();
            while(count<3 && rs.next())
            {
                val = rs.getString(1);
                System.out.println("******thread******");
                System.out.println(val);
                count++;
                Thread.sleep(5000);
            }
            conn.commit();
            System.out.println("end of thread program");