Oracle이 SKIP LOCKED 상태에서 상위 N개 행을 반환하도록 강제 적용

Oracle이 SKIP LOCKED 상태에서 상위 N개 행을 반환하도록 강제 적용

Oracle 및 SQL Server에서 대기열과 유사한 테이블(특정 행 잠금, 특정 행 수 선택, 현재 잠긴 행 건너뛰기)을 구현하는 방법에 대한 몇 가지 질문이 있습니다.

가 어떻게 번호를 할 수 요?N ) 행, 가정합니다.N적합한 행?

본 은 Oracle을 합니다.WHERE건너뛸 행을 결정하기 전에 술어를 입력합니다.즉, 테이블에서 하나의 행을 끌어오고 두 개의 스레드가 동시에 동일한 SQL을 실행하려면 하나는 행을 수신하고 다른 하나는 빈 결과 집합을 수신합니다(대상 행이 더 많더라도).

에서 SQL Server를 됩니다.UPDLOCK,ROWLOCK그리고.READPAST SQL Server에서TOP잠금을 성공적으로 달성한 후의 레코드 수를 마법처럼 제한하는 것 같습니다.

여기와 여기 두 개의 흥미로운 기사가 있습니다.

오라클

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 서버

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에서 유사한 동작을 수행하려면 어떻게 해야 합니까?

"제가 본 바로는 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;

게리 마이어스가 올린 해결책은 제가 생각할 수 있는 모든 것에 대한 것입니다. AQ를 사용하는 것 외에는 이 모든 것을 여러분과 더 많은 것을 위해 합니다.

PLSQL을 사용하지 않으려면 PLSQL을 Java JDBC 호출로 변환할 수 있어야 합니다.동일한 SQL 문을 준비하고 실행한 다음 해당 문에 대해 단일 행 가져오기(또는 N 행 가져오기)를 계속 수행하기만 하면 됩니다.

http://download.oracle.com/docs/cd/B10501_01/java.920/a96654/resltset.htm#1023642 에 있는 Oracle 설명서는 문 수준에서 이를 수행하는 방법에 대한 몇 가지 단서를 제공합니다.

쿼리의 가져오기 크기를 설정하려면 쿼리를 실행하기 전에 문 개체에서 setFetchSize()를 호출합니다.가져오기 크기를 N으로 설정하면 N개의 행이 데이터베이스로 이동할 때마다 가져오기됩니다.

그래서 당신은 자바에서 (의사 코드에서) 다음과 같은 것을 코딩할 수 있습니다.

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개가 포함된 행은 처음, 9개 등입니다.

처음 5개 행을 오름차순으로, 즉 1부터 5까지 순서대로 원한다고 합니다.첫 번째 시도는 다음과 같습니다.

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

결과는 다음과 같습니다.

C1
--
6
7
8
9 
10

그것은 분명히 잘못된 것이고, 거의 모든 사람들이 저지르는 실수입니다!쿼리에 대한 설명 계획을 확인합니다.

---

| 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은 아래에서 위로 계획을 실행합니다. 정렬하기 전에 행에 대한 필터가 수행되고 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에서 이 쿼리를 실행한 후 세션 2에서 다시 실행하면 세션 2에서 행이 0개가 되는데, 이는 정말 잘못된 것입니다!

그래서 당신은 무엇을 할 수 있습니까?커서를 열고 원하는 행 수를 가져옵니다.

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에서 다시 실행하면 1행을 건너뛰고 다음 행을 무료로 얻었을 때 '2'가 인쇄됩니다.

이 예제는 PLSQL에 있지만 Java에서 setFetchSize를 사용하면 동일한 동작을 얻을 수 있습니다.

첫 번째 세션에서 다음을 실행할 때:

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만 잡고 잠그려고 합니다.이 단일 행이 아직 잠겨 있지 않기 때문에 성공했습니다.

두 번째 세션에서는 내부 선택 항목이 여전히 id=4만 잡고 잠그려고 합니다.이 단일 행은 첫 번째 세션에서 여전히 잠겨 있기 때문에 성공하지 못했습니다.

첫 번째 세션에서 "잠긴" 필드를 업데이트한 경우 다음에 실행할 세션은 id=3을 잡습니다.

기본적으로 이 예에서는 설정되지 않은 플래그에 의존합니다.잠긴 플래그를 사용하려면 다음과 같은 작업을 수행해야 합니다.

1. 일부 기준에 따라 원하는 ID를 선택합니다.
2. 이러한 ID에 대해 잠긴 플래그 = 1을 즉시 업데이트합니다(리소스 사용 중인 경우 다른 세션에서 1개 이상의 ID에 대해 이 단계로 이동하면 다시 1로 다시 이동합니다.
3. 이 ID에서 무엇이든 수행합니다.
4. 잠긴 플래그를 null로 다시 업데이트합니다.

그러면 잠금 플래그가 유지되고 있으므로 선택한 업데이트 건너뛰기 잠금 문을 사용할 수 있습니다.

개인적으로 플래그에 대한 모든 업데이트를 좋아하지 않기 때문에(어떤 이유로든 해당 솔루션에 플래그가 필요할 수 있음) 각 세션에서 업데이트할 ID를 선택하려고 합니다.

...의 대기열 테스트에서 *를 선택합니다.업데이트 스킵 잠금의 경우

예를 들어, (실제로 제 기준은 ID 목록을 기반으로 하지 않지만 대기열 테스트 테이블은 지나치게 단순합니다.

• 세션 1: 대기열 테스트에서 *를 선택합니다. 여기서 id는 (4,3)의 업데이트 건너뛰기 잠금에 사용됩니다.

• sess 2: 대기열 테스트에서 *를 선택합니다. 여기서 id는 (4,3,2)에서 업데이트 건너뛰기 잠금에 사용됩니다.

여기서 sess1은 4,3을 잠그고 sess2는 2만 잠깁니다.

업데이트 선택 문에서 top-n을 수행하거나 group_by/order_by 등을 사용할 수 없는 것으로 알고 있습니다. ORA-02014가 제공됩니다.

나의 해결책은 다음과 같은 저장 프로시저를 쓰는 것입니다.

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: 커서 반환 - 최대 절전 모드 우정을 위한 것입니다.

저는 이 문제를 만났고, 우리는 그것을 해결하기 위해 많은 시간을 소비합니다.은 부일사를 사용합니다.for update for update skip locked오라클 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;

제가 메모장에 써놨으니 뭔가 잘못된 것 같은데, 절차대로 수정하시거나 다른 방법으로 수정하시면 됩니다.

먼저 상위 2개 답변에 감사드립니다.그들에게서 많은 것을 배웠습니다.다음 코드를 테스트해 본 결과 Practionontdel.java main 메서드를 실행한 후 두 클래스가 매번 다른 행을 출력합니다.어떤 경우에도 이 코드가 실패할 수 있으면 알려주시기 바랍니다.(P.S : 스택 오버플로 덕분)

연습된 온델.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");

tread.java: in 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");

언급URL : https://stackoverflow.com/questions/6117254/force-oracle-to-return-top-n-rows-with-skip-locked