Заставить Oracle вернуть TOP N строк с SKIP LOCKED

Question

Заставить Oracle вернуть TOP N строк с SKIP LOCKED

Есть несколько вопросов о том, как реализовать таблицу, подобную очереди (блокировка определенных строк, выбор определенного количества и пропуск текущих заблокированных строк) в Oracle и SQL Server.

Как я могу гарантировать получение определенного номера (N) строк, при условии, что есть хотя бы N подходящие строки?

Из того, что я видел, Oracle применяет WHERE Предикат, прежде чем определять, какие строки пропустить. Это означает, что если я хочу извлечь одну строку из таблицы, и два потока одновременно выполняют один и тот же SQL-запрос, один получит строку, а другой - пустой набор результатов (даже если имеется больше подходящих строк).

Это противоречит тому, как выглядит SQL Server для обработки UPDLOCK, ROWLOCK а также READPAST Блокировка подсказок. В SQL Server TOP волшебным образом кажется ограничить количество записей после успешного достижения блокировок.

Обратите внимание, две интересные статьи здесь и здесь.

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);

В двух отдельных сеансах выполните:

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

Обратите внимание, что первый возвращает строку, а второй сеанс не возвращает строку:

Сессия 1

 Я БЫ
----
  4

Сессия 2

 Я БЫ
----

SQL SERVER

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);

В двух отдельных сеансах выполните:

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

Обратите внимание, что обе сессии возвращают разные строки.

Сессия 1

 Я БЫ
----
  4

Сессия 2

 Я БЫ
----
  3

Как я могу получить подобное поведение в Oracle?

32

sql sql-server oracle queue

Источник

user533907 24 май '11 в 21:40

5 ответов

Решение

Решение, которое выложил Гэри Мейерс, - это почти все, что я могу придумать, за исключением использования AQ, которое делает все это для вас и многое другое.

Если вы действительно хотите избежать PLSQL, вы должны иметь возможность переводить PLSQL в вызовы Java JDBC. Все, что вам нужно сделать, это подготовить один и тот же оператор SQL, выполнить его, а затем продолжать выполнять выборки из одной строки (или N выборок строк).

Документация Oracle по адресу http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm дает некоторое представление о том, как это сделать на уровне операторов:

Чтобы установить размер выборки для запроса, вызовите setFetchSize() для объекта оператора перед выполнением запроса. Если вы установите размер выборки N, то N строк будут выбраны с каждой поездкой в базу данных.

Таким образом, вы можете написать код в 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;

ОБНОВИТЬ

Основываясь на комментариях ниже, было предложено использовать 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;
/

Теперь у нас есть таблица с 10 строками. Обратите внимание, что я аккуратно вставил строки в обратном порядке, сначала строка, содержащая 10, затем 9 и т. Д.

Допустим, вы хотите первые 5 рядов, упорядоченные по возрастанию - т.е. от 1 до 5. Ваша первая попытка заключается в следующем:

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

Который дает результаты:

Это явно неправильно, и это ошибка, которую делают почти все! Посмотрите на план объяснения для запроса:

| 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 выполняет план снизу вверх - обратите внимание, что фильтр по rownum выполняется перед сортировкой, Oracle берет строки в том порядке, в котором они их находят (в порядке их вставки здесь { 10, 9, 8, 7, 6}), останавливается после получения 5 строк, а затем сортирует этот набор.

Итак, чтобы получить правильные первые 5, вам нужно сначала выполнить сортировку, а затем упорядочить, используя встроенное представление:

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

C1
--
1
2
3
4
5

Теперь, чтобы наконец добраться до сути - можете ли вы поставить для обновления пропущенный в нужном месте?

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

Это дает ошибку:

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

Попытка переместить для обновления в представление приводит к синтаксической ошибке:

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

Единственное, что сработает, это следующее, что дает неверный результат:

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

На самом деле, если вы запустите этот запрос в сеансе 1, а затем снова запустите его во втором сеансе, второй сеанс даст ноль строк, что на самом деле неправильно!

Так что ты можешь сделать? Откройте курсор и извлеките из него сколько строк вы хотите:

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;
/

Если вы запустите этот блок в сеансе 1, он выведет "1", поскольку заблокировал первую строку. Затем запустите его снова в сеансе 2, и он напечатает "2", пропустив строку 1 и получив следующую свободную.

