업데이트 동작에 대한 Oracle 선택

업데이트 동작에 대한 Oracle 선택

우리가 해결하고자 하는 문제는 다음과 같습니다.

• 우리는 카드를 나타내는 줄로 가득 찬 테이블을 가지고 있습니다.예약 거래의 목적은 고객에게 카드를 할당하는 것입니다.
• 카드는 많은 클라이언트에 속할 수 없습니다.
• 얼마 후(구매하지 않은 경우) 카드를 다시 사용할 수 있는 재사용 가능한 풀로 반환해야 합니다.
• 여러 고객이 동시에 예약할 수 있습니다.
• 데이터를 저장하는 데 Oracle 데이터베이스를 사용하므로 솔루션은 Oracle 11 이상에서 작동해야 합니다.

우리의 해결책은 카드에 상태를 부여하고 예약 날짜를 저장하는 것입니다.카드를 예약할 때 "업데이트를 위해 선택" 문을 사용하여 예약합니다.쿼리는 사용 가능한 카드와 오래 전에 예약된 카드를 찾습니다.

하지만 우리의 쿼리가 예상대로 작동하지 않습니다.

문제를 설명하기 위해 간단한 상황을 준비했습니다.데이터로 가득 찬 card_numbers 테이블이 있습니다. 모든 행에 null이 아닌 번호가 있습니다.이제, 그것들 중 일부를 잠궈 보겠습니다.

-- first, in session 1
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

여기서는 거래를 하지 않습니다. 행이 잠겨 있어야 합니다.

-- later, in session 2
set autocommit off;

select id from card_numbers  
where id is not null  
and rownum <= 1  
for update skip locked;

예상되는 동작은 두 세션에서 쿼리 조건을 만족하는 단일 다른 행을 얻는 것입니다.

하지만 그것은 그렇게 작동하지 않습니다.쿼리의 "잠금 해제" 부분을 사용하는지 여부에 따라 동작이 변경됩니다.

• 세션 1에서 트랜잭션 커밋 또는 롤백 대기 중인 "잠금 해제" 없음 - 두 번째 세션이 차단됨
• "filename locked" 포함 - 두 번째 쿼리는 즉시 빈 결과 집합을 반환합니다.

그래서, 이 긴 소개 후에 질문이 나옵니다.

Oracle에서 원하는 잠금 동작이 가능합니까?만약 그렇다면, 우리는 무엇을 잘못하고 있습니까?올바른 해결책은 무엇입니까?

업데이트 건너뛰기 잠금에 대해 발생한 동작은 이 블로그 노트에 설명되어 있습니다.FOR UPDATE 조항은 WHERE 조항 다음에 평가하는 것으로 알고 있습니다.SKIP LOCKED는 반환된 행 중에 잠긴 행이 없음을 보장하는 추가 필터와 같습니다.

당신의 진술은 논리적으로 다음과 같습니다: 첫 번째 행 찾기card_numbers잠기지 않은 경우 반송합니다.분명히 이것은 당신이 원하는 것이 아닙니다.

다음은 사용자가 설명하는 동작을 재현하는 작은 테스트 사례입니다.

SQL> CREATE TABLE t (ID PRIMARY KEY)
  2  AS SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1000;

Table created

SESSION1> select id from t where rownum <= 1 for update skip locked;

        ID
----------
         1

SESSION2> select id from t where rownum <= 1 for update skip locked;

        ID
----------

두 번째 선택부터는 행이 반환되지 않습니다.커서를 사용하여 이 문제를 해결할 수 있습니다.

SQL> CREATE FUNCTION get_and_lock RETURN NUMBER IS
  2     CURSOR c IS SELECT ID FROM t FOR UPDATE SKIP LOCKED;
  3     l_id NUMBER;
  4  BEGIN
  5     OPEN c;
  6     FETCH c INTO l_id;
  7     CLOSE c;
  8     RETURN l_id;
  9  END;
 10  /

Function created

SESSION1> variable x number;
SESSION1> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
1

SESSION2> variable x number;
SESSION2> exec :x := get_and_lock;

PL/SQL procedure successfully completed
x
---------
2

커서를 명시적으로 가져왔으므로 한 행만 반환되고 한 행만 잠깁니다.

다른 답변들은 이미 다양한 데이터베이스의 상황을 충분히 설명하고 있습니다.SELECT .. FOR UPDATE변형 모델, Oracle이 사용을 권장하지 않는다는 점을 언급할 가치가 있다고 생각합니다.FOR UPDATE SKIP LOCKED직접적으로 그리고 사용을 장려합니다.Oracle AQ대신:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#i2066346

우리는 사용합니다.Oracle AQ과 저는 다소 곡선에, 생산자/를 직접 꽤 될 수 것을 할 수 .

빈센트의 대답이 틀렸다는 것은 아니지만, 저는 다르게 설계했을 것입니다.

제 첫 번째 본능은 사용 가능한 첫 번째 레코드를 선택하고 "reserved_date"로 레코드를 업데이트하는 것입니다.XXX 시간이 경과하고 트랜잭션이 완료되지 않은 후 레코드의 reserved_date를 다시 null로 업데이트하여 레코드를 다시 확보합니다.

저는 가능한 한 단순하게 하려고 노력합니다.저는 이것이 더 간단합니다.

언급URL : https://stackoverflow.com/questions/5847228/oracle-select-for-update-behaviour