Этот пример в PLSQL, но используя setFetchSize в Java, вы сможете получить точно такое же поведение.

14

Источник

user88839 11 июл '11 в 11:43

В вашем первом сеансе, когда вы выполняете:

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

Ваша внутренняя попытка выбора захватить только id=4 и заблокировать его. Это успешно, потому что эта строка еще не заблокирована.

Во втором сеансе ваш внутренний селектор STILL пытается захватить ТОЛЬКО id=4 и заблокировать его. Это не удачно, потому что эта единственная строка все еще заблокирована первым сеансом.

Теперь, если вы обновили "заблокированное" поле в первом сеансе, следующий сеанс, который будет запущен, выберет id=3.

По сути, в вашем примере вы зависите от не установленного флага. Чтобы использовать ваш заблокированный флаг, вы, вероятно, хотите сделать что-то вроде:

выберите идентификаторы, которые вы хотите на основе некоторых критериев.
Немедленно обновите заблокированный флаг = 1 для этих идентификаторов (если ресурс занят, другой сеанс побил вас на этом шаге для 1 или более идентификаторов, снова перейдите к 1)
Делай что угодно с этими идентификаторами
обновить заблокированный флаг обратно на ноль

Затем вы можете использовать свой выбор для обновления, чтобы пропустить заблокированный оператор, так как ваш заблокированный флаг сохраняется.

Лично мне не нравятся все обновления флагов (ваше решение может потребовать их по любой причине), поэтому я, вероятно, просто попытаюсь выбрать идентификаторы, которые я хочу обновить (по любым критериям) в каждой сессии:

выберите * из очереди, где... для обновления пропустить заблокирован;

Например (на самом деле мои критерии не основаны на списке идентификаторов, но таблица очередей слишком упрощена):

sess 1: выберите * из очереди, где идентификатор в (4,3) для обновления пропущен;
sess 2: выберите * из очереди, где идентификатор в (4,3,2) для обновления заблокирован;

Здесь sess1 заблокирует 4,3, а sess2 заблокирует только 2.

Насколько мне известно, вы не можете использовать top-n или использовать group_by/order_by и т. Д. В операторе выбора для обновления, вы получите ORA-02014.

2

Источник

user534120 08 июл '11 в 12:44

Во-первых, спасибо за два первых ответа. Многому научился у них. Я протестировал следующий код и после запуска основного метода Practicedontdel.java обнаружил, что эти два класса каждый раз выводят разные строки. Пожалуйста, дайте мне знать, если в любом случае этот код может выйти из строя. (PS: благодаря переполнению стека)

Практикуется ontdel.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: в 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");

1

Источник

user11351011 21 июн '19 в 12:52

Мое решение - написать хранимую процедуру следующим образом:

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;

Это простой пример - возвращение TOP FIRST неблокированной строки. Чтобы получить TOP N строк - замените одиночную выборку в локальную переменную ("i") циклической выборкой во временную таблицу.

PS: возвращение курсора - это для спящей дружбы.

1

Источник

user994961 14 окт '11 в 08:06

Я встретил эту проблему, мы тратим много времени на ее решение. Некоторые используют for updatefor update skip lockedВ Oracle 12C новый метод заключается в использовании fetch first n rows only, Но мы используем оракула 11g.

Наконец, мы попробовали этот метод, и нашли хорошо работает.

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;

Я пишу это в блокноте, так что что-то может быть не так, вы можете изменить это как процедуру или как-то еще.

1

Источник

user1415511 03 авг '18 в 13:18

Другие вопросы по тегам sql sql-server oracle queue

user25714 24 май '11 в 23:20 2011-05-24 23:20 · Accepted Answer · 2011-05-24 23:20

"Из того, что я видел, Oracle применяет предикат WHERE, прежде чем определять, какие строки пропустить".

Ага. Это единственный возможный способ. Вы не можете пропустить строку из набора результатов, пока не определите набор результатов.

Ответ заключается просто в том, чтобы не ограничивать количество строк, возвращаемых оператором SELECT. Вы все еще можете использовать подсказки FIRST_ROWS_n, чтобы указать оптимизатору, что вы не будете получать полный набор данных.

Программное обеспечение, вызывающее 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